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

[Bob Ippolito]

On Sep 13, 2005, at 11:38 AM, Josh Berkus wrote:

> Bob,
>
>
>> 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';
>>
>
> You're not going to get constraint exclusion for this query because  
> the
> WHERE conditions cover the whole data population.   That is, since the
> WHERE conditions apply to all partitions, the planner is going to  
> append
> all partitions.
>
> Now, *you* happen to know that the last 1000 ids is only going to  
> match a
> couple of partitions.   However, there's no way for the *planner*  
> to know
> that.   The top 15 partitions could be empty, for example.  Since
> Constraint Exclusion is determinative, it can't use statistics (like
> pg_stats) which are probabilistic.

Ok, but if I rewrite it to be id > 0 and id < 10000, it still scans  
every table

EXPLAIN SELECT id FROM ping WHERE id between 0 and 10000 order by id  
limit 1000;

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
                ->  Append  (cost=0.00..813.44 rows=10382 width=8)
                      ->  Index Scan using ping_pkey on ping   
(cost=0.00..8.30 ro
ws=2 width=8)
                            Index Cond: ((id >= 0) AND (id <= 10000))
                      ->  Index Scan using ping_20050816_pkey on  
ping_20050816 pi
ng  (cost=0.00..3.02 rows=1 width=8)
                            Index Cond: ((id >= 0) AND (id <= 10000))
                      ->  Seq Scan on ping_20050512 ping   
(cost=0.00..1.01 rows=1
width=8)
                            Filter: ((id >= 0) AND (id <= 10000))
                      ->  Index Scan using ping_20050514_pkey on  
ping_20050514 pi
ng  (cost=0.00..3.02 rows=1 width=8)
                            Index Cond: ((id >= 0) AND (id <= 10000))
.... the rest of them are listed


clearly that makes no sense, because ping_20050816 has these check  
constraints:
     "ping_20050816_id_check" CHECK (id >= 20457247 AND id <= 21092646)
     "ping_20050816_timestamp_check" CHECK ("timestamp" >=  
'2005-08-16 00:00:00-0
7'::timestamp with time zone AND "timestamp" <= '2005-08-16  
23:59:59-07'::timest
amp with time zone)

ping_20050514 shouldn't even be scanned:
     "ping_20050514_id_check" CHECK (id >= 23320 AND id <= 1264112)
     "ping_20050514_timestamp_check" CHECK ("timestamp" >=  
'2005-05-14 00:00:01-0
7'::timestamp with time zone AND "timestamp" <= '2005-05-14  
23:59:59-07'::timest
amp with time zone)

In the EXPLAIN ANALYZE, it still lists all of the tables.

-bob