Skip site navigation (1) Skip section navigation (2)

Re: Constraint exclusion in views

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Constraint exclusion in views
Date: 2012-11-03 21:23:47
Message-ID: 29144.1351977827@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
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.

			regards, tom lane


In response to

Responses

pgsql-performance by date

Next:From: Jeff JanesDate: 2012-11-03 23:53:38
Subject: Re: How to keep queries low latency as concurrency increases
Previous:From: Josh BerkusDate: 2012-11-03 19:37:20
Subject: Re: Constraint exclusion in views

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group