From: Andrew Dunstan <andrew@dunslane.net>
To: Vlad Arkhipov <arhipov@dc.baikal.ru>
To: David Rowley <dgrowleyml@gmail.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Postgres does not use indexes with OR-conditions
Date: Fri, 07 Nov 2014 08:55:32 -0500
Message-ID: <545CCF54.1010205@dunslane.net> (raw)
In-Reply-To: <545C5352.4050309@dc.baikal.ru>
References: <545C479B.7010104@dc.baikal.ru>
<CAApHDvr0X+eYy-bZJh-ch3Kr62BJkk=ZWACyndMKJsUBYWjQpg@mail.gmail.com>
<545C5352.4050309@dc.baikal.ru>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 11/07/2014 12:06 AM, Vlad Arkhipov wrote:
> 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
>
First, please do not top-post on the PostgreSQL lists. See
<http://idallen.com/topposting.html;
Second, the last test for fd.creation_time in your query seems
redundant. 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)
cheers
andrew
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andrew@dunslane.net, arhipov@dc.baikal.ru, dgrowleyml@gmail.com
Subject: Re: Postgres does not use indexes with OR-conditions
In-Reply-To: <545CCF54.1010205@dunslane.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox