Re: Strange query planner behavior

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/

Regards

Pavel

>
> Best regards
> Martin Querleu
>
>
>