Strange query planner behavior

EffiSYS / Martin Querleu <[email protected]>
Newsgroups gmane.comp.db.postgresql.bugs
Organization EffiSYS
Message-ID <[email protected]>
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

Best regards
Martin Querleu
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.