Efficient querying of multi-partition Postgres table

Question

I've just restructured my database to use partitioning in Postgres 8.2. Now I have a problem with query performance:

SELECT *
FROM my_table
WHERE time_stamp >= '2010-02-10' and time_stamp < '2010-02-11'
ORDER BY id DESC
LIMIT 100;

There are 45 million rows in the table. Prior to partitioning, this would use a reverse index scan and stop as soon as it hit the limit.

After partitioning (on time_stamp ranges), Postgres does a full index scan of the master table and the relevant partition and merges the results, sorts them, then applies the limit. This takes way too long.

I can fix it with:

SELECT * FROM (
  SELECT *
  FROM my_table_part_a
  WHERE time_stamp >= '2010-02-10' and time_stamp < '2010-02-11'
  ORDER BY id DESC
  LIMIT 100) t
UNION ALL
SELECT * FROM (
  SELECT *
  FROM my_table_part_b
  WHERE time_stamp >= '2010-02-10' and time_stamp < '2010-02-11'
  ORDER BY id DESC
  LIMIT 100) t
UNION ALL
  ... and so on ...
ORDER BY id DESC
LIMIT 100

This runs quickly. The partitions where the times-stamps are out-of-range aren't even included in the query plan.

My question is: Is there some hint or syntax I can use in Postgres 8.2 to prevent the query-planner from scanning the full table but still using simple syntax that only refers to the master table?

Basically, can I avoid the pain of dynamically building the big UNION query over each partition that happens to be currently defined?

EDIT: I have constraint_exclusion enabled (thanks @Vinko Vrsalovic)

Answer 1

I had a similar problem that I was able fix by casting conditions in WHERE. EG: (assuming the time_stamp column is timestamptz type)

WHERE time_stamp >= '2010-02-10'::timestamptz and time_stamp < '2010-02-11'::timestamptz

Also, make sure the CHECK condition on the table is defined the same way... EG: CHECK (time_stamp < '2010-02-10'::timestamptz)