From: | Andrew Dunstan <andrew(at)dunslane(dot)net> |
---|---|
To: | Vlad Arkhipov <arhipov(at)dc(dot)baikal(dot)ru>, David Rowley <dgrowleyml(at)gmail(dot)com> |
Cc: | pgsql-performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Postgres does not use indexes with OR-conditions |
Date: | 2014-11-07 13:55:32 |
Message-ID: | 545CCF54.1010205@dunslane.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-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
From | Date | Subject | |
---|---|---|---|
Next Message | Shaun Thomas | 2014-11-07 14:13:20 | Re: pgtune + configurations with 9.3 |
Previous Message | Artūras Lapinskas | 2014-11-07 11:14:51 | Re: Index order ignored after `is null` in query |