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
>
> here is a tool for sharing explains https://explain.depesz.com/
>

the reason probably will be in using sublans in second case. There should
be ensured so sublan results only one row. Probably better for optimizer in
this case is SELECT WHERE x IN (SELECT ..

> Regards
>
> Pavel
>
>>
>> Best regards
>> Martin Querleu
>>
>>
>>