It was just a minimal example. The real query looks like this.
select *
from commons.financial_documents fd
where fd.creation_time < '2011-11-07 10:39:07.285022+08'
or (fd.creation_time = '2011-11-07 10:39:07.285022+08' and
fd.financial_document_id < 100)
order by fd.creation_time desc
limit 200
I need to rewrite it in the way below to make Postgres use the index.
select *
from commons.financial_documents fd
where fd.creation_time <= '2011-11-07 10:39:07.285022+08'
and (
fd.creation_time < '2011-11-07 10:39:07.285022+08'
or (fd.creation_time = '2011-11-07 10:39:07.285022+08' and
fd.financial_document_id < 100)
)
order by fd.creation_time desc
limit 200
On 11/07/2014 12:38 PM, David Rowley wrote:
On Fri, Nov 7, 2014 at 5:16 PM, arhipov <arhi...@dc.baikal.ru
<mailto:arhi...@dc.baikal.ru>> wrote:
Hello,
I have just came across interesting Postgres behaviour with
OR-conditions. Are there any chances that the optimizer will
handle this situation in the future?
select *
from commons.financial_documents fd
where fd.creation_time <= '2011-11-07 10:39:07.285022+08'
order by fd.creation_time desc
limit 200
select *
from commons.financial_documents fd
where fd.creation_time = '2011-11-07 10:39:07.285022+08'
or fd.creation_time < '2011-11-07 10:39:07.285022+08'
order by fd.creation_time desc
limit 200
It would certainly be possible, providing the constants compare
equally, but... Question: Would you really want to pay a, say 1%
increase in planning time for ALL queries, so that you could have this
unique case of queries perform better at execution time?
Is there a valid reason why you don't just write the query with the <=
operator?
Regards
David Rowley
