Slow COUNT DISTINCT on a large table in PostgreSQL

Question

In PostgreSQL 14, I have a table with around 10 million of rows with this structure:

CREATE TABLE search_stats (
    id bigserial NOT NULL,
    search_date timestamp NOT NULL,
    user_id varchar NULL,
    search_query varchar NOT NULL
)

I want to retrieve, for each search_query, the count and the number of unique user_id associated.

This is the query that I have come up with:

SELECT search_query, count(*) AS total_number_of_searches, 
    count(DISTINCT user_id) AS total_number_of_users
FROM search_stats 
WHERE search_date >= '2023-01-01' AND search_date < '2023-07-01'
GROUP BY search_query
ORDER BY total_number_of_searches DESC, total_number_of_users desc

The problem is that it's really slow (30 seconds) even if the table is partitioned by date (each month in a partition) and there are the following indexes:

CREATE INDEX search_stats_date_idx ON search_stats USING brin (date);
CREATE INDEX search_stats_search_query_idx ON search_stats USING btree (search_query);
CREATE INDEX search_stats_user_id_idx ON search_stats USING btree (user_id);

This is my EXPLAIN (ANALYZE, BUFFERS)

Sort  (cost=576930.96..580812.49 rows=1552612 width=68) (actual time=15702.669..15936.775 rows=1656788 loops=1)                                                                                          |
  Sort Key: (count(*)) DESC, (count(DISTINCT search_stats.user_id)) DESC                                                                                                                   |
  Sort Method: external merge  Disk: 134584kB                                                                                                                                                            |
  Buffers: shared hit=15953 read=29111, temp read=38184 written=32013                                                                                                                                    |
  ->  WindowAgg  (cost=100614.65..353591.31 rows=1552612 width=68) (actual time=14207.366..14836.972 rows=1656788 loops=1)                                                                               |
        Buffers: shared hit=15953 read=29111, temp read=21361 written=15188                                                                                                                              |
        ->  GroupAggregate  (cost=100614.65..334183.66 rows=1552612 width=60) (actual time=7578.995..13339.283 rows=1656788 loops=1)                                                                     |
              Group Key: search_stats.search_query                                                                                                                                         |
              Buffers: shared hit=15953 read=29111                                                                                                                                                       |
              ->  Gather Merge  (cost=100614.65..305804.74 rows=1713707 width=109) (actual time=7578.971..10803.777 rows=1714043 loops=1)                                                                |
                    Workers Planned: 4                                                                                                                                                                   |
                    Workers Launched: 4                                                                                                                                                                  |
                    Buffers: shared hit=15953 read=29111                                                                                                                                                 |
                    ->  Sort  (cost=99614.59..100685.65 rows=428426 width=109) (actual time=5440.355..5521.071 rows=342809 loops=5)                                                                      |
                          Sort Key: search_stats.search_query                                                                                                                              |
                          Sort Method: quicksort  Memory: 113434kB                                                                                                                                       |
                          Buffers: shared hit=15953 read=29111                                                                                                                                           |
                          Worker 0:  Sort Method: quicksort  Memory: 93953kB                                                                                                                             |
                          Worker 1:  Sort Method: quicksort  Memory: 95874kB                                                                                                                             |
                          Worker 2:  Sort Method: quicksort  Memory: 43844kB                                                                                                                             |
                          Worker 3:  Sort Method: quicksort  Memory: 38459kB                                                                                                                             |
                          ->  Parallel Append  (cost=0.00..59538.15 rows=428426 width=109) (actual time=12.168..142.869 rows=342809 loops=5)                                                             |
                                Buffers: shared hit=15785 read=29111                                                                                                                                     |
                                ->  Parallel Seq Scan on mdr_querystats_part_may search_stats_5  (cost=0.00..8218.54 rows=119279 width=110) (actual time=0.018..87.771 rows=286326 loops=1)|
                                      Filter: ((search_date >= '2023-01-01 00:00:00'::timestamp without time zone) AND (search_date < '2023-07-01 00:00:00'::timestamp without time zone))                             |
                                      Buffers: shared hit=2312 read=4117                                                                                                                                 |
                                ->  Parallel Seq Scan on mdr_querystats_part_jun search_stats_6  (cost=0.00..8212.33 rows=119198 width=110) (actual time=0.018..86.707 rows=286133 loops=1)|
                                      Filter: ((search_date >= '2023-01-01 00:00:00'::timestamp without time zone) AND (search_date < '2023-07-01 00:00:00'::timestamp without time zone))                             |
                                      Buffers: shared hit=2272 read=4152                                                                                                                                 |
                                ->  Parallel Seq Scan on mdr_querystats_part_jul search_stats_7  (cost=0.00..8206.25 rows=1 width=109) (actual time=59.912..59.912 rows=0 loops=1)         |
                                      Filter: ((search_date >= '2023-01-01 00:00:00'::timestamp without time zone) AND (search_date < '2023-07-01 00:00:00'::timestamp without time zone))                             |
                                      Rows Removed by Filter: 285960                                                                                                                                     |
                                      Buffers: shared hit=2270 read=4149                                                                                                                                 |
                                ->  Parallel Seq Scan on mdr_querystats_part_jan search_stats_1  (cost=0.00..8198.10 rows=118983 width=109) (actual time=0.405..52.659 rows=142808 loops=2)|
                                      Filter: ((search_date >= '2023-01-01 00:00:00'::timestamp without time zone) AND (search_date < '2023-07-01 00:00:00'::timestamp without time zone))                             |
                                      Buffers: shared hit=2203 read=4210                                                                                                                                 |
                                ->  Parallel Seq Scan on mdr_querystats_part_mar search_stats_3  (cost=0.00..8196.36 rows=119000 width=109) (actual time=0.499..21.592 rows=57132 loops=5) |
                                      Filter: ((search_date >= '2023-01-01 00:00:00'::timestamp without time zone) AND (search_date < '2023-07-01 00:00:00'::timestamp without time zone))                             |
                                      Buffers: shared hit=2275 read=4136                                                                                                                                 |
                                ->  Parallel Seq Scan on mdr_querystats_part_apr search_stats_4  (cost=0.00..8187.51 rows=118877 width=109) (actual time=0.006..45.467 rows=142681 loops=2)|
                                      Filter: ((search_date >= '2023-01-01 00:00:00'::timestamp without time zone) AND (search_date < '2023-07-01 00:00:00'::timestamp without time zone))                             |
                                      Buffers: shared hit=2252 read=4152                                                                                                                                 |
                                ->  Parallel Seq Scan on mdr_querystats_part_feb search_stats_2  (cost=0.00..8176.93 rows=118705 width=109) (actual time=0.061..83.882 rows=284948 loops=1)|
                                      Filter: ((search_date >= '2023-01-01 00:00:00'::timestamp without time zone) AND (search_date < '2023-07-01 00:00:00'::timestamp without time zone))                             |
                                      Buffers: shared hit=2201 read=4195                                                                                                                                 |
                                ->  Parallel Seq Scan on mdr_querystats_part_aug search_stats_8  (cost=0.00..0.00 rows=1 width=64) (actual time=0.000..0.000 rows=0 loops=1)               |
                                      Filter: ((search_date >= '2023-01-01 00:00:00'::timestamp without time zone) AND (search_date < '2023-07-01 00:00:00'::timestamp without time zone))                             |
                                ->  Parallel Seq Scan on mdr_querystats_part_sep search_stats_9  (cost=0.00..0.00 rows=1 width=64) (actual time=0.001..0.001 rows=0 loops=1)               |
                                      Filter: ((search_date >= '2023-01-01 00:00:00'::timestamp without time zone) AND (search_date < '2023-07-01 00:00:00'::timestamp without time zone))                             |
                                ->  Parallel Seq Scan on mdr_querystats_part_oct search_stats_10  (cost=0.00..0.00 rows=1 width=64) (actual time=0.000..0.000 rows=0 loops=1)              |
                                      Filter: ((search_date >= '2023-01-01 00:00:00'::timestamp without time zone) AND (search_date < '2023-07-01 00:00:00'::timestamp without time zone))                             |
                                ->  Parallel Seq Scan on mdr_querystats_part_nov search_stats_11  (cost=0.00..0.00 rows=1 width=64) (actual time=0.000..0.001 rows=0 loops=1)              |
                                      Filter: ((search_date >= '2023-01-01 00:00:00'::timestamp without time zone) AND (search_date < '2023-07-01 00:00:00'::timestamp without time zone))                             |
                                ->  Parallel Seq Scan on mdr_querystats_part_dec search_stats_12  (cost=0.00..0.00 rows=1 width=64) (actual time=0.002..0.002 rows=0 loops=1)              |
                                      Filter: ((search_date >= '2023-01-01 00:00:00'::timestamp without time zone) AND (search_date < '2023-07-01 00:00:00'::timestamp without time zone))                             |
Planning:                                                                                                                                                                                                |
  Buffers: shared hit=20                                                                                                                                                                                 |
Planning Time: 0.602 ms                                                                                                                                                                                  |
Execution Time: 16105.124 ms

How can I improve the performance of the query?

Answer 1

Your condition on search_date doesn't seem very selective, so using an index for that is probably not going to help. But avoiding the sort on search_query might help, and to do that you would need an index starting with search_query and including all the columns needed by the query (to get an index-only scan). So that would be on (search_query, user_id, search_date).

I suspect that both parallel query and partitioning don't really help, and they do make the plan harder to understand, so you should look at a plan without both of those.

You say the query takes 30 seconds, but the plan shows it taking 16 seconds. Perhaps the difference is in how long it takes to send the data to the client.