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

[Bob Ippolito]

On Sep 13, 2005, at 6:00 PM, Josh Berkus wrote:

> 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?

If you make the a column bigserial (or bigint), and it'll be busted.   
Surprisingly, numeric seems to work.

Limit  (cost=95.55..95.56 rows=5 width=40) (actual time=0.112..0.116  
rows=1 loops=1)
    ->  Sort  (cost=95.55..95.61 rows=24 width=40) (actual  
time=0.107..0.109 rows=1 loops=1)
          Sort Key: public.a.a
          ->  Result  (cost=0.00..95.00 rows=24 width=40) (actual  
time=0.069..0.094 rows=1 loops=1)
                ->  Append  (cost=0.00..95.00 rows=24 width=40)  
(actual time=0.065..0.087 rows=1 loops=1)
                      ->  Seq Scan on a  (cost=0.00..23.75 rows=6  
width=40) (actual time=0.002..0.002 rows=0 loops=1)
                            Filter: (a = 1)
                      ->  Seq Scan on a1 a  (cost=0.00..23.75 rows=6  
width=40) (actual time=0.058..0.061 rows=1 loops=1)
                            Filter: (a = 1)
                      ->  Seq Scan on a2 a  (cost=0.00..23.75 rows=6  
width=40) (actual time=0.006..0.006 rows=0 loops=1)
                            Filter: (a = 1)
                      ->  Seq Scan on a3 a  (cost=0.00..23.75 rows=6  
width=40) (actual time=0.005..0.005 rows=0 loops=1)
                            Filter: (a = 1)
Total runtime: 0.200 ms
(14 rows)


  PostgreSQL 8.1beta1 on i386-unknown-freebsd6.0, compiled by GCC gcc  
(GCC) 3.4.4 [FreeBSD] 20050518

-bob