Sample Header Ad - 728x90

random_page_cost and memoize plan relation

2 votes
1 answer
289 views
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.
Asked by Cowabunga (145 rep)
Jun 16, 2023, 04:31 PM
Last activity: May 13, 2025, 05:03 PM