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(dot)stehule(at)gmail(dot)com <mailto:pavel(dot)stehule(at)gmail(dot)com>> napsal:
>
> Hi
>
> so 30. 11. 2019 v 10:31 odesílatel EffiSYS / Martin Querleu
> <martin(dot)querleu(at)effisys(dot)fr <mailto:martin(dot)querleu(at)effisys(dot)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(dot)querleu(at)effisys(dot)fr
3, rue Gustave Delory
59000 Lille
Tél: +33 9 54 28 38 76

Vous rencontrez un problème d'utilisation sur effitr(at)ce?
=====> écrivez à support(at)effisys(dot)fr
Vous rencontrez un problème technique au niveau des échanges de données?
=====> écrivez à supervision(at)effisys(dot)fr