From: | Vlad Arkhipov <arhipov(at)dc(dot)baikal(dot)ru> |
---|---|
To: | David Rowley <dgrowleyml(at)gmail(dot)com> |
Cc: | pgsql-performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Postgres does not use indexes with OR-conditions |
Date: | 2014-11-07 05:06:26 |
Message-ID: | 545C5352.4050309@dc.baikal.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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 <arhipov(at)dc(dot)baikal(dot)ru
> <mailto:arhipov(at)dc(dot)baikal(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Artūras Lapinskas | 2014-11-07 11:14:51 | Re: Index order ignored after `is null` in query |
Previous Message | David Rowley | 2014-11-07 04:38:13 | Re: Postgres does not use indexes with OR-conditions |