Re: Constraint exclusion in views

On Sat, Nov 3, 2012 at 10:23 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Josh Berkus <josh(at)agliodbs(dot)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.