Re: Postgres does not use indexes with OR-conditions

Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> On 11/07/2014 12:06 AM, Vlad Arkhipov wrote:

>> 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
>
> Could you not rewrite it as something this?:
>
> 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.financial_document_id < 100)

Yeah, when there are two ways to write a query that are logically
equivalent, it is better to put the AND at the higher level than
the OR. On the other hand, why not simply write it as?:

select *
from commons.financial_documents fd
where (fd.creation_time, fd.financial_document_id)
< ('2011-11-07 10:39:07.285022+08', 100)
order by fd.creation_time desc
limit 200

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company