PostgreSQL efficient count of number of rows WHERE parameter=TRUE

Question

I have a very massive PostgreSQL database table. One of its columns stores a boolean parameter. What I want is a very simple query:

SELECT COUNT(*) FROM myTable WHERE myTable.isSthTrue;

The problem I have is that it is very slow as it needs to check every row if it satisfies the criteria. Adding an index to this parameter speeds up the calculation roughly two times, but doesn't really improve the complexity in general.

Some people on the internet suggest adding triggers that update the count which is stored in a separate table. But that feels like too much effort for an easy problem like this.

How many rows do you have in that table? And how many rows qualify for that condition? And what index did you add exactly? What is the output of explain (analyze, verbose) for the query with and without the index? And what is the current performance? And how fast do you need it to be? Also which version are you using? — user330315
– user330315, Commented Nov 23, 2015 at 22:10
Did you try adding a partial index on isSthTrue WHERE isSthTrue is true ? — Kamil Gosciminski
– Kamil Gosciminski, Commented Nov 23, 2015 at 22:13
@a_horse_with_no_name Currently I have 100k, but in real system there are going to be around 10M. Number of rows that qualify for the condition might be anything between 1-75%. I added btree index. explain analyze says that it is using seq scan without index and Index only scan with index, as expected. Performance is in hundreds of milis. So I don't want it to take dozens of seconds in real system to do such a simple query. I am using 9.3.sth version. — eddyP23
– eddyP23, Commented Nov 24, 2015 at 15:10
@eddyP a partial index only builds up btree of values that meet where condition. I'm sure that the size of an index would be much less than the whole, non-partial one. — Kamil Gosciminski
– Kamil Gosciminski, Commented Nov 24, 2015 at 22:25

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

If you need an exact result.

Then, yes, a trigger-based solution is likely the best path to go.

If an estimate is okay, consider Materialized Views. (Postgres 9.3+)

Something like CREATE MATERIALIZED VIEW myCount AS SELECT COUNT(*) FROM myTable WHERE myTable.isSthTrue; would maintain a copy of the expensive query you reference. The only caveat is that this aggregate view would not be automatically updated. To do that you need to call REFRESH MATERIALIZED VIEW, which could be done in a cron, or other timed task.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Nov 23, 2015 at 22:25

Don Scott

3,5071 gold badge30 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

steve Over a year ago

As at August 2018, the link "check out this resource" is spam.

Collectives™ on Stack Overflow

PostgreSQL efficient count of number of rows WHERE parameter=TRUE

1 Answer 1

If you need an exact result.

If an estimate is okay, consider Materialized Views. (Postgres 9.3+)

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

If you need an exact result.

If an estimate is okay, consider Materialized Views. (Postgres 9.3+)

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related