Subject: Re: partitioning question 1
From: "Joshua D. Drake" <jd@commandprompt.com>
Reply-To: jd@commandprompt.com
To: Ben <midfield@gmail.com>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <4486C61F-DC08-4DE4-8AF9-245FA60B0310@gmail.com>
References: <A9A748D8-A52F-4C16-A303-3A4AA1EC1BD2@gmail.com>
	<1288287092.22359.23.camel@jd-desktop>
	<E03148FE-26C6-47BA-9463-B97749BE3E8C@gmail.com>
	<1288291814.22359.31.camel@jd-desktop>
	<4486C61F-DC08-4DE4-8AF9-245FA60B0310@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Organization: Command Prompt, Inc.
Date: Thu, 28 Oct 2010 12:44:12 -0700
Message-ID: <1288295052.22359.35.camel@jd-desktop>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

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