Speed up PostgreSQL COUNT request with ORDER BY and multiple INNER JOIN-CodePudding

I have a "complex" request, used from a back office only (2 users), that takes around 5s to perform. I would like to know if there are some tips to reduce this delay.

There are 5M records in each table.
optimized_all is a varchar and it has a BTREE index.
The ORDER BY seems to be the main cause of the delay. When I remove it, it's 80ms...
The website is on a dedicated server.
work_men is currently set to 10Mb on the postgresl.conf

The request:

SELECT
    optimized_all,
    COUNT(optimized_all) AS count_optimized_all
FROM
    "usr_drinks"
    INNER JOIN usr_seasons ON usr_seasons.drink_id = usr_drinks.id
    INNER JOIN usr_photos ON usr_photos.season_id = usr_seasons.id
        AND(usr_photos.verified_kind = 1
            OR usr_photos.verified_kind = 0)
WHERE 
(usr_drinks.optimized_type_id = 1
    AND usr_drinks.optimized_status = 1
    AND usr_seasons.verified_at IS NULL
    )
GROUP BY
    usr_drinks.optimized_all
ORDER BY
    count_optimized_all DESC
LIMIT 10;

Explain Analyze:

Limit  (cost=150022.12..150022.12 rows=1 width=194) (actual time=4813.137..4923.631 rows=1 loops=1)
  ->  Sort  (cost=150022.12..150111.98 rows=35945 width=194) (actual time=4813.136..4923.629 rows=1 loops=1)
        Sort Key: (count(usr_drinks.optimized_all)) DESC
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Finalize GroupAggregate  (cost=144716.68..149842.39 rows=35945 width=194) (actual time=3675.407..4881.022 rows=314695 loops=1)
              Group Key: usr_drinks.optimized_all
              ->  Gather Merge  (cost=144716.68..149297.46 rows=37096 width=101) (actual time=3675.400..4799.409 rows=462144 loops=1)
                    Workers Planned: 4
                    Workers Launched: 4
                    ->  Partial GroupAggregate  (cost=143716.62..143878.91 rows=9274 width=101) (actual time=3647.837..3914.241 rows=92429 loops=5)
                          Group Key: usr_drinks.optimized_all
                          ->  Sort  (cost=143716.62..143739.80 rows=9274 width=93) (actual time=3647.828..3867.945 rows=161362 loops=5)
                                Sort Key: usr_drinks.optimized_all
                                Sort Method: external merge  Disk: 18848kB
                                Worker 0:  Sort Method: external merge  Disk: 16016kB
                                Worker 1:  Sort Method: external merge  Disk: 16016kB
                                Worker 2:  Sort Method: external merge  Disk: 16008kB
                                Worker 3:  Sort Method: external merge  Disk: 15752kB
                                ->  Nested Loop  (cost=1.30..143105.51 rows=9274 width=93) (actual time=12.400..3077.821 rows=161362 loops=5)
                                      ->  Nested Loop  (cost=0.86..104531.30 rows=48751 width=109) (actual time=1.882..1242.603 rows=172132 loops=5)
                                            ->  Parallel Index Scan using usr_drinks_on_optimized_type_idx on usr_drinks  (cost=0.43..35406.66 rows=44170 width=109) (actual time=0.097..216.641 rows=196036 loops=5)
                                                  Index Cond: (optimized_type_id = 1)
                                                  Filter: (optimized_status = 1)
                                                  Rows Removed by Filter: 9387
                                            ->  Index Scan using usr_seasons_on_drink_id_idx on usr_seasons  (cost=0.43..1.54 rows=2 width=32) (actual time=0.005..0.005 rows=1 loops=980181)
                                                  Index Cond: (drink_id = usr_drinks.id)
                                                  Filter: (verified_at IS NULL)
                                                  Rows Removed by Filter: 0
                                      ->  Index Scan using usr_photos_on_season_id_idx on usr_photos  (cost=0.43..0.78 rows=1 width=16) (actual time=0.008..0.010 rows=1 loops=860662)
                                            Index Cond: (season_id = usr_seasons.id)
                                            Filter: ((verified_kind = 1) OR (verified_kind = 0))
                                            Rows Removed by Filter: 1
Planning Time: 1.120 ms
Execution Time: 4927.502 ms

Possible solution ?: Storing the count in another table, but for my needs, it seems quite complicate to update the counters. Any new idea is welcome.

EDIT 1: I removed the 2 unnecessary INNER JOIN. Now there are only 2.

EDIT 2: I tried to replace the last 2 INNER JOIN by a double EXIST condition. I saved only 1 second. (request is now 4 seconds instead of 1)

SELECT
    optimized_all,
    COUNT(optimized_all) AS count_optimized_all
FROM
    "usr_drinks"
WHERE (usr_drinks.optimized_type_id = 1
    AND usr_drinks.optimized_status = 1)
AND EXISTS (
    SELECT
        *
    FROM
        usr_seasons
    WHERE
        usr_seasons.drink_id = usr_drinks.id
        AND usr_seasons.verified_at IS NULL
        AND EXISTS (
            SELECT
                *
            FROM
                usr_photos
            WHERE
                usr_photos.season_id = usr_seasons.id
                AND(usr_photos.verified_kind = 1
                    OR usr_photos.verified_kind = 0)))
    GROUP BY
        usr_drinks.optimized_all
    ORDER BY
        count_optimized_all DESC
    LIMIT 10;

EDIT 3: the current postgresql.conf settings are:

max_connections = 100
shared_buffers = 6GB
effective_cache_size = 18GB
maintenance_work_mem = 1536MB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 10485kB
min_wal_size = 1GB
max_wal_size = 2GB
max_worker_processes = 12
max_parallel_workers_per_gather = 6
max_parallel_workers = 12

Increasing work_mem, even to 256MB, doesn't help (surely because my disk is a SSD) ?

CodePudding user response：

You are facing a structural problem of PostGreSQL which is unable to correctly optimize queries of type COUNT or SUM. This is due to PostGreSQL's internal architecture due to the way PostGreSQL handles MVCC (Multi Versioning Concurrency Control).

Take a look at the article I wrote about it.

The only way around this problem is to use a materialized view.

CodePudding user response：

As I didn't find a way to speed up massively the request, due to the ORDER BY count delay, this is what I did:

I created a new table that stores the optimized_all field with the corresponding optimized_all_count - I didn't want to do this firstly, but it was just a 3 hours work for me.
I run a task once a day that fill this table with INSERT...SELECT... and the long request (it's a rails app)
Now, I just search in this new table... it's just a few milliseconds of course.

This is completely acceptable for my needs (an admin tool), but could not correspond to other scenarios. Thanks to everybody for your suggestions.