From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-hackers@postgreSQL.org
Cc: pgsql-performance@postgreSQL.org
Subject: Re: [PERFORM] Constraint exclusion on UNION ALL subqueries with WHERE conditions
Date: Wed, 21 Sep 2011 13:11:00 -0400
Message-ID: <25530.1316625060@sss.pgh.pa.us> (raw)
In-Reply-To: <12971543.2235.1316510130842.JavaMail.geo-discussion-forums@yqcd38>
References: <bd6669a0-5da6-410c-b3a2-4a1c01777003@glegroupsg2000goo.googlegroups.com>
<25440.1316404770@sss.pgh.pa.us>
<12971543.2235.1316510130842.JavaMail.geo-discussion-forums@yqcd38>
=?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 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, pgsql-hackers@postgreSQL.org
Subject: Re: [PERFORM] Constraint exclusion on UNION ALL subqueries with WHERE conditions
In-Reply-To: <25530.1316625060@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox