Re: Postgres does not use indexes with OR-conditions

Kevin Grittner-5 wrote
> Andrew Dunstan <

> andrew@

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

From personal experience and observation on these lists record inequality is
not particularly intuitive. I'm also not sure someone is likely to really
"get it" until they have a problem for which the above is the solution.

That said is there a place where we supply solutions and idioms to common
queries? This query as well as pagination-oriented queries are two that
come to mind. I think the material would fit well in the tutorial section
but having some kind of quick synopsis and cross reference in the
performance chapter would aid someone whose looking to solve a problem and
not in general education mode.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Postgres-does-not-use-indexes-with-OR-conditions-tp5826027p5826065.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.