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

[Bob Ippolito]

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

> Bob,
>
>
>> Limit  (cost=1506.01..1508.51 rows=1000 width=8)
>>     ->  Sort  (cost=1506.01..1531.96 rows=10382 width=8)
>>           Sort Key: public.ping.id
>>           ->  Result  (cost=0.00..813.44 rows=10382 width=8)
>>                 One-Time Filter: false
>>
>                 ^^^^^^^^^^^^^^^^^^^
> Verdict from Simon: nothing below this line is being executed.  Try
> actually running the query -- you'll see it's very fast.   We should
> document this though so that people know what they're looking at.

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:

mochi=# explain 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)
    One-Time Filter: false
    ->  Append  (cost=0.00..7.84 rows=2 width=16)
          ->  Index Scan using ping_pkey on ping  (cost=0.00..4.83  
rows=1 width=16)
                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)
                Index Cond: (id = 8706410)
                Filter: ("timestamp" = '2005-07-19  
00:00:00-07'::timestamp with time zone)
(9 rows)

however if I take away either of those conditions, it breaks:

mochi=# explain select id, timestamp from ping where id = 8706410;
                                                 QUERY PLAN
------------------------------------------------------------------------ 
----------------------------------
Result  (cost=0.00..379.55 rows=125 width=16)
    One-Time Filter: false
    ->  Append  (cost=0.00..379.55 rows=125 width=16)
          ->  Index Scan using ping_pkey on ping  (cost=0.00..4.82  
rows=1 width=16)
                Index Cond: (id = 8706410)
          ->  Index Scan using ping_20050816_pkey on ping_20050816  
ping  (cost=0.00..3.01 rows=1 width=16)
                Index Cond: (id = 8706410)
          ->  Seq Scan on ping_20050512 ping  (cost=0.00..1.01 rows=1  
width=16)
                Filter: (id = 8706410)
          ->  Index Scan using ping_20050514_pkey on ping_20050514  
ping  (cost=0.00..3.01 rows=1 width=16)
                Index Cond: (id = 8706410)
         ......
(253 rows)

The check constraints are separate:

Check constraints:
     "ping_20050512_id_check" CHECK (id >= 1 AND id <= 1)
     "ping_20050512_timestamp_check" CHECK ("timestamp" >=  
'2005-05-12 20:26:12-07'::timestamp with time zone AND "timestamp" <=  
'2005-05-12 20:26:12-07'::timestamp with time zone)


The runtimes prove that the query plan is in fact insane:

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.052..0.062  
rows=1 loops=1)
    One-Time Filter: false
    ->  Append  (cost=0.00..7.84 rows=2 width=16) (actual  
time=0.045..0.051 rows=1 loops=1)
          ->  Index Scan using ping_pkey on ping  (cost=0.00..4.83  
rows=1 width=16) (actual time=0.016..0.016 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.024..0.027  
rows=1 loops=1)
                Index Cond: (id = 8706410)
                Filter: ("timestamp" = '2005-07-19  
00:00:00-07'::timestamp with time zone)
Total runtime: 0.243 ms
(10 rows)

mochi=# explain analyze select id, timestamp from ping where id =  
8706410;
                                                                         
QUERY PLAN
------------------------------------------------------------------------ 
------------------------------------------------------------------------ 
--------
Result  (cost=0.00..379.55 rows=125 width=16) (actual  
time=4682.538..4682.639 rows=1 loops=1)
    One-Time Filter: false
    ->  Append  (cost=0.00..379.55 rows=125 width=16) (actual  
time=4682.531..4682.628 rows=1 loops=1)
          ->  Index Scan using ping_pkey on ping  (cost=0.00..4.82  
rows=1 width=16) (actual time=0.019..0.019 rows=0 loops=1)
         ....
Total runtime: 4685.674 ms
(254 rows)


WTF? :)

-bob