Partitioned tables - planner wont use indexes - Mailing list pgsql-performance

Hi List;

Sorry if this is a dupe, my first post never showed up...

I'm having some performance issues with a partitioned table. We have a
VERY large table that we've partitioned by day.

Currently we have 17 partitions - each partition table contains >
700million rows.
One of the things we need to query is the min date from the master
table - we may explore alternatives for this particular query, however
even if we fix this query I think we have a fundamental issue with the
use of indexes (actuallt the non-use) by the planner.

Below is a sample of the DDL used to create our tables and an explain
showing that the planner wants to do a sequential scan on each
partition. We do have "constraint_elimination = on" set in the
postgresql.conf file.

I tried removing the index from the part_master table and got the same
result

Likewise the costs associated with the seq scans seem to be way off
(yes I've run analyze on the master and all partition tables) - I ran
the actual SQL statement below and killed it after about 15min.

Thanks in advance for any help, advice, etc...




Tables:

------------------------------------------
-- Master Table
------------------------------------------
CREATE TABLE part_master (
    filename character varying(100),
    logdate date,
    ... -- about 50 more columns go here
    loghour date,
    url character varying(500),
    customer character varying(500)
);
CREATE INDEX master_logdate ON part_master USING btree (logdate);

------------------------------------------
-- Partitions:
------------------------------------------

------------------------------------------
-- part_20080319
------------------------------------------
CREATE TABLE part_20080319 (CONSTRAINT part_20080319_logdate_check
    CHECK ((logdate = '2008-03-19'::date))
)
INHERITS (part_master);


CREATE INDEX idx_part_20080319_customer ON part_20080319 USING btree
(customer);
CREATE INDEX idx_part_20080319_logdate ON part_20080319 USING btree
(logdate);
CREATE INDEX idx_part_20080319_loghour ON part_20080319 USING btree
(loghour);


------------------------------------------
-- part_20080320
------------------------------------------
CREATE TABLE part_20080320 (CONSTRAINT part_20080320_logdate_check
    CHECK ((logdate = '2008-03-20'::date))
)
INHERITS (part_master);


CREATE INDEX idx_part_20080320_customer ON part_20080320 USING btree
(customer);
CREATE INDEX idx_part_20080320_logdate ON part_20080320 USING btree
(logdate);
CREATE INDEX idx_part_20080320_loghour ON part_20080320 USING btree
(loghour);


-- And so on, thru part_20080404



------------------------------------------
-- explain plan
------------------------------------------

myDB=# explain SELECT min(logdate) FROM part_master;
                                                       QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------
Aggregate  (cost=117070810.10..117070810.11 rows=1 width=4)
   ->  Append  (cost=0.00..114866502.48 rows=881723048 width=4)
         ->  Seq Scan on part_master  (cost=0.00..85596244.18
rows=679385718 width=4)
         ->  Seq Scan on part_20080319 part  (cost=0.00..212860.86
rows=1674986 width=4)
         ->  Seq Scan on part_20080320 part  (cost=0.00..1753802.51
rows=13782951 width=4)
         ->  Seq Scan on part_20080321 part  (cost=0.00..2061636.83
rows=15881283 width=4)
         ->  Seq Scan on part_20080322 part  (cost=0.00..1965144.71
rows=14936971 width=4)
         ->  Seq Scan on part_20080323 part  (cost=0.00..1614413.18
rows=12345618 width=4)
         ->  Seq Scan on part_20080324 part  (cost=0.00..1926520.22
rows=14741022 width=4)
         ->  Seq Scan on part_20080325 part  (cost=0.00..2356704.22
rows=18477622 width=4)
         ->  Seq Scan on part_20080326 part  (cost=0.00..1889267.71
rows=14512171 width=4)
         ->  Seq Scan on part_20080327 part  (cost=0.00..1622100.34
rows=12445034 width=4)
         ->  Seq Scan on part_20080328 part  (cost=0.00..1711779.49
rows=12885749 width=4)
         ->  Seq Scan on part_20080329 part  (cost=0.00..1568192.94
rows=11958394 width=4)
         ->  Seq Scan on part_20080330 part  (cost=0.00..1521204.64
rows=11676564 width=4)
         ->  Seq Scan on part_20080331 part  (cost=0.00..1587138.77
rows=12180377 width=4)
         ->  Seq Scan on part_20080401 part  (cost=0.00..2324352.82
rows=18211382 width=4)
         ->  Seq Scan on part_20080402 part  (cost=0.00..2891295.04
rows=6693804 width=4)
         ->  Seq Scan on part_20080403 part  (cost=0.00..1707327.48
rows=5748348 width=4)
         ->  Seq Scan on part_20080404 part  (cost=0.00..556516.54
rows=4185054 width=4)
(20 rows)