Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Xmbkp-0000yK-GB for pgsql-performance@arkaria.postgresql.org; Fri, 07 Nov 2014 05:06:35 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Xmbko-00074p-H5 for pgsql-performance@arkaria.postgresql.org; Fri, 07 Nov 2014 05:06:34 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Xmbkn-00074j-Jj for pgsql-performance@postgresql.org; Fri, 07 Nov 2014 05:06:33 +0000 Received: from smtp.dc.baikal.ru ([87.103.128.133]) by makus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1Xmbkj-0001GY-Cx for pgsql-performance@postgresql.org; Fri, 07 Nov 2014 05:06:31 +0000 Received: from [192.168.17.193] (it00.dc-local [192.168.17.193]) (authenticated bits=0) by smtp.dc.baikal.ru (8.13.8/8.13.8) with ESMTP id sA756Ms7006107; Fri, 7 Nov 2014 13:06:22 +0800 Message-ID: <545C5352.4050309@dc.baikal.ru> Date: Fri, 07 Nov 2014 13:06:26 +0800 From: Vlad Arkhipov User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:31.0) Gecko/20100101 Thunderbird/31.2.0 MIME-Version: 1.0 To: David Rowley CC: pgsql-performance Subject: Re: Postgres does not use indexes with OR-conditions References: <545C479B.7010104@dc.baikal.ru> In-Reply-To: Content-Type: multipart/alternative; boundary="------------090204020607000509050701" X-Pg-Spam-Score: -2.5 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multi-part message in MIME format. --------------090204020607000509050701 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit 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 --------------090204020607000509050701 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit 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 <arhipov@dc.baikal.ru> 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

--------------090204020607000509050701--