Constraint exclusion in views

From: Claudio Freire <klaussfreire(at)gmail(dot)com>
To: postgres performance list <pgsql-performance(at)postgresql(dot)org>
Subject: Constraint exclusion in views
Date: 2012-11-02 18:17:10
Message-ID: CAGTBQpZzLsR1xij+7tgbOsi3M3Hga9Y=Ta+K6CzTZb-CLTjy4w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi list.

I've been battling with a design issue here.

I have postgres 9.0.x deployed in some databases, and was designing
some changes that involve querying in a very partition-like way, but
not quite.

In particular, I have a few tables (lets call them table1...tableN). N
is pretty small here, but it might grow over time. It's not date-based
partitioning or anything like that, it's more like kinds of rows.
Think multiple-table inheritance.

Now, I have a view, call it all_tables, that "normalizes" the schema
(picks common rows, does some expression magic to translate one form
of some data point into another, etc), and union alls them all.

SELECT t1.id, t1.x, t1.y, t1.z FROM table1
UNION ALL
SELECT t2.id, t2.x, t2.y, 0::integer as z FROM table2
... etc

Ids are unique among all tables, a-la partitioning, so I have set up
check constraints on each table, and it works perfectly for one case
where table1..n are equal structure.

But for another case where they differ (like the case I pointed to
above), the planner ignores constraint exclusion, because it seems to
add a "subquery" node before the append:

"Append (cost=0.00..16.93 rows=2 width=136)"
" -> Subquery Scan on "*SELECT* 1" (cost=0.00..8.61 rows=1 width=179)"
" -> Index Scan using table1_pkey on table1 (cost=0.00..8.60
rows=1 width=179)"
" Index Cond: (id = (-3))"
" -> Subquery Scan on "*SELECT* 2" (cost=0.00..8.32 rows=1 width=93)"
" -> Index Scan using table2_pkey on table2 (cost=0.00..8.31
rows=1 width=93)"
" Index Cond: (id = (-3))"

Funny thing is, if I set constraint_exclusion=on, it works as
expected. But not with constraint_exclusion=partition.

Is there a workaround for this, other than micromanaging
constraint_exclusion from the application side? (I wouldn't want to
set it to on globally)

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Gunnar "Nick" Bluth 2012-11-03 10:31:28 Re: Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
Previous Message Petr Praus 2012-11-02 16:12:22 Re: Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries