PostgreSQL+table partitioning: inefficient max() and min()

Question

Hi,

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):

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 >= 1000000::bigint AND id <= 2000000::bigint)

Now,

SELECT max(id) FROM points_01

is instant, but:

SELECT max(id) FROM points

which is a master table for points_01 .. points_60 and should take very little time using check constraints, takes more than an hour because the query planner does not utilize the check constraints.

According to the PostgreSQL wiki (last section of this page), this is a known issue that would be fixed in the next versions.

Is there a good hack that will make the query planner utilize the check constraints and indices of sub-tables for max() and min() queries?

Thanks,

Adam

Answer 1

I don't know if it will work, but you could try this:

For that session, you could disable all access strategies but indexed ones:

db=> set enable_seqscan = off;
db=> set enable_tidscan = off;
db=> -- your query goes here

This way, only bitmapscan and indexscan would be enabled. PostgreSQL will have no choice but to use indexes to access data on the table.

After running your query, remember to reenable seqscan and tidscan by doing:

db=> set enable_seqscan = on;
db=> set enable_tidscan = on;

Otherwise, those access strategies will be disabled for the session from that point on.

Answer 2

Short answer: No. At this point in time, there's no way to make the Postgres planner understand that some aggregate functions can check the constraints on child partitions first. Its fairly easy to prove for specific case of min and max, but for aggregates in general, its a tough case.

You can always write it as a UNION of several partitions when it just has to be done...

Answer 3

I don't know much about postgres but you could could try this query (My query syntax may be not right due to lack of experience with postgres query's):

SELECT id FROM points a WHERE id > ALL (SELECT id FROM x WHERE x.id != a.id)

I'm curious if this works.