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

Re: [PERFORM] Constraint exclusion on UNION ALL subqueries with WHERE conditions

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)postgreSQL(dot)org
Cc: pgsql-performance(at)postgreSQL(dot)org
Subject: Re: [PERFORM] Constraint exclusion on UNION ALL subqueries with WHERE conditions
Date: 2011-09-21 17:11:00
Message-ID: 25530.1316625060@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
=?ISO-8859-1?Q?Gunnlaugur_=DE=F3r_Briem?= <gunnlaugur(at)gmail(dot)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 constraints only 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

In response to

Responses

pgsql-performance by date

Next:From: Greg SmithDate: 2011-09-21 17:57:51
Subject: Re: : Performance Improvement Strategy
Previous:From: Royce AusburnDate: 2011-09-21 17:08:04
Subject: Re: Prepared statements and suboptimal plans

pgsql-hackers by date

Next:From: Robert HaasDate: 2011-09-21 17:13:29
Subject: Re: unite recovery.conf and postgresql.conf
Previous:From: Josh BerkusDate: 2011-09-21 17:08:22
Subject: Re: unite recovery.conf and postgresql.conf

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