title: Efficient querying of multi-partition Postgres table
body: <p>I've just restructured my database to use <a href="http://www.postgresql.org/docs/8.2/static/ddl-partitioning.html" rel="noreferrer">partitioning</a> in Postgres 8.2. Now I have a problem with query performance:</p>
  
  <pre><code>SELECT *
  FROM my_table
  WHERE time_stamp &gt;= '2010-02-10' and time_stamp &lt; '2010-02-11'
  ORDER BY id DESC
  LIMIT 100;
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>I can fix it with:</p>
  
  <pre><code>SELECT * FROM (
    SELECT *
    FROM my_table_part_a
    WHERE time_stamp &gt;= '2010-02-10' and time_stamp &lt; '2010-02-11'
    ORDER BY id DESC
    LIMIT 100) t
  UNION ALL
  SELECT * FROM (
    SELECT *
    FROM my_table_part_b
    WHERE time_stamp &gt;= '2010-02-10' and time_stamp &lt; '2010-02-11'
    ORDER BY id DESC
    LIMIT 100) t
  UNION ALL
    ... and so on ...
  ORDER BY id DESC
  LIMIT 100
  </code></pre>
  
  <p>This runs quickly.  The partitions where the times-stamps are out-of-range aren't even included in the query plan.</p>
  
  <p>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?</p>
  
  <p>Basically, can I avoid the pain of dynamically building the big UNION query over each partition that happens to be currently defined?</p>
  
  <p><strong>EDIT:</strong> I have constraint_exclusion enabled (thanks @Vinko Vrsalovic)</p>