From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-performance@postgresql.org
Subject: Re: Constraint exclusion in views
In-reply-to: <50957270.80300@agliodbs.com>
References: 
 <CAGTBQpZzLsR1xij+7tgbOsi3M3Hga9Y=Ta+K6CzTZb-CLTjy4w@mail.gmail.com>
	<50957270.80300@agliodbs.com>
Comments: In-reply-to Josh Berkus <josh@agliodbs.com>
	message dated "Sat, 03 Nov 2012 12:37:20 -0700"
Date: Sat, 03 Nov 2012 17:23:47 -0400
Message-ID: <29144.1351977827@sss.pgh.pa.us>

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