Re: Strange query planner behavior

so 30. 11. 2019 v 10:55 odesílatel Pavel Stehule <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> 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
>>
>>
>>