Re: Postgres does not use indexes with OR-conditions

From: Vlad Arkhipov
Subject: Re: Postgres does not use indexes with OR-conditions
Date: ,
Msg-id: 545C5352.4050309@dc.baikal.ru
(view: Whole thread, Raw)
In response to: Re: Postgres does not use indexes with OR-conditions  (David Rowley)
Responses: Re: Postgres does not use indexes with OR-conditions  (Andrew Dunstan)
List: pgsql-performance

Tree view

Postgres does not use indexes with OR-conditions  (arhipov, )
 Re: Postgres does not use indexes with OR-conditions  (David Rowley, )
  Re: Postgres does not use indexes with OR-conditions  (Vlad Arkhipov, )
   Re: Postgres does not use indexes with OR-conditions  (Andrew Dunstan, )
    Re: Postgres does not use indexes with OR-conditions  (Kevin Grittner, )
     Re: Postgres does not use indexes with OR-conditions  (Tom Lane, )
     Re: Postgres does not use indexes with OR-conditions  (David G Johnston, )

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 <> 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


pgsql-performance by date:

From: Vlad Arkhipov
Date:
Subject: Re: Postgres does not use indexes with OR-conditions
From: Artūras Lapinskas
Date:
Subject: Re: Index order ignored after `is null` in query