1

Query:

EXPLAIN ANALYZE select count(*) from product;

ROWS: 534965

EXPLANATION :

Finalize Aggregate  (cost=53840.85..53840.86 rows=1 width=8) (actual time=5014.774..5014.774 rows=1 loops=1)
  ->  Gather  (cost=53840.64..53840.85 rows=2 width=8) (actual time=5011.623..5015.480 rows=3 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial Aggregate  (cost=52840.64..52840.65 rows=1 width=8) (actual time=4951.366..4951.367 rows=1 loops=3)
              ->  Parallel Seq Scan on product prod  (cost=0.00..52296.71 rows=217571 width=0) (actual time=0.511..4906.569 rows=178088 loops=3)
Planning Time: 34.814 ms
Execution Time: 5015.580 ms

How can we optimize the above query to get the counts very quickly?

This is a simple query, however, its variations can include different conditions and join with other tables. Consider a situation where a user searches something from the frontend of the website and we want to display X number of results match your search. And this can include lots of filters.

Following improves performance but uses more resources.

set max_parallel_workers_per_gather to 4;

It performs a bit faster now as compared to the originally posted question due to the right index and using parallel_workers. Still, looking for a more efficient way, since it's a common problem people try to find a solution, particularly to support client side applications, specially in dashboards.

My use case right now is to retrieve some records to display on the client-side application along with pagination.

Overall there are two queries. The first one is to select the records; the second one is to select the count (on the frontend for pagination as well). The first query takes less than a second and the second query takes much more time than that.

The overall time to return response is more than 4-5 seconds depending upon filters. On the frontend overall performance becomes slow.

I am using PostgreSQL 11.8.

Improve this question
6
  • 2
    This question is too broad, but read this.
    – Laurenz Albe
    Nov 11, 2020 at 6:33
  • @MuhammadAdnan I really don't want to know that there are exactly 534965 matches. Approx 500000 would be more than adequate. Even just "lots". When it gets down to the tens, then it's nice to know but hardly essential!
    – Colin 't Hart
    Nov 11, 2020 at 19:22
  • @Colin'tHart version is 11.8. what do you propose for Approx results?
    – Adnan Mohib
    Nov 11, 2020 at 20:21
  • Could you possibly explain what you are trying to achieve and why you thing that the duration is too slow? There is a WIKI article that explains why a COUNT is slow: Slow Counting The reason why this is slow is related to the MVCC implementation in PostgreSQL. The fact that multiple transactions can see different states of the data means that there can be no straightforward way for "COUNT(*)" to summarize data across the whole table; PostgreSQL must walk through all rows, in some sense. ...
    – John K. N.
    Nov 12, 2020 at 8:51
  • 1
    "And this can include lots of filters" - then check the performance of the query when applying those filters. If you have the right indexes it's highly likely that is going to be a lot faster than going through all rows.
    – user1822
    Nov 12, 2020 at 13:52

0

Reset to default