Postgres15 memoize slows down many queries

Question

I have a query that uses joins and group by. The tables it's working on consists of a few millions records (10-50mil), one of them is partitioned (unsure if relevant but providing as much info as possible). For some reason, this query (and many others from my research, doesn't necessarily have to contain group by) is running kind of slow, but when changing enable_memoize=false it runs in almost half the time.

Why does this happen? All around the web memoize is glorified as a great new feature that improves many queries.

Is there any psql settings that needs to be changed in order to make memoize work fast/not get chosen by the planner since it's inferior plan (which is not straight up disabling memoize)?

The query itself:

EXPLAIN ANALYZE 
SELECT table3.id, table3.type, count(*) FROM table1 
 JOIN table2 ON table1.id=table2.table1_id AND table1.tenant_id=table2.tenant_id
 JOIN table3 ON table2.table3_id=table3.id AND table2.tenant_id=table3.tenant_id
WHERE table1.tenant_id=123 
GROUP BY table3.id, table3.type
ORDER BY count(*) DESC LIMIT 10;

The output plan:

 Limit  (cost=79555.10..79555.12 rows=10 width=45) (actual time=11017.288..11017.294 rows=10 loops=1)
   ->  Sort  (cost=79555.10..80128.80 rows=229481 width=45) (actual time=11017.286..11017.291 rows=10 loops=1)
         Sort Key: (count(*)) DESC
         Sort Method: top-N heapsort  Memory: 26kB
         ->  HashAggregate  (cost=68715.65..74596.10 rows=229481 width=45) (actual time=9814.014..10892.716 rows=814630 loops=1)
               Group Key: table3.id
               Planned Partitions: 4  Batches: 33  Memory Usage: 9265kB  Disk Usage: 105720kB
               ->  Merge Join  (cost=5.67..59034.42 rows=229481 width=37) (actual time=0.101..8846.868 rows=1912806 loops=1)
                     Merge Cond: (table2.table1_id = table1.id)
                     ->  Nested Loop  (cost=0.87..184852.18 rows=919397 width=53) (actual time=0.062..7766.353 rows=1912806 loops=1)
                           ->  Index Scan using idx_table2_tenant_id_table1id on table2  (cost=0.43..124987.43 rows=1872626 width=24) (actual time=0.034..1710.167 rows=1912806 loops=1)
                                 Index Cond: (tenant_id = 123)
                           ->  Memoize  (cost=0.44..0.62 rows=1 width=45) (actual time=0.003..0.003 rows=1 loops=1912806)
                                 Cache Key: table2.table3_id
                                 Cache Mode: logical
                                 Hits: 1040220  Misses: 872586  Evictions: 816079  Overflows: 0  Memory Usage: 8389kB
                                 ->  Index Scan using table3_pkey on table3  (cost=0.43..0.61 rows=1 width=45) (actual time=0.004..0.004 rows=1 loops=872586)
                                       Index Cond: (id = table2.table3_id)
                                       Filter: (tenant_id = 123)
                     ->  Index Only Scan using table1_partition_123_pkey on table1_partition_123 table1  (cost=0.43..39581.09 rows=1683017 width=16) (actual time=0.035..455.399 rows=1912806 loops=1)
                           Index Cond: (tenant_id = 123)
                           Heap Fetches: 9346
 Planning Time: 7.250 ms
 Execution Time: 11038.258 ms

Output plan after settings enable_memoize=false:

  Limit  (cost=102850.70..102850.72 rows=10 width=45) (actual time=6040.773..6040.960 rows=10 loops=1)
   ->  Sort  (cost=102850.70..103424.40 rows=229481 width=45) (actual time=6040.772..6040.957 rows=10 loops=1)
         Sort Key: (count(*)) DESC
         Sort Method: top-N heapsort  Memory: 26kB
         ->  HashAggregate  (cost=92011.24..97891.69 rows=229481 width=45) (actual time=4841.865..5916.868 rows=814630 loops=1)
               Group Key: table3.id
               Planned Partitions: 4  Batches: 33  Memory Usage: 9265kB  Disk Usage: 105720kB
               ->  Merge Join  (cost=1005.72..82330.01 rows=229481 width=37) (actual time=10.344..3868.398 rows=1912806 loops=1)
                     Merge Cond: (table2.table1_id = table1.id)
                     ->  Gather Merge  (cost=1000.92..508058.81 rows=919397 width=53) (actual time=10.288..2796.661 rows=1912806 loops=1)
                           Workers Planned: 4
                           Workers Launched: 4
                           ->  Nested Loop  (cost=0.86..397549.71 rows=229849 width=53) (actual time=0.071..2360.817 rows=382561 loops=5)
                                 ->  Parallel Index Scan using idx_table2_tenant_id_table1id on table2  (cost=0.43..110942.73 rows=468156 width=24) (actual time=0.040..403.754 rows=382561 loops=5)
                                       Index Cond: (tenant_id = 123)
                                 ->  Index Scan using table3_pkey on table3  (cost=0.43..0.61 rows=1 width=45) (actual time=0.004..0.004 rows=1 loops=1912806)
                                       Index Cond: (id = table2.table3_id)
                                       Filter: (tenant_id = 123)
                     ->  Index Only Scan using table1_partition_123_pkey on table1_partition_123 table1  (cost=0.43..39581.09 rows=1683017 width=16) (actual time=0.052..460.241 rows=1912806 loops=1)
                           Index Cond: (tenant_id = 123)
                           Heap Fetches: 9346
 Planning Time: 0.907 ms
 Execution Time: 6061.154 ms

Psql settings that may be relevant(?)

max_parallel_workers_per_gather=4;
max_parallel_workers=32;
max_parallel_maintenance_workers=4;
random_page_cost=1.1;
work_mem='4194kB';

Answer 1

It is hard to give exact details here without having access to your data and machine, but the broad strokes seem pretty clear to me.

The planner thinks it can benefit from memoize, and also things it can benefit from parallel query. But it doesn't think it can benefit from using both in conjunction (if each worker has its own cache, then the hit rate drops as they won't see each others cache, while if they have a shared cache they have to pay a lot of locking costs to not corrupt each other), so it has to choose between them. And it makes the wrong choice. Either it overestimates the benefit of memoizing, or (what I think is more likely) it underestimates the benefit of parallelizing. Probably because the default setting of parallel_tuple_cost is quite high, which discourages parallel queries. And this would be compounded by your low setting of random_page_cost, as doing random reads in parallel is much of the perceived benefit of parallelizing, so lowering that cost also lowers the benefit.

But you also have to remember that parallelization is not free. If you use 5 times more resources to get an answer 2 times faster, that is great if those other resources would otherwise go unused. But if your production server has other things going on at the same time which could have used those resources, then doing each one of things in parallel is a net loss even if in isolation each would be a gain.