Re: Explain Plan - Lower Estimated Time but Higher Cost

Поиск
Список
Период
Сортировка
От Greg Rychlewski (LCL)
Тема Re: Explain Plan - Lower Estimated Time but Higher Cost
Дата
Msg-id DM6PR04MB6713292F92C93FA4251A5AF0FBB69@DM6PR04MB6713.namprd04.prod.outlook.com
обсуждение исходный текст
Ответ на Explain Plan - Lower Estimated Time but Higher Cost  ("Greg Rychlewski (LCL)" <Greg.Rychlewski@loblaw.ca>)
Список pgsql-novice
Дерево обсуждения
I'm not an expert, but one of the problems looks like it expects the cost of reading 932 rows from this index:

Index Scan using product_id_index on public.prices o0  (cost=0.57..8.38 rows=1 width=214) (actual time=0.049..3.731 rows=932 loops=1)

is about the same as reading 1 row from this index:

Index Scan using store_product_id_unique_index on public.prices o0  (cost=0.57..8.60 rows=1 width=214) (actual time=0.032..0.033 rows=1 loops=1)

which doesn't appear correct based on the time estimates.

The first index has 6743200 tuples while the second has 257224685 tuples. Could this be related to the problem with the estimate?

From: Greg Rychlewski (LCL)
Sent: Sunday, January 31, 2021 6:17 PM
To: pgsql-novice@postgresql.org <pgsql-novice@postgresql.org>
Subject: Explain Plan - Lower Estimated Time but Higher Cost
 
I have a query where I'm joining 4 tables together and the optimizer is picking a plan with much higher estimated execution time because it has lower cost. I'm wondering what could cause this and if there is anything I can do to help it pick the quicker plan.

The only difference between the two plans is the join order, which makes the prices​ table use a different index. The higher cost plan uses store_product_id_unique_index​ (257224685 tuples) vs product_id_index​ (6743200 tuples) for the lower cost plan.

Here is plan with high cost/low execution time.


 Nested Loop  (cost=1.28..21.27 rows=1 width=340) (actual time=0.080..0.083 rows=1 loops=1)
   Output: ...
   Join Filter: ((o0.product_id)::text = o.product_id)
   Buffers: shared hit=13
   ->  Function Scan on pg_catalog.unnest o  (cost=0.00..0.01 rows=1 width=40) (actual time=0.004..0.004 rows=1 loops=1)
         Output: ...
         Function Call: unnest('{12345}'::text[])
   ->  Nested Loop  (cost=1.28..21.25 rows=1 width=346) (actual time=0.074..0.076 rows=1 loops=1)
         Output: ...
         Inner Unique: true
         Buffers: shared hit=13
         ->  Nested Loop  (cost=0.85..16.91 rows=1 width=332) (actual time=0.050..0.052 rows=1 loops=1)
               Output: ...
               Buffers: shared hit=8
               ->  Index Scan using stores_store_type_store_id_index on public.stores v2  (cost=0.28..8.30 rows=1 width=118) (actual time=0.016..0.016 rows=1 loops=1)
                     Output: ...
                     Index Cond: (((v2.store_type)::text = 'a'::text) AND ((v2.store_id)::text = '01'::text))
                     Buffers: shared hit=3
               ->  Index Scan using store_product_id_unique_index on public.prices o0  (cost=0.57..8.60 rows=1 width=214) (actual time=0.032..0.033 rows=1 loops=1)
                     Output: ...
                     Index Cond: ((o0.store_id = v2.id) AND ((o0.product_id)::text = ANY ('{12345}'::text[])))
                     Filter: ((o0.valid_from <= '2021-01-28 00:00:00'::timestamp without time zone) AND (o0.valid_to >= '2021-01-28 00:00:00'::timestamp without time zone))
                     Buffers: shared hit=5
         ->  Index Only Scan using product_id_index on public.products p1  (cost=0.43..4.34 rows=1 width=14) (actual time=0.023..0.023 rows=1 loops=1)
               Output: ...
               Index Cond: (p1.product_id = (o0.product_id)::text)
               Heap Fetches: 1
               Buffers: shared hit=5
 Planning:
   Buffers: shared hit=32
 Planning Time: 0.800 ms
 Execution Time: 0.144 ms


