Re: Strange query planner behavior
| От | EffiSYS / Martin Querleu |
|---|---|
| Тема | Re: Strange query planner behavior |
| Дата | |
| Msg-id | b4834a66-c023-01c2-3b11-fb4af0d1e262@effisys.fr обсуждение исходный текст |
| Ответ на | Re: Strange query planner behavior (Pavel Stehule <pavel.stehule@gmail.com>) |
| Ответы |
Re: Strange query planner behavior
(Pavel Stehule <pavel.stehule@gmail.com>)
Re: Strange query planner behavior (Tomas Vondra <tomas.vondra@2ndquadrant.com>) |
| Список | pgsql-bugs |
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)
cpu_tuple_cost at 0.005 (half default)
cpu_index_tuple_cost at 0.00025 (half defaut)
cpu_operator_cost at 0.00025 (default, by the way I assume we should lower it at 0.0001)
I would expect the seq scan to be more costly than default since both page_cost are higher and cpu_index_tuple_cost lower
I think the main question is whether the query planner is able to pre calculate subqueries with = to use the value returned to get the good query plan
Best regards
Martin
On 30/11/2019 11:00, Pavel Stehule wrote:
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)
cpu_tuple_cost at 0.005 (half default)
cpu_index_tuple_cost at 0.00025 (half defaut)
cpu_operator_cost at 0.00025 (default, by the way I assume we should lower it at 0.0001)
I would expect the seq scan to be more costly than default since both page_cost are higher and cpu_index_tuple_cost lower
I think the main question is whether the query planner is able to pre calculate subqueries with = to use the value returned to get the good query plan
Best regards
Martin
On 30/11/2019 11:00, Pavel Stehule wrote:
so 30. 11. 2019 v 10:55 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:Hiso 30. 11. 2019 v 10:31 odesílatel EffiSYS / Martin Querleu <martin.querleu@effisys.fr> napsal:Hello
I have a strange problem with the query planner on Postgresql 11.5 on
Debian stretch, the plan differs between the following 2 requests:
- SELECT * FROM LIVRAISON WHERE ID_MASTER = 10 which uses a btree index
on ID_MASTER (the table has 1M rows). Everything is normal
- SELECT * FROM LIVRAISON WHERE ID_MASTER = (SELECT 10) which uses a seq
scan and is 3000 times slower
I don't understand how the planner cannot consider that a subselect with
an = is equivalent to having = VALUE (the subselect either returning 1
row or NULL)
I don't have the same behavior on other column with indexes of the same
table, maybe it's because 99% or the table has ID_MASTER = 0? I can
understand that if the value returned by the subquery is 0 the seqscan
could be faster (in our case it is still slower than index scan but only
by 2 times), but if the subquery does not return 0 in no case the
seqscan could be faster. The question is why is the subquery not
calculated before choosing wether to use the index or not since it will
return a single value?
Thanks for your reply and sorry if the question is stupidplease try1. run vacuum analyze on LIVRAISON2. send result of EXPLAIN ANALYZE SELECT * FROM ... for both cases3. do you have some custom settings of planner configuration variables like random_page_cost, seq_page_cost?here is a tool for sharing explains https://explain.depesz.com/RegardsPavel
Best regards
Martin Querleu
-- 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 по дате отправления: