0

I have a problem I have been facing for some time and was ignoring for a while. The problem of counting rows. I have read that it is slow as the PostgreSQL has to validate each row thus have to do sequential scan. I am currently using PostgreSQL 13.

Potential solutions I came across:

  1. Using Estimate.

    Although the estimation was closer when querying a single table, the result was far off when using joins. Also I want exact count as I need to show the exact number to the user like how many transactions he did.

  2. Using an external table to maintain the count.

    The idea here was to use a rule or after insert trigger to update the count using count = count + 1 The problem here is the user can add multiple filters (like date, userid etc), which will change the count but the external table is only storing the count.

How would I solve this? I am trying to reduce the time to get the count to less than 3-4 seconds. Currently, there are almost 1 billion records in a table, although this table is split into 12 partitions for each month. Is this even possible?

Eleven partitions won't have inserts but could have delete operations. The user can apply filters which will change the count for any partition.

I am using a b-tree index on the date column. In explain analyze, the index is being used but since the records are huge, the b-tree index cannot fit in RAM as it's size is also big.

The tables are partitioned by each month and each partition has an index on the date, which means there is one index for each month.

0
2

With a large table, you won't be able to avoid I/O, and the query will never be fast. Very fast aggregates over huge amounts of data are not PostgreSQL's strong point. You want a column store for that (PostgreSQL has a column store extension).

If you VACUUM the table, PostgreSQL can avoid looking up the row by consulting the visibility map.

If 11 partitions are static, with only one active, it might be possible to store the total for the static partitions and add to that the count of rows for the active partition. If the user can apply filters, there is no way to get out of actually counting the rows that match. You might need a data warehouse of some sort that summarises the data.

0
1

If your data have predictable values that you are searching on, you can build partial indexes that can be loaded into RAM and used effectively.

CREATE INDEX table_type_value1
ON table(ordered_value)
WHERE type=value1

to serve the query SELECT ordered_value FROM table WHERE type=value1

You will need one index for each type value you want to optimize.

In your description, the ordering column is a date, and the condition could be a date range that is practical to search (today, last week, etc.)

2
  • So you mean to say that let's say for the month "May" I would create a partial index which would be for each week for that partition? I could test this. Thanks
    – Freedom911
    May 17 at 7:20
  • Make the condition in the index match the condition in the query (in order of importance, as above) to make sure the index is selected by the planner. Start with a simpler index and add columns to satisfy additional sorting, filtering, and selected columns. May 17 at 15:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.