Here is the plan with low cost/high execution time 

 Nested Loop  (cost=1.28..21.16 rows=1 width=340) (actual time=2.648..9.154 rows=1 loops=1)
   Output: ...
   Inner Unique: true
   Join Filter: (o0.store_id = v2.id)
   Rows Removed by Join Filter: 931
   Buffers: shared hit=3760
   ->  Nested Loop  (cost=1.00..12.84 rows=1 width=222) (actual time=0.095..4.301 rows=932 loops=1)
         Output: ...
         Buffers: shared hit=964
         ->  Nested Loop  (cost=0.43..4.46 rows=1 width=54) (actual time=0.045..0.049 rows=1 loops=1)
               Output: ...
               Inner Unique: true
               Buffers: shared hit=5
               ->  Function Scan on pg_catalog.unnest o  (cost=0.00..0.01 rows=1 width=40) (actual time=0.004..0.007 rows=1 loops=1)
                     Output: ...
                     Function Call: unnest('{12345}'::text[])
               ->  Index Only Scan using product_id_index on public.products p1  (cost=0.43..4.45 rows=1 width=14) (actual time=0.038..0.038 rows=1 loops=1)
                     Output: ...
                     Index Cond: (p1.product_id = o.product_id)
                     Heap Fetches: 1
                     Buffers: shared hit=5
         ->  Index Scan using product_id_index on public.prices o0  (cost=0.57..8.38 rows=1 width=214) (actual time=0.049..3.731 rows=932 loops=1)
               Output: ...
               Index Cond: (((o0.product_id)::text = (p1.product_id)::text) AND ((o0.product_id)::text = ANY ('{12345}'::text[])))
               Filter: ((o0.valid_from <= '2021-01-28 00:00:00'::timestamp without time zone) AND (o0.valid_to >= '2021-01-28 00:00:00'::timestamp without time zone))
               Rows Removed by Filter: 20
               Buffers: shared hit=959
   ->  Index Scan using stores_store_type_store_id_index on public.stores v2  (cost=0.28..8.30 rows=1 width=118) (actual time=0.005..0.005 rows=1 loops=932)
         Output: ...
         Index Cond: (((v2.store_type)::text = 'a'::text) AND ((v2.store_id)::text = '01'::text))
         Buffers: shared hit=2796
 Planning:
   Buffers: shared hit=51
 Planning Time: 1.139 ms
 Execution Time: 9.254 ms

This email message is confidential, may be legally privileged and is intended for the exclusive use of the addressee. If you received this message in error or are not the intended recipient, you should destroy the email message and any attachments or copies, and you are prohibited from retaining, distributing, disclosing or using any information contained. Please inform us of the delivery error by return email. Thank you for your cooperation.

Le présent message électronique est confidentiel et peut être couvert par le secret professionnel. Il est à l’usage exclusif du destinataire. Si vous recevez ce message par erreur ou si vous n’en êtes pas le destinataire prévu, vous devez détruire le message et toute pièce jointe ou copie et vous êtes tenu de ne pas conserver, distribuer, divulguer ni utiliser tout renseignement qu’il contient. Veuillez nous informer de toute erreur d’envoi en répondant à ce message. Merci de votre collaboration.

В списке pgsql-novice по дате отправления:

Предыдущее
От: "Greg Rychlewski (LCL)"
Дата:
Сообщение: Explain Plan - Lower Estimated Time but Higher Cost
Следующее
От: Santosh Udupi
Дата:
Сообщение: Could not find the table 'pgagent.pga_job'. Have you run pgagent.sql on this database?