Обсуждение: Constraint exclusion on UNION ALL subqueries with WHERE conditions

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
ofthe 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
thisa case of “sounds nice, but too rare to merit implementing”? 

Thanks,

- Gulli

=?ISO-8859-1?Q?Gunnlaugur_=DE=F3r_Briem?= <gunnlaugur@gmail.com> writes:
> 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
ofthe subqueries, for a query like: 

> SELECT * FROM unionview WHERE col=2;

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

Works for me in 8.4.8.  Do you have constraint_exclusion set to ON?

            regards, tom lane

On Monday, September 19, 2011 3:59:30 AM UTC, Tom Lane wrote:
> Works for me in 8.4.8.  Do you have constraint_exclusion set to ON?

I did try with constraint_exclusion set to on, though the docs suggest partition should be enough ("examine constraints
onlyfor ... UNION ALL subqueries") 

Here's a minimal test case (which I should have supplied in the original post, sorry), tried just now in 8.4.8:

CREATE OR REPLACE VIEW v_heavy_view
AS SELECT (random()*1e5)::integer col
FROM generate_series(1, 1e6::integer);

CREATE OR REPLACE VIEW v_test_constraint_exclusion AS
SELECT col FROM v_heavy_view WHERE col < 3
UNION ALL SELECT col FROM v_heavy_view WHERE col >= 3;

EXPLAIN SELECT * FROM v_test_constraint_exclusion WHERE col=2;

                                        QUERY PLAN
--------------------------------------------------------------------------
 Result  (cost=0.00..70.04 rows=4 width=4)
   ->  Append  (cost=0.00..70.04 rows=4 width=4)
         ->  Subquery Scan v_heavy_view  (cost=0.00..35.00 rows=2 width=4)
               Filter: ((v_heavy_view.col < 3) AND (v_heavy_view.col = 2))
               ->  Function Scan on generate_series  (cost=0.00..20.00 rows=1000 width=0)
         ->  Subquery Scan v_heavy_view  (cost=0.00..35.00 rows=2 width=4)
               Filter: ((v_heavy_view.col >= 3) AND (v_heavy_view.col = 2))
               ->  Function Scan on generate_series  (cost=0.00..20.00 rows=1000 width=0)

I want the planner to notice that (v_heavy_view.col >= 3) AND (v_heavy_view.col = 2) can never be satisfied, and skip
thatsubquery. 

Regards,

- Gulli

=?ISO-8859-1?Q?Gunnlaugur_=DE=F3r_Briem?= <gunnlaugur@gmail.com> writes:
> On Monday, September 19, 2011 3:59:30 AM UTC, Tom Lane wrote:
>> Works for me in 8.4.8.  Do you have constraint_exclusion set to ON?

> I did try with constraint_exclusion set to on, though the docs suggest partition should be enough ("examine
constraintsonly for ... UNION ALL subqueries") 

> Here's a minimal test case (which I should have supplied in the original post, sorry), tried just now in 8.4.8:

> CREATE OR REPLACE VIEW v_heavy_view
> AS SELECT (random()*1e5)::integer col
> FROM generate_series(1, 1e6::integer);

> CREATE OR REPLACE VIEW v_test_constraint_exclusion AS
> SELECT col FROM v_heavy_view WHERE col < 3
> UNION ALL SELECT col FROM v_heavy_view WHERE col >= 3;

> EXPLAIN SELECT * FROM v_test_constraint_exclusion WHERE col=2;

Hmm.  The reason this particular case doesn't work is that we don't
apply relation_excluded_by_constraints() to functions-in-FROM.
It's only used for plain-table RTEs, not subqueries, functions,
etc.  I suspect the complainant's real case involved an unflattenable
subquery.

Probably the rationale for that coding was that only plain tables
could have CHECK constraints; but the portion of the logic that looks
for mutually contradictory scan constraints could apply to non-table
relations.

Should we change the code to make such checks in these cases?
The default behavior (with constraint_exclusion = partition) would
still be to do nothing extra, but it would add planning expense when
constraint_exclusion = on.

            regards, tom lane

Right, the view that prompted this involved subqueries; the function was just an artificial test case.

That change seems like a good one for sure.

Ideally I'd like to enable it for a particular view rather than incur the planning expense for the whole DB (something
likeALTER VIEW foo WITH CONSTRAINT EXCLUSION), but I guess there's no support currently (and not easily added) for such
per-objectplanner settings? The application can just issue SET constraint_exclusion=on; as needed; for my case that's
fine,but for DBAs maybe a bit limiting. 

Regards,

- Gulli