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

[Bob Ippolito]

I'm getting a totally bogus query plan with bizgres 0.7.4 for a  
partitioned table:

EXPLAIN SELECT
         "id",
         date_trunc('hour', "timestamp") AS "timestamp",
         "mochiTag",
         "movieURL",
         "movieURLHost"
     FROM "ping" WHERE
         "id" > '0' AND "timestamp" >= '1969-12-31 16:00:00-08'
     ORDER BY "id" LIMIT '1000';


  Limit  (cost=10224769.58..10224772.08 rows=1000 width=36)
    ->  Sort  (cost=10224769.58..10328540.22 rows=41508256 width=36)
          Sort Key: public.ping.id
          ->  Result  (cost=1.43..1772652.20 rows=41508256 width=36)
                One-Time Filter: false
                ->  Append  (cost=1.43..1668881.56 rows=41508256  
width=36)
                      ->  Bitmap Heap Scan on ping  (cost=1.43..13.29  
rows=42 width=36)
                            Recheck Cond: (id > 0::bigint)
                            Filter: ("timestamp" >= '1969-12-31  
16:00:00-08'::timestamp with time zone)
                            ->  Bitmap Index Scan on ping_pkey   
(cost=0.00..1.43 rows=124 width=0)
                                  Index Cond: (id > 0::bigint)
                      ->  Seq Scan on ping_20050816 ping   
(cost=0.00..25823.00 rows=635274 width=36)
                            Filter: ((id > 0::bigint) AND  
("timestamp" >= '1969-12-31 16:00:00-08'::timestamp with time zone))
                      ->  Seq Scan on ping_20050512 ping   
(cost=0.00..1.01 rows=1 width=36)
                            Filter: ((id > 0::bigint) AND  
("timestamp" >= '1969-12-31 16:00:00-08'::timestamp with time zone))
                      ->  Seq Scan on ping_20050514 ping   
(cost=0.00..48123.90 rows=1240547 width=36)
                            Filter: ((id > 0::bigint) AND  
("timestamp" >= '1969-12-31 16:00:00-08'::timestamp with time zone))
                      ->  Seq Scan on ping_20050726 ping   
(cost=0.00..30969.24 rows=795591 width=36)
                            Filter: ((id > 0::bigint) AND  
("timestamp" >= '1969-12-31 16:00:00-08'::timestamp with time zone))
                      ->  Seq Scan on ping_20050727 ping   
(cost=0.00..8317.99 rows=204959 width=36)
                            Filter: ((id > 0::bigint) AND  
("timestamp" >= '1969-12-31 16:00:00-08'::timestamp with time zone))
                      ->  Seq Scan on ping_20050513 ping   
(cost=0.00..904.77 rows=23314 width=36)
                            Filter: ((id > 0::bigint) AND  
("timestamp" >= '1969-12-31 16:00:00-08'::timestamp with time zone))
                      ->  Seq Scan on ping_20050515 ping   
(cost=0.00..45802.06 rows=1180970 width=36)
                            Filter: ((id > 0::bigint) AND  
("timestamp" >= '1969-12-31 16:00:00-08'::timestamp with time zone))
..... there's a lot more of these tables with dumb query plans

They all look like this:

                                        Table "public.ping_20050816"
          Column         |           Type            
|                      Modifiers
------------------------+-------------------------- 
+------------------------------------------------------
id                     | bigint                   | not null default  
nextval('public.ping_id_seq'::text)
timestamp              | timestamp with time zone | not null

..... with more columns that are irrelevant

Indexes:
     "ping_20050816_pkey" PRIMARY KEY, btree (id)
     "ping_20050816_mochiTag_movieURLHost_timestamp" btree  
("mochiTag", "movieURLHost", "timestamp")
     "ping_20050816_mochiTag_movieURL_timestamp" btree ("mochiTag",  
"movieURL", "timestamp")
     "ping_20050816_mochiTag_timestamp" btree ("mochiTag", "timestamp")
Check constraints:
     "ping_20050816_id_check" CHECK (id >= 20457247 AND id <= 21092646)
     "ping_20050816_timestamp_check" CHECK ("timestamp" >=  
'2005-08-16 00:00:00-07'::timestamp with time zone AND "timestamp" <=  
'2005-08-16 23:59:59-07'::timestamp with time zone)
Inherits: ping

I would expect that in this case, it should:

1. Use indexes
2. Hopefully know that the tables are strictly ordered on id because  
there are no overlaps on check constraints. it should only have to  
look in the two first ping tables to find 1000 rows (the first table  
is degenerate and only has one row, the rest generally have at least  
tens of thousands, some over a million).

I have checked that enable_constraint_exclusion is on (it seems to be  
on by default) and everything should be fully vacuumed and analyzed.

The query plan for scanning the empty parent table (ping) is closer  
to what I would expect them all to look like (I don't understand why  
it wants a bitmap for one column though).

I'm currently doing a pg_restore into CVS HEAD of 8.1, to see what it  
does here (hopefully something else!).

-bob