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

Re: Constraint exclusion in views

From: Claudio Freire <klaussfreire(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: postgres performance list <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Constraint exclusion in views
Date: 2012-11-05 20:28:31
Message-ID: CAGTBQpYf3EktbSBq6OPvuk0EdZQM7ZXz3Zz+CgYcnOrxJpkU=g@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Sun, Nov 4, 2012 at 2:32 PM, Claudio Freire <klaussfreire(at)gmail(dot)com> wrote:
>> 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.

It was indeed a type mismatch, there was an int in one subquery that
was a bigint in all the others.
Thanks a lot.


In response to

pgsql-performance by date

Next:From: Merlin MoncureDate: 2012-11-05 22:51:44
Subject: Re: How to keep queries low latency as concurrency increases
Previous:From: Claudio FreireDate: 2012-11-05 17:59:12
Subject: Re: Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries

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