partitioned table performance - Mailing list pgsql-performance

Hi!

I'm just wondering, I've got a table that is partitioned into monthly
tables:

media_downloads -> media_downloads_YYYYMM
 I\- id (primary key)
  \- created_on (timestamp criteria for the monthly table split)

There are constraints upon the created_on column, all needed insert
instead rules are defined too.
One additional hardship is that id are not monotone against created_on,
id1 < id2 does not imply created_on1 <= created_on2 :(
The table contains basically almost 100M rows, and the number is
growing. (the table will be about a 12GB pg_dump.)
All relevant indexes (primary key id, index on created_on) are defined
too.

The good thing is, queries like all rows in the last 7 days work
reasonable fast, the optimizer just checks the 1-2 last month tables.

Using postgres 8.1.4-0ubuntu1, I've got to implement the following
queries in a reasonable fast way:

-- sequential reading of rows
SELECT * FROM media_downloads WHERE id > 1000000 ORDER BY id LIMIT 100;

Against the same monolithic table with about 16.5M rows, I'm getting a
cost of 20.6 pages. (Index scan)

Against the partitioned tables, I'm getting a cost of 5406822 pages.
Now I understand, that without any additional conditions, postgresql
needs to do the query for all subtables first, but explain against the
subtables show costs of 4-5 pages.
events=#  explain select * from media_downloads where id >90000000 order
by id limit 100;

QUERY
PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=5406822.39..5406822.64 rows=100 width=1764)
   ->  Sort  (cost=5406822.39..5413639.50 rows=2726843 width=1764)
         Sort Key: public.media_downloads.id
         ->  Result  (cost=0.00..115960.71 rows=2726843 width=1764)
               ->  Append  (cost=0.00..115960.71 rows=2726843
width=1764)
                     ->  Seq Scan on media_downloads  (cost=0.00..10.50
rows=13 width=1764)
                           Filter: (id > 90000000)
                     ->  Index Scan using media_downloads_200510_pkey on
media_downloads_200510 media_downloads  (cost=0.00..3.75 rows=14
width=243)
                           Index Cond: (id > 90000000)
                     ->  Index Scan using media_downloads_200511_pkey on
media_downloads_200511 media_downloads  (cost=0.00..72.19 rows=172
width=239)
                           Index Cond: (id > 90000000)
                     ->  Index Scan using media_downloads_200512_pkey on
media_downloads_200512 media_downloads  (cost=0.00..603.64 rows=172
width=240)
                           Index Cond: (id > 90000000)
                     ->  Index Scan using media_downloads_200601_pkey on
media_downloads_200601 media_downloads  (cost=0.00..19.33 rows=232
width=239)
                           Index Cond: (id > 90000000)
                     ->  Index Scan using media_downloads_200602_pkey on
media_downloads_200602 media_downloads  (cost=0.00..56.82 rows=316
width=240)
                           Index Cond: (id > 90000000)
                     ->  Index Scan using media_downloads_200603_pkey on
media_downloads_200603 media_downloads  (cost=0.00..18.88 rows=270
width=243)
                           Index Cond: (id > 90000000)
                     ->  Index Scan using media_downloads_200604_pkey on
media_downloads_200604 media_downloads  (cost=0.00..1194.16 rows=939
width=298)
                           Index Cond: (id > 90000000)
                     ->  Index Scan using media_downloads_200605_pkey on
media_downloads_200605 media_downloads  (cost=0.00..79.28 rows=672
width=326)
                           Index Cond: (id > 90000000)
                     ->  Index Scan using media_downloads_200606_pkey on
media_downloads_200606 media_downloads  (cost=0.00..75.26 rows=1190
width=314)
                           Index Cond: (id > 90000000)
                     ->  Index Scan using media_downloads_200607_pkey on
media_downloads_200607 media_downloads  (cost=0.00..55.29 rows=1238
width=319)
                           Index Cond: (id > 90000000)
                     ->  Index Scan using media_downloads_200608_pkey on
media_downloads_200608 media_downloads  (cost=0.00..73.95 rows=1305
width=319)
                           Index Cond: (id > 90000000)
                     ->  Index Scan using media_downloads_200609_pkey on
