[Bizgres-general] constraint exclusion (table partitioning) not working?

[Josh Berkus]

Bob,

> 8.1 CVS HEAD, as of yesterday, has the same behavior.  The query that
> satisfies all constraints is very fast, but the query does not is not.
>
> mochi=# explain analyze select id, timestamp from ping where id =
> 8706410 and timestamp = '2005-07-19 00:00:00-07';

Hmmm ... this is very odd.   I just built a simple test case and it works 
fine:

create table a ( a int, b text );
create table a1 () inherits (a);
create table a2 () inherits (a);
create table a3 () inherits (a);
 a1 add constraint a1_a check ( a between 1 and 3);
 a1 add constraint a1_b check ( b between 'A' and 'C' );
 a2 add constraint a2_a check ( a between 4 and 6);
 a2 add constraint a2_b check ( b between 'D' and 'F' );
 a3 add constraint a3_a check ( a between 7 and 9);
 a3 add constraint a3_b check ( b between 'G' and 'I' );
insert into a1 values ( 1, 'B' );
insert into a2 values ( 5, 'F' );
insert into a3 values ( 8, 'G' );

postgres=# explain analyze select * from a where a = 1 order by a limit 5;
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=49.22..49.23 rows=5 width=36) (actual time=0.210..0.238 
rows=1 loops=1)
   ->  Sort  (cost=49.22..49.25 rows=12 width=36) (actual time=0.169..0.173 
rows=1 loops=1)
         Sort Key: public.a.a
         ->  Result  (cost=0.00..49.00 rows=12 width=36) (actual 
time=0.072..0.110 rows=1 loops=1)
               ->  Append  (cost=0.00..49.00 rows=12 width=36) (actual 
time=0.064..0.094 rows=1 loops=1)
                     ->  Seq Scan on a  (cost=0.00..24.50 rows=6 width=36) 
(actual time=0.005..0.005 rows=0 loops=1)
                           Filter: (a = 1)
                     ->  Seq Scan on a1 a  (cost=0.00..24.50 rows=6 
width=36) (actual time=0.031..0.054 rows=1 loops=1)
                           Filter: (a = 1)
 Total runtime: 0.443 ms
(10 rows)

select version();
                                                   version
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.1beta1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 
20050117 (prerelease) (SUSE Linux)

... so it's working fine for me.   Can you think of anything that might 
make your case special?

-- 
Josh Berkus                GreenPlum Inc
Community Liason       www.greenplum.com
415-752-2500       jberkus at greenplum.com