Re: Constraint exclusion in views

On Sat, Nov 3, 2012 at 10:23 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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.

I would think our view succeeds all those tests, but I'm not entirely
sure about 2. It does use coalesce too, but I really doubt coalesce is
volatile... right?

I don't have access to the code during the weekend, but I'll check
first thing tomorrow whether we have some datatype inconsistencies I
didn't notice.

Thanks for the hint.