Skip site navigation (1) Skip section navigation (2)

Re: Poor query plan across OR operator

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Mark Hills <Mark(dot)Hills(at)framestore(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Poor query plan across OR operator
Date: 2010-01-26 19:35:07
Message-ID: 603c8f071001261135k7d240c80wf4acc8a7b63f17b2@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Tue, Jan 26, 2010 at 11:41 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Mark Hills <Mark(dot)Hills(at)framestore(dot)com> writes:
>> One of our most-used queries performs poorly (taking over 2 seconds) and a
>> tiny amount of refactoring shows it can be fast (less than 1ms) by
>> transforming the OR case (which spans two tables) into a UNION.
>
> I'd suggest going with the UNION.  We are unlikely to make the planner
> look for such cases, because usually such a transformation would be a
> net loss.  It seems like rather a corner case that it's a win even on
> your example.

This has come up for me, too.  But even if we grant that it's
worthwhile, it seems like a tricky optimization to apply in practice,
because unless your row estimates are very accurate, you might easily
apply it when you would have been better off leaving it alone.  And it
seems like getting accurate estimates would be hard, since the
conditions might be highly correlated, or not, and they're on
different tables.

...Robert

In response to

Responses

pgsql-performance by date

Next:From: Greg SmithDate: 2010-01-26 20:32:34
Subject: Re: splitting data into multiple tables
Previous:From: Viji V NairDate: 2010-01-26 18:23:25
Subject: Re: splitting data into multiple tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group