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.
