pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres does not use indexes with OR-conditions
7+ messages / 7 participants
[nested] [flat]
* Postgres does not use indexes with OR-conditions
@ 2014-11-07 04:16 arhipov <arhipov@dc.baikal.ru>
0 siblings, 1 reply; 7+ messages in thread
From: arhipov @ 2014-11-07 04:16 UTC (permalink / raw)
To: pgsql-performance
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
"Limit (cost=4.30..44.50 rows=200 width=67) (actual time=0.032..1.376
rows=200 loops=1)"
" -> Index Scan Backward using financial_document_creation_time_index
on financial_documents fd (cost=4.30..292076.25 rows=1453075 width=67)
(actual time=0.027..0.683 rows=200 loops=1)"
" Index Cond: (creation_time <= '2011-11-07
11:39:07.285022+09'::timestamp with time zone)"
"Total runtime: 1.740 ms"
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
"Limit (cost=4.30..71.76 rows=200 width=67) (actual
time=1067.935..1069.126 rows=200 loops=1)"
" -> Index Scan Backward using financial_document_creation_time_index
on financial_documents fd (cost=4.30..490104.07 rows=1453075 width=67)
(actual time=1067.927..1068.532 rows=200 loops=1)"
" Filter: ((creation_time = '2011-11-07
11:39:07.285022+09'::timestamp with time zone) OR (creation_time <
'2011-11-07 11:39:07.285022+09'::timestamp with time zone))"
" Rows Removed by Filter: 776785"
"Total runtime: 1069.480 ms"
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres does not use indexes with OR-conditions
@ 2014-11-07 04:38 David Rowley <dgrowleyml@gmail.com>
parent: arhipov <arhipov@dc.baikal.ru>
0 siblings, 1 reply; 7+ messages in thread
From: David Rowley @ 2014-11-07 04:38 UTC (permalink / raw)
To: arhipov <arhipov@dc.baikal.ru>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres does not use indexes with OR-conditions
@ 2014-11-07 05:06 Vlad Arkhipov <arhipov@dc.baikal.ru>
parent: David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Vlad Arkhipov @ 2014-11-07 05:06 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: pgsql-performance
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
> <mailto: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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres does not use indexes with OR-conditions
@ 2014-11-07 13:55 Andrew Dunstan <andrew@dunslane.net>
parent: Vlad Arkhipov <arhipov@dc.baikal.ru>
0 siblings, 1 reply; 7+ messages in thread
From: Andrew Dunstan @ 2014-11-07 13:55 UTC (permalink / raw)
To: Vlad Arkhipov <arhipov@dc.baikal.ru>; David Rowley <dgrowleyml@gmail.com>; +Cc: 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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres does not use indexes with OR-conditions
@ 2014-11-07 14:17 Kevin Grittner <kgrittn@ymail.com>
parent: Andrew Dunstan <andrew@dunslane.net>
0 siblings, 2 replies; 7+ messages in thread
From: Kevin Grittner @ 2014-11-07 14:17 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; Vlad Arkhipov <arhipov@dc.baikal.ru>; David Rowley <dgrowleyml@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres does not use indexes with OR-conditions
@ 2014-11-07 15:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kevin Grittner <kgrittn@ymail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2014-11-07 15:11 UTC (permalink / raw)
To: Kevin Grittner <kgrittn@ymail.com>; +Cc: Andrew Dunstan <andrew@dunslane.net>; Vlad Arkhipov <arhipov@dc.baikal.ru>; David Rowley <dgrowleyml@gmail.com>; pgsql-performance
Kevin Grittner <kgrittn@ymail.com> writes:
> 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
That's the way to do it, not only because it's simpler and clearer,
but because the planner will recognize the relevance of the
condition to an index on creation_time, financial_document_id ...
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres does not use indexes with OR-conditions
@ 2014-11-07 16:50 David G Johnston <david.g.johnston@gmail.com>
parent: Kevin Grittner <kgrittn@ymail.com>
1 sibling, 0 replies; 7+ messages in thread
From: David G Johnston @ 2014-11-07 16:50 UTC (permalink / raw)
To: pgsql-performance
Kevin Grittner-5 wrote
> Andrew Dunstan <
> andrew@
> > 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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2014-11-07 16:50 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-11-07 04:16 Postgres does not use indexes with OR-conditions arhipov <arhipov@dc.baikal.ru>
2014-11-07 04:38 ` David Rowley <dgrowleyml@gmail.com>
2014-11-07 05:06 ` Vlad Arkhipov <arhipov@dc.baikal.ru>
2014-11-07 13:55 ` Andrew Dunstan <andrew@dunslane.net>
2014-11-07 14:17 ` Kevin Grittner <kgrittn@ymail.com>
2014-11-07 15:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-11-07 16:50 ` David G Johnston <david.g.johnston@gmail.com>
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