Re: Strange query planner behavior

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:

so 30. 11. 2019 v 10:55 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:

Hi

so 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 stupid

please try

1. run vacuum analyze on LIVRAISON

2. send result of EXPLAIN ANALYZE SELECT * FROM ... for both cases

3. 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/

Regards

Pavel

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