media_downloads_200609 media_downloads  (cost=0.00..144.10 rows=1575
width=324)
                           Index Cond: (id > 90000000)
                     ->  Index Scan using media_downloads_200610_pkey on
media_downloads_200610 media_downloads  (cost=0.00..113532.57
rows=2718709 width=337)
                           Index Cond: (id > 90000000)
                     ->  Seq Scan on media_downloads_200611
media_downloads  (cost=0.00..10.50 rows=13 width=1764)
                           Filter: (id > 90000000)
                     ->  Seq Scan on media_downloads_200612
media_downloads  (cost=0.00..10.50 rows=13 width=1764)
                           Filter: (id > 90000000)
(37 rows)

events=#  explain select * from media_downloads_200610  where id
>90000000 order by id limit 100;
                                                         QUERY
PLAN

-----------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..4.18 rows=100 width=337)
   ->  Index Scan using media_downloads_200610_pkey on
media_downloads_200610  (cost=0.00..113582.70 rows=2719904 width=337)
         Index Cond: (id > 90000000)
(3 rows)

Interestingly, if one reformulates the query like that:

SELECT * FROM media_downloads WHERE id > 90000000 AND id < 90001000
ORDER BY id LIMIT 100;

results in a reasonable cost of 161.5 pages.

Now the above query is basically acceptable, as one iterate all rows
this way, but now I need to know max(id) to know when to stop my loop:

events=# explain select max(id) from media_downloads;
                                                  QUERY
PLAN
--------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=3676914.56..3676914.58 rows=1 width=4)
   ->  Append  (cost=0.00..3444211.85 rows=93081085 width=4)
         ->  Seq Scan on media_downloads  (cost=0.00..10.40 rows=40
width=4)
         ->  Seq Scan on media_downloads_200510 media_downloads
(cost=0.00..5615.84 rows=139884 width=4)
         ->  Seq Scan on media_downloads_200511 media_downloads
(cost=0.00..67446.56 rows=1724356 width=4)
         ->  Seq Scan on media_downloads_200512 media_downloads
(cost=0.00..66727.02 rows=1718302 width=4)
         ->  Seq Scan on media_downloads_200601 media_downloads
(cost=0.00..88799.91 rows=2321991 width=4)
         ->  Seq Scan on media_downloads_200602 media_downloads
(cost=0.00..121525.71 rows=3159571 width=4)
         ->  Seq Scan on media_downloads_200603 media_downloads
(cost=0.00..104205.40 rows=2701240 width=4)
         ->  Seq Scan on media_downloads_200604 media_downloads
(cost=0.00..342511.42 rows=9391242 width=4)
         ->  Seq Scan on media_downloads_200605 media_downloads
(cost=0.00..245167.39 rows=6724039 width=4)
         ->  Seq Scan on media_downloads_200606 media_downloads
(cost=0.00..430186.99 rows=11901499 width=4)
         ->  Seq Scan on media_downloads_200607 media_downloads
(cost=0.00..451313.72 rows=12380172 width=4)
         ->  Seq Scan on media_downloads_200608 media_downloads
(cost=0.00..474743.72 rows=13048372 width=4)
         ->  Seq Scan on media_downloads_200609 media_downloads
(cost=0.00..619711.52 rows=15754452 width=4)
         ->  Seq Scan on media_downloads_200610 media_downloads
(cost=0.00..426225.45 rows=12115845 width=4)
         ->  Seq Scan on media_downloads_200611 media_downloads
(cost=0.00..10.40 rows=40 width=4)
         ->  Seq Scan on media_downloads_200612 media_downloads
(cost=0.00..10.40 rows=40 width=4)
(18 rows)

events=# explain select max(id) from media_downloads_200610;
                                                                 QUERY
PLAN

---------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.04..0.05 rows=1 width=0)
   InitPlan
     ->  Limit  (cost=0.00..0.04 rows=1 width=4)
           ->  Index Scan Backward using media_downloads_200610_pkey on
media_downloads_200610  (cost=0.00..475660.29 rows=12115845 width=4)
                 Filter: (id IS NOT NULL)
(5 rows)

For me as a human, it's obvious, that max(media_downloads) ==
max(media_downloads_200612..media_downloads_200510).

Any ideas how to make the optimizer handle partitioned tables more
sensible?

Andreas