The following bug has been logged online:
Bug reference: 5652
Logged by: Mladen Gogala
Email address: mladen.gogala@vmsinfo.com
PostgreSQL version: 8.4.4
Operating system: Red Hat Linux 5.5, 64b
Description: Optimizer does wrong thing with partitioned tables
Details:
Optimizer chooses to scan each partitioned table sequentially, instead of
using the available index:
news=# explain select max(created_at) from moreover_documents;
QUERY PLAN
----------------------------------------------------------------------------
----
---------------------------------------
Aggregate (cost=5115432.65..5115432.66 rows=1 width=8)
-> Append (cost=0.00..5017318.72 rows=39245572 width=8)
-> Seq Scan on moreover_documents (cost=0.00..10.20 rows=20
width=8)
-> Seq Scan on moreover_documents_y2010m06 moreover_documents
(cost=0
.00..236550.85 rows=1859585 width=8)
-> Seq Scan on moreover_documents_y2010m07 moreover_documents
(cost=0
.00..2073604.38 rows=16276938 width=8)
-> Seq Scan on moreover_documents_y2010m08 moreover_documents
(cost=0
.00..2022494.13 rows=15670513 width=8)
-> Seq Scan on moreover_documents_y2010m09 moreover_documents
(cost=0
.00..684618.36 rows=5438436 width=8)
-> Seq Scan on moreover_documents_y2010m10 moreover_documents
(cost=0
.00..10.20 rows=20 width=8)
-> Seq Scan on moreover_documents_y2010m11 moreover_documents
(cost=0
.00..10.20 rows=20 width=8)
-> Seq Scan on moreover_documents_y2010m12 moreover_documents
(cost=0
.00..10.20 rows=20 width=8)
-> Seq Scan on moreover_documents_y2011m01 moreover_documents
(cost=0
.00..10.20 rows=20 width=8)
When on single partition, the optimizer does the right thing:
news=# explain select max(created_at) from moreover_documents_y2010m09;
QUERY PLAN
----------------------------------------------------------------------------
----
------------------------------------------------------------
Result (cost=0.15..0.16 rows=1 width=0)
InitPlan 1 (returns $0)
-> Limit (cost=0.00..0.15 rows=1 width=8)
-> Index Scan Backward using mdocs_created_y2010m09 on
moreover_docu
ments_y2010m09 (cost=0.00..800757.60 rows=5438436 width=8)
Filter: (created_at IS NOT NULL)
(5 rows)
Index is available on each and every partition. There is only one way to
calculate the max for the entire partitioned table: to calculate max for
each partition and then select max of those. Optimizer should take into
account the available access paths for each partition, yet it doesn't do so.