Date: Fri, 7 Nov 2014 08:50:26 -0800 (PST)
From: David G Johnston <david.g.johnston@gmail.com>
To: pgsql-performance@postgresql.org
Message-ID: <1415379026083-5826065.post@n5.nabble.com>
In-Reply-To: <1415369851.48766.YahooMailNeo@web122305.mail.ne1.yahoo.com>
References: <545C479B.7010104@dc.baikal.ru>
 <CAApHDvr0X+eYy-bZJh-ch3Kr62BJkk=ZWACyndMKJsUBYWjQpg@mail.gmail.com>
 <545C5352.4050309@dc.baikal.ru> <545CCF54.1010205@dunslane.net>
 <1415369851.48766.YahooMailNeo@web122305.mail.ne1.yahoo.com>
Subject: Re: Postgres does not use indexes with OR-conditions
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Kevin Grittner-5 wrote
> Andrew Dunstan &lt;

> andrew@

> &gt; 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