From: | EffiSYS / Martin Querleu <martin(dot)querleu(at)effisys(dot)fr> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Strange query planner behavior |
Date: | 2019-11-30 08:31:08 |
Message-ID: | 798855a5-4abe-b1d0-68ab-f21966d2c3a1@effisys.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Petr Fedorov | 2019-11-30 09:28:18 | Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch |
Previous Message | Tomas Vondra | 2019-11-29 21:37:20 | Re: BUG #16142: host down |