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

[Josh Berkus]

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.

As for index use, I agree that it would be keen to have the executor able 
to append indexes instead of seq scans across partitions.   But nobody has 
yet written the code for that feature, either in Bizgres or in PostgreSQL.

So, to sum up: you've found a type of query which should not be run on a 
large partitioned table.   

-- 
Josh Berkus                GreenPlum Inc
Community Liason       www.greenplum.com
415-752-2500       jberkus at greenplum.com