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

[Bob Ippolito]

On Sep 13, 2005, at 5:30 PM, Josh Berkus wrote:

> Bob,
>
>
>> I don't think that's true, there's no tables referenced above there
>> -- besides, I can prove that the runtime is abysmal for a very very   
>> selective query supported by the constraints and the primary key
>> index (below).  public.ping is empty, because it's the parent table.
>> If I do really dumb queries that satisfy every check constraint, it
>> seems to use constraint exclusion:
>>
>
> Do you have this loaded into PostgreSQL 8.1 yet?   I'd like to see  
> the same
> query on 8.1.   If it works there, we'll know exactly what we need  
> to fix.
>
> Otherwise, we'll check the bizgres patch code later this week.

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';
                                                                       
QUERY PLAN
------------------------------------------------------------------------ 
------------------------------------------------------------------------ 
----
Result  (cost=0.00..7.84 rows=2 width=16) (actual time=0.040..0.050  
rows=1 loops=1)
    ->  Append  (cost=0.00..7.84 rows=2 width=16) (actual  
time=0.037..0.043 rows=1 loops=1)
          ->  Index Scan using ping_pkey on ping  (cost=0.00..4.83  
rows=1 width=16) (actual time=0.013..0.013 rows=0 loops=1)
                Index Cond: (id = 8706410)
                Filter: ("timestamp" = '2005-07-19  
00:00:00-07'::timestamp with time zone)
          ->  Index Scan using ping_20050719_pkey on ping_20050719  
ping  (cost=0.00..3.02 rows=1 width=16) (actual time=0.019..0.021  
rows=1 loops=1)
                Index Cond: (id = 8706410)
                Filter: ("timestamp" = '2005-07-19  
00:00:00-07'::timestamp with time zone)
Total runtime: 0.184 ms
(9 rows)

mochi=# explain analyze select id, timestamp from ping where id =  
8706410;                                                                 
       QUERY PLAN

------------------------------------------------------------------------ 
--------
----------------------------------------------------------------------
Result  (cost=0.00..376.54 rows=125 width=16) (actual  
time=950.392..952.220 row
s=1 loops=1)
    ->  Append  (cost=0.00..376.54 rows=125 width=16) (actual  
time=950.387..952.2
12 rows=1 loops=1)
          ->  Index Scan using ping_pkey on ping  (cost=0.00..4.82  
rows=1 width=1
6) (actual time=0.016..0.016 rows=0 loops=1)
                Index Cond: (id = 8706410)
....
Total runtime: 955.222 ms
(253 rows)


-bob