References: <545C479B.7010104@dc.baikal.ru>
 <CAApHDvr0X+eYy-bZJh-ch3Kr62BJkk=ZWACyndMKJsUBYWjQpg@mail.gmail.com>
 <545C5352.4050309@dc.baikal.ru> <545CCF54.1010205@dunslane.net>
Message-ID: <1415369851.48766.YahooMailNeo@web122305.mail.ne1.yahoo.com>
Date: Fri, 7 Nov 2014 06:17:31 -0800
From: Kevin Grittner <kgrittn@ymail.com>
Reply-To: Kevin Grittner <kgrittn@ymail.com>
Subject: Re: Postgres does not use indexes with OR-conditions
To: Andrew Dunstan <andrew@dunslane.net>,
 Vlad Arkhipov <arhipov@dc.baikal.ru>, David Rowley <dgrowleyml@gmail.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
In-Reply-To: <545CCF54.1010205@dunslane.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Andrew Dunstan <andrew@dunslane.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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance