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)

Search Discussions

  • Josh Berkus at Nov 3, 2012 at 7:37 pm

    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.

    --
    Josh Berkus
    PostgreSQL Experts Inc.
    http://pgexperts.com
  • Tom Lane at Nov 3, 2012 at 9:23 pm

    Josh Berkus 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
  • Claudio Freire at Nov 4, 2012 at 5:33 pm

    On Sat, Nov 3, 2012 at 10:23 PM, Tom Lane 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.
  • Claudio Freire at Nov 5, 2012 at 8:28 pm

    On Sun, Nov 4, 2012 at 2:32 PM, Claudio Freire 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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedNov 2, '12 at 6:17p
activeNov 5, '12 at 8:28p
posts5
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase