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 
> <[email protected] <mailto:[email protected]>> napsal:
>
>     Hi
>
>     so 30. 11. 2019 v 10:31 odesílatel EffiSYS / Martin Querleu
>     <[email protected] <mailto:[email protected]>> 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)
[email protected]
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 à [email protected]
Vous rencontrez un problème technique au niveau des échanges de données?
=====> écrivez à [email protected]