Inefficient min/max against partition (ver 9.1.1) - Mailing list pgsql-performance

On PostgreSQL 9.1.1, I'm experiencing extremely slow/inefficient min/max queries against a partitioned table, despite
therecent improvements made in version 9.1. I haven't seen this issue discussed since 9.1 was released, so I wanted to
providean example of the inefficient execution plan in case this is not a known issue with the new version. 

In my case, the query analyzer chooses the wrong index to scan of the child table when the query is made against the
parenttable. The tables are partitioned by 'fctid'. The query 'SELECT max(date) FROM table WHERE fctid=301 and
sec_id=1'correctly uses the index (sec_id, date) when querying against the child table (0.1ms), but when run against
theparent table, the planner chooses to scan the (date, sec_id) primary key instead, resulting in a full table scan in
someinstances (49 minutes!). 

In my example the parent case is empty and all child tables have non-overlapping check constraints. Below is the schema
andexecution plans. 

Let me know if you need anything else. Thanks, Robert


Parent table schema:
template1=# \d f_data
     Table "public.f_data"
 Column |   Type   | Modifiers
--------+----------+-----------
 sec_id | integer  | not null
 date   | date     | not null
 fctid  | smallint | not null
 value  | real     | not null
Indexes:
    "f_data_pkey" PRIMARY KEY, btree (fctid, date, sec_id)
Triggers:
    insert_f_data_trigger BEFORE INSERT ON f_data FOR EACH ROW EXECUTE PROCEDURE f_data_insert_trigger()
Number of child tables: 7 (Use \d+ to list them.)

Child table schema:
template1=# \d f_data301
   Table "public.f_data301"
 Column |   Type   | Modifiers
--------+----------+-----------
 sec_id | integer  | not null
 date   | date     | not null
 fctid  | smallint | not null
 value  | real     | not null
Indexes:
    "pk_f_data_rsi2" PRIMARY KEY, btree (date, sec_id) CLUSTER
    "f_data_rsi2_idx" btree (sec_id, date)
Check constraints:
    "f_data_rsi2_fctid_check" CHECK (fctid = 301)
Inherits: f_data


template1=# EXPLAIN ANALYZE SELECT max(date) FROM f_data301 WHERE fctid=301 and sec_id=1;
                                                                       QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Result  (cost=1.84..1.85 rows=1 width=0) (actual time=0.077..0.078 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..1.84 rows=1 width=4) (actual time=0.074..0.074 rows=0 loops=1)
           ->  Index Scan Backward using f_data_rsi2_idx on f_data301  (cost=0.00..6370.59 rows=3465 width=4) (a
                 Index Cond: ((sec_id = 1) AND (date IS NOT NULL))
                 Filter: (fctid = 301)
 Total runtime: 0.132 ms
(7 rows)


template1=# EXPLAIN ANALYZE SELECT max(date) FROM f_data where fctid=301 and sec_id=1;
                                                                                    QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Result  (cost=522.10..522.11 rows=1 width=0) (actual time=2921439.560..2921439.561 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.02..522.10 rows=1 width=4) (actual time=2921439.554..2921439.554 rows=0 loops=1)
           ->  Merge Append  (cost=0.02..1809543.34 rows=3466 width=4) (actual time=2921439.551..2921439.551 row
                 Sort Key: public.f_data.date
                 ->  Sort  (cost=0.01..0.02 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=1)
                       Sort Key: public.f_data.date
                       Sort Method: quicksort  Memory: 25kB
                       ->  Seq Scan on f_data  (cost=0.00..0.00 rows=1 width=4) (actual time=0.002..0.002 rows=0
                             Filter: ((date IS NOT NULL) AND (fctid = 301) AND (sec_id = 1))
                 ->  Index Scan Backward using pk_f_data_rsi2 on f_data301 f_data  (cost=0.00..1809499.99 rows=3
                       Index Cond: ((date IS NOT NULL) AND (sec_id = 1))
                       Filter: (fctid = 301)
 Total runtime: 2921439.645 ms
(14 rows)


template1=# select version();
                                                    version
---------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-50), 64-bit

Robert McGehee, CFA
Geode Capital Management, LLC
One Post Office Square, 28th Floor | Boston, MA | 02109
Direct: (617)392-8396

This e-mail, and any attachments hereto, are intended for use by the addressee(s) only and may contain information that
is(i) confidential information of Geode Capital Management, LLC and/or its affiliates, and/or (ii) proprietary
informationof Geode Capital Management, LLC and/or its affiliates. If you are not the intended recipient of this
e-mail,or if you have otherwise received this e-mail in error, please immediately notify me by telephone (you may call
collect),or by e-mail, and please permanently delete the original, any print outs and any copies of the foregoing. Any
dissemination,distribution or copying of this e-mail is strictly prohibited.