Re: Strange query planner behavior
От | EffiSYS / Martin Querleu |
---|---|
Тема | Re: Strange query planner behavior |
Дата | |
Msg-id | 4d3aeef2-85c1-e9e2-d4d8-396a0358fbe2@effisys.fr обсуждение исходный текст |
Ответ на | Re: Strange query planner behavior (Tomas Vondra <tomas.vondra@2ndquadrant.com>) |
Список | pgsql-bugs |
Hi Tomas I tried the method of using IN instead of =, it's not really better in the present case: EFT_MBON=# explain analyse select * from livraison where id_master in (select 10); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ Nested Loop Semi Join (cost=0.00..211841.54 rows=1919474 width=697) (actual time=1245.271..1245.271 rows=0 loops=1) Join Filter: (livraison.id_master = (10)) Rows Removed by Join Filter: 1921796 -> Seq Scan on livraison (cost=0.00..201476.37 rows=1919474 width=697) (actual time=0.006..211.196 rows=1921796 loops=1) -> Materialize (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1921796) -> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1) Planning Time: 0.200 ms Execution Time: 1245.309 ms (8 lignes) I tried many different query planner cost variables (including defaults) but no impact, as expected (default for random_page_cost is 4 so 15 is about 3-4 times the default) This was a very precise case of a special data distribution, so we'll go for a workaround on this one Best regards and thanks Martin On 30/11/2019 15:53, Tomas Vondra wrote: > On Sat, Nov 30, 2019 at 11:29:53AM +0100, EffiSYS / Martin Querleu wrote: >> Hi Pavel >> >> Thanks for the fast reply >> Our databases are VACUUMed everyday. I did it again but no difference >> >> Here are the query plans: >> >> EFT_MBON=# explain analyse select * from livraison where id_master = 10; >> QUERY PLAN >> -------------------------------------------------------------------------------------------------------------------------------------------- >> >> Index Scan using pour_recherche_sous_livraison on livraison >> (cost=0.03..15.04 rows=1 width=697) (actual time=0.017..0.017 rows=0 >> loops=1) >> Index Cond: (id_master = 10) >> Planning Time: 0.124 ms >> Execution Time: 0.036 ms >> (4 lignes) >> >> EFT_MBON=# explain analyse select * from livraison where id_master = >> (select 10); >> QUERY PLAN >> ----------------------------------------------------------------------------------------------------------------------- >> >> Seq Scan on livraison (cost=0.01..2888156.69 rows=1917632 >> width=697) (actual time=1334.615..1334.615 rows=0 loops=1) >> Filter: (id_master = $0) >> Rows Removed by Filter: 1918196 >> InitPlan 1 (returns $0) >> -> Result (cost=0.00..0.01 rows=1 width=4) (actual >> time=0.000..0.001 rows=1 loops=1) >> Planning Time: 0.138 ms >> Execution Time: 1334.642 ms >> (7 lignes) >> >> Regarding the cost calculator the configuration is as follows: >> >> random_page_cost and seq_page_cost are identical since the data is >> 100% in RAM (both at 15.0, 3 times default) > > Ummm, what? Does this mean you have > random_page_cost = 15 > seq_page_cost = 15 > > Neither of that is 3 times the default value, though, so maybe I just > don't understand correctly. > > regards > -- Martin Querleu - Directeur Général EffiSYS (www.effitrace.fr - www.logistique-e-commerce.fr) martin.querleu@effisys.fr 3, rue Gustave Delory 59000 Lille Tél: +33 9 54 28 38 76 Vous rencontrez un problème d'utilisation sur effitr@ce? =====> écrivez à support@effisys.fr Vous rencontrez un problème technique au niveau des échanges de données? =====> écrivez à supervision@effisys.fr
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Michael PaquierДата:
Сообщение: Re: logical replication: could not create file "state.tmp": Fileexists
Следующее
От: Petr FedorovДата:
Сообщение: Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost whenextracting epoch