Constraint exclusion on UNION ALL subqueries with WHERE conditions

From: Gunnlaugur Þór Briem <gunnlaugur(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Constraint exclusion on UNION ALL subqueries with WHERE conditions
Date: 2011-09-14 17:53:22
Message-ID: bd6669a0-5da6-410c-b3a2-4a1c01777003@glegroupsg2000goo.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-performance

Hi,

I did this:

CREATE VIEW unionview AS
SELECT col, otherstuff FROM (heavy subquery)
WHERE col BETWEEN 1 AND 3
UNION ALL
SELECT col, otherstuff FROM (another heavy subquery)
WHERE col BETWEEN 4 AND 6;

hoping that the planner could use the WHERE conditions (like it would use check constraints on tables) to exclude one of the subqueries, for a query like:

SELECT * FROM unionview WHERE col=2;

But it doesn't. (In PostgreSQL 8.4.5, at least.)

Is there a way (currently) to get the planner to use these conditions to exclude subqueries in the UNION ALL? Or is this a case of “sounds nice, but too rare to merit implementing”?

Thanks,

- Gulli

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Josh Berkus 2011-09-14 20:15:21 Re: What Would You Like To Do?
Previous Message Jaime Casanova 2011-09-14 17:01:57 Re: What Would You Like To Do?

Browse pgsql-performance by date

  From Date Subject
Next Message Merlin Moncure 2011-09-14 18:18:49 Re: raid array seek performance
Previous Message Tomas Vondra 2011-09-14 16:05:23 Re: cannot use multicolumn index