Re: Strange query planner behavior
Pavel Stehule <[email protected]>
| Newsgroups | gmane.comp.db.postgresql.bugs |
|---|---|
| Message-ID | <CAFj8pRB4CLf-m3KMjAiQ3QUpLCX38wiJTKzzuyyAE6MCOeff4w@mail.gmail.com> |
so 30. 11. 2019 v 10:55 odesílatel Pavel Stehule <[email protected]> napsal: > Hi > > so 30. 11. 2019 v 10:31 odesílatel EffiSYS / Martin Querleu < > [email protected]> 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 >> >> >>