Re: planner missing a trick for foreign tables w/OR conditions

On Mon, Dec 16, 2013 at 12:41:43PM -0500, Robert Haas wrote:
> Consider a query such as:
> 
> SELECT * FROM a, b WHERE (a.x = 42 AND b.y = 43) OR (a.x = 44 AND b.z = 45);
> 
> If a and/or b are regular tables, the query planner will cleverly
> consider the possibility of using an index on a to filter for rows
> with a.x = 42 OR a.x = 44, or of using an index on b to filter for
> rows where b.y = 43 OR b.z = 45.  But if they are foreign tables, this
> optimization isn't considered, because we don't intrinsically know
> anything about what indexes are present on the foreign side.  However,
> this optimization could potentially be quite valuable.  In fact, it's
> arguably more useful here for regular tables, because even if no index
> is present on the foreign side, applying the condition on the remote
> side might eliminate enough data transfer overhead to win.  The only
> situation in which I can really see it losing is if the simplified
> qual ends up eliminating too few rows to cover the remote side's
> processing costs; I'm not sure how possible that is, or how to know
> whether it might be the case.
> 
> To see how this can torpedo performance, run the attached SQL file on
> an empty database, and then run these quereis:

+1 for fixing this bug :)

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate