2

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';
6
  • Just an observation but in both query plans you're getting an index scan on the idx_table2_tenant_id_table1id index which is about 2 million rows big. But the difference between both plans is with memoize disabled, it's scanning the index with parallelism. I could see that being the difference in the time you're seeing. Not sure why this happens or how to influence the plan (if possible) to leverage parallelism with that index when memoize is enabled, except for maybe tuning the query itself to elicit an index seek instead of scan. Having your table and index definitions would be handy here.
    – J.D.
    Jul 24, 2023 at 12:47
  • What plan do you get if you both disable memoize, and set max_parallel_workers_per_gather=0?
    – jjanes
    Jul 24, 2023 at 14:29
  • @J.D. I could add them, but table1 is like ~40 columns and ~10 indexes, so not sure if it'll help much understanding the issue. The main architecture is just many to many relationship between table1 and table3, with table2 being their connecting table
    – Roee
    Jul 24, 2023 at 15:05
  • @jjanes This just gets the query to 20s, I can attach the plan if it changes anything Overall it's worth noting that this query is just one example, I have many more that are without group by that are also using memoize and are just slow
    – Roee
    Jul 24, 2023 at 15:07
  • "I could add them, but table1 is like ~40 columns and ~10 indexes, so not sure if it'll help much understanding the issue." - That's not that bad, the create scripts would be perfect. Typically this is part of the minimal required information for helping discuss performance issues on here.
    – J.D.
    Jul 24, 2023 at 16:20

1 Answer 1

3

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.

1
  • This answer is 100% correct and gave me the indication that something is truly wrong with the planner. Running analyze on the relevant tables made the planner forget the memoize, and make it choose the right plan (also, db is big enough to support that parallelization, settings still appear to be fine, but it was also an important remark). Thanks!
    – Roee
    Jul 31, 2023 at 14:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.