From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-performance@postgresql.org
Subject: Re: Constraint exclusion in views
Date: Sat, 03 Nov 2012 17:23:47 -0400
Message-ID: <29144.1351977827@sss.pgh.pa.us> (raw)
In-Reply-To: <50957270.80300@agliodbs.com>
References: <CAGTBQpZzLsR1xij+7tgbOsi3M3Hga9Y=Ta+K6CzTZb-CLTjy4w@mail.gmail.com>
<50957270.80300@agliodbs.com>
Josh Berkus <josh@agliodbs.com> writes:
>> Funny thing is, if I set constraint_exclusion=on, it works as
>> expected. But not with constraint_exclusion=partition.
> The difference between "on" and "partition" is how it treats UNION.
> This seems to be working as designed.
Well, what "partition" actually means is "only bother to try constraint
exclusion proofs on appendrel members". UNION ALL trees will get
flattened into appendrels in some cases. In a quick look at the code,
it seems like in recent releases the restrictions are basically that the
UNION ALL arms have to (1) each be a plain SELECT from a single table
with no WHERE restriction; (2) all produce the same column datatypes;
and (3) not have any volatile functions in the SELECT lists. I might be
missing something relevant to the OP's case, but it's hard to tell
without a concrete example.
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
Subject: Re: Constraint exclusion in views
In-Reply-To: <29144.1351977827@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