2

Postgres 15.2. I have the following simple query:

SELECT mark.last_modified FROM mark
    INNER JOIN element ON mark.element_id = element.id
    INNER JOIN model ON element.model_id = model.id
WHERE model.brand_id = '9cb22c38-af8a-4347-988e-1b2287122d39'
ORDER BY mark.last_modified DESC LIMIT 1;

and my DB is configured with page_random_cost = 3.66 (should be closer to 1 - I know). I analyze the query and notice it use memoize and the following plan:

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit  (cost=20338.19..30531.32 rows=1 width=8) (actual time=10588.047..10588.137 rows=0 loops=1)
Buffers: shared hit=1209004 read=8403
I/O Timings: shared/local read=8475.963
->  Nested Loop  (cost=20338.19..1335251.72 rows=129 width=8) (actual time=10588.046..10588.135 rows=0 loops=1)
        Buffers: shared hit=1209004 read=8403
        I/O Timings: shared/local read=8475.963
        ->  Nested Loop  (cost=20337.90..1326623.17 rows=278958 width=24) (actual time=101.060..10457.145 rows=277857 loops=1)
            Buffers: shared hit=1205893 read=8403
            I/O Timings: shared/local read=8475.963
            ->  Gather Merge  (cost=20337.32..52826.58 rows=278958 width=24) (actual time=101.016..183.845 rows=277857 loops=1)
                    Workers Planned: 2
                    Workers Launched: 0
                    Buffers: shared hit=8396
                    ->  Sort  (cost=19337.30..19627.88 rows=116232 width=24) (actual time=100.708..147.960 rows=277857 loops=1)
                        Sort Key: mark.last_modified DESC
                        Sort Method: quicksort  Memory: 29655kB
                        Buffers: shared hit=8396
                        ->  Parallel Seq Scan on mark  (cost=0.00..9558.33 rows=116232 width=24) (actual time=0.008..43.455 rows=277857 loops=1)
                                Buffers: shared hit=8396
            ->  Memoize  (cost=0.57..5.24 rows=1 width=32) (actual time=0.037..0.037 rows=1 loops=277857)
                    Cache Key: mark.element_id
                    Cache Mode: logical
                    Hits: 36677  Misses: 241180  Evictions: 0  Overflows: 0  Memory Usage: 33916kB
                    Buffers: shared hit=1197497 read=8403
                    I/O Timings: shared/local read=8475.963
                    ->  Index Scan using activity_pkey on element  (cost=0.56..5.23 rows=1 width=32) (actual time=0.041..0.041 rows=1 loops=241180)
                        Index Cond: (id = mark.element_id)
                        Buffers: shared hit=1197497 read=8403
                        I/O Timings: shared/local read=8475.963
        ->  Memoize  (cost=0.29..0.31 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=277857)
            Cache Key: element.model_id
            Cache Mode: logical
            Hits: 276820  Misses: 1037  Evictions: 0  Overflows: 0  Memory Usage: 82kB
            Buffers: shared hit=3111
            ->  Index Scan using model_pkey on model  (cost=0.28..0.30 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=1037)
                    Index Cond: (id = element.model_id)
                    Filter: (brand_id = '9cb22c38-af8a-4347-988e-1b2287122d39'::uuid)
                    Rows Removed by Filter: 1
                    Buffers: shared hit=3111
Planning:
Buffers: shared hit=1045
Planning Time: 1.985 ms
Execution Time: 10598.922 ms
(43 rows)

However, when I change random_page_cost to a lower value (=1.1) it change the plan and it doesn't use the memoize anyone:

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit  (cost=16808.48..16808.49 rows=1 width=8) (actual time=425.764..425.765 rows=0 loops=1)
Buffers: shared hit=348291 read=212
I/O Timings: shared/local read=173.481
->  Sort  (cost=16808.48..16808.81 rows=129 width=8) (actual time=425.763..425.764 rows=0 loops=1)
        Sort Key: mark.last_modified DESC
        Sort Method: quicksort  Memory: 25kB
        Buffers: shared hit=348291 read=212
        I/O Timings: shared/local read=173.481                                                                                   QUERY PLAN
        ->  Nested Loop  (cost=1.27..16807.84 rows=129 width=8) (actual time=425.738..425.739 rows=0 loops=1)
            Buffers: shared hit=348288 read=212
            I/O Timings: shared/local read=173.481
            ->  Nested Loop  (cost=0.84..12253.80 rows=10110 width=16) (actual time=0.041..55.468 rows=111456 loops=1)
                    Buffers: shared hit=14132
                    ->  Index Scan using model_brand_id_95f0c5ac on model  (cost=0.28..4.33 rows=3 width=16) (actual time=0.028..0.033 rows=3 loops=1)
                        Index Cond: (brand_id = '9cb22c38-af8a-4347-988e-1b2287122d39'::uuid)
                        Buffers: shared hit=4
                    ->  Index Scan using element_model_id_c798104e on element  (cost=0.56..4043.31 rows=3984 width=32) (actual time=0.009..14.143 rows=37152 loops=3)
                        Index Cond: (model_id = model.id)
                        Buffers: shared hit=14128
            ->  Index Scan using mark_element_id_4d370815 on mark  (cost=0.42..0.44 rows=1 width=24) (actual time=0.003..0.003 rows=0 loops=111456)
                    Index Cond: (element_id = element.id)
                    Buffers: shared hit=334156 read=212
                    I/O Timings: shared/local read=173.481
Planning:
Buffers: shared hit=514
Planning Time: 0.649 ms
Execution Time: 425.799 ms
(27 rows)

Besides the fact that now the query performance is better, I would like to understand the relation between random_page_cost and the lake of use of memoize in the plan.

Improve this question
4
  • The memoize is probably irrelevant here. The important difference is the seq scan. If you set enable_memoize to off, does it make much of a difference?
    – jjanes
    Jun 16 at 21:08
  • @jjanes yes, setting enable_memoize to off result with the same plan (like lower random_page_cost). However, as fas as I understand when setting random_page_cost to high value Postgres calculate the use of index scan to be expensive (and probably prefer sequence scans instead). But here the significant part is not the parallel seq scan but rather the Index Scan using activity_pkey and memoize
    – Cowabunga
    Jun 17 at 9:50
  • What plan do you get if you force it to use the same overall plan as the bad plan, but without the memoize? You can probably get this happen if you disable memoize, while at the same time disabling the alternative index by writing the condition as WHERE model.brand_id||'' = '9cb22c38-af8a-4347-988e-1b2287122d39'.
    – jjanes
    Jun 19 at 20:52
  • How do you know the parallel seq scan is not signifcant? If you set max_parallel_workers_per_gather to 0, does it still use the bad plan? If so, please post that plan, as non-parallel plans are much easier to reliably think through than parallel ones are.
    – jjanes
    Jun 19 at 20:54

1 Answer 1

Reset to default
0

I think there is nothing very special going on between the memoize and the random_page_cost. Perhaps memoize allowed the bad plan to get chosen, but something else just as easily could have done the same thing as well/instead (like effective_cache_size, or max_parallel_workers_per_gather, or index-only scans, or hash joins, or just a different random sample used to calculate the stats).

The more fundamental problem is that it thinks it will find 129 rows (neglecting the the LIMIT kicking in) and therefore will get to stop early after doing only 1/129th of the work (once it accounts for the LIMIT) of the nested memoize/index scans. But in fact it finds 0 rows, and so doesn't get to stop early at all. If you could fix this massive misestimation, then whatever else is going on probably wouldn't matter. (Unfortunately, I don't see a likely way to fix that misestimate.)

There might be something more specific going on, but without a way to generate data which reproduces this phenomenon it will hard to assess that more carefully (when I try to reproduce it I get hash joins instead of nested loop/memoize combinations).

Improve this answer

Your Answer

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

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