title: PostgreSQL+table partitioning: inefficient max() and min()
body: <p>I have a huge partitioned table stored at a PostgreSQL table. Each child table has an index and a check constraint on its id, e.g. (irrelevant deatils removed for clarity):</p>
  
  <pre><code>Master table: points
      Column     |            Type             |       Modifiers        
  ---------------+-----------------------------+------------------------
   id            | bigint                      |
   creation_time | timestamp without time zone | 
   the_geom      | geometry                    | 
  
  
  Sub-table points_01
      Column     |            Type             |        Modifiers        
  ---------------+-----------------------------+-------------------------
       id            | bigint                      | 
   creation_time | timestamp without time zone | 
   the_geom      | geometry                    | 
  
  Indexes:
      "points_01_pkey" PRIMARY KEY, btree (id)
      "points_01_creation_time_idx" btree (creation_time)
      "points_01_the_geom_idx" gist (the_geom) CLUSTER
  Check constraints:
      "enforce_srid_the_geom" CHECK (srid(the_geom) = 4326)
      "id_gps_points_2010_08_22__14_47_04_check" 
                 CHECK (id &gt;= 1000000::bigint AND id &lt;= 2000000::bigint)
  </code></pre>
  
  <p>Now,</p>
  
  <pre><code>SELECT max(id) FROM points_01
  </code></pre>
  
  <p>is instant, but:</p>
  
  <pre><code>SELECT max(id) FROM points
  </code></pre>
  
  <p>which is a master table for <code>points_01 .. points_60</code> and should take very little time using check constraints, takes more than an hour because the query planner does not utilize the check constraints.</p>
  
  <p>According to the PostgreSQL wiki (last section of <a href="http://wiki.postgresql.org/wiki/Table_partitioning" rel="nofollow">this page</a>), this is a known issue that would be fixed in the next versions.</p>
  
  <p>Is there a good hack that will make the query planner utilize the check constraints and indices of sub-tables for <code>max()</code> and <code>min()</code> queries?</p>
  
  <p>Thanks,</p>
  
  <p>Adam</p>