On Thu, 2010-10-28 at 12:25 -0700, Ben wrote:
> i think we are talking about two different things here: the constraints on
> the table, and the where-clause constraints in a query which may or may not
> trigger constraint exclusion. i understand that table constraints have to be
> constants -- it doesn't make much sense otherwise. what i am wondering about
> is, will constraint exclusion be triggered for queries where the column that
> is being partitioned on is being constrained things that are not static
> constants, for instance, in a join. (i'm pretty sure the answer is no,
> because i think constraint exclusion happens before real query planning.) a
> concrete example :
>
> create table foo (i integer not null, j float not null);
> create table foo_1 (check ( i >= 0 and i < 10) ) inherits (foo);
> create table foo_2 (check ( i >= 10 and i < 20) ) inherits (foo);
> create table foo_3 (check ( i >= 20 and i < 30) ) inherits (foo);
> etc..
>
> create table bar (i integer not null, k float not null);
>
> my understanding is that a query like
>
> select * from foo, bar using (i);
>
> can't use constraint exclusion, even if the histogram of i-values on table
> bar says they only live in the range 0-9, and so the query will touch all of
> the tables. i think this is not favorable compared to a single foo table
> with a well-maintained btree index on i.
>
My tests show you are incorrect:
part_test=# explain analyze select * from foo join bar using (i) where
i=9;
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=34.26..106.76 rows=200 width=20) (actual
time=0.004..0.004 rows=0 loops=1)
-> Append (cost=0.00..68.50 rows=20 width=12) (actual
time=0.004..0.004 rows=0 loops=1)
-> Seq Scan on foo (cost=0.00..34.25 rows=10 width=12)
(actual time=0.001..0.001 rows=0 loops=1)
Filter: (i = 9)
-> Seq Scan on foo_1 foo (cost=0.00..34.25 rows=10 width=12)
(actual time=0.000..0.000 rows=0 loops=1)
Filter: (i = 9)
-> Materialize (cost=34.26..34.36 rows=10 width=12) (never
executed)
-> Seq Scan on bar (cost=0.00..34.25 rows=10 width=12) (never
executed)
Filter: (i = 9)
Total runtime: 0.032 ms
(10 rows)
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
