Partitions and the optimizer.

Optimizer doesn't behave well when the tables are partitioned. Here is
the problem:

news=# explain
select max("document#") from moreover_documents_y2010m08;

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 pk_moreover_documents_y2010m08
on moreover_documents_y2010m08 (cost=0.00..2169871.61 rows=14615132
width=8)
Filter: ("document#" IS NOT NULL)
(5 rows)

Time: 31.191 ms
news=# explain
select max("document#") from moreover_documents;
QUERY
PLAN
-----------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4227019.40..4227019.41 rows=1 width=8)
-> Append (cost=0.00..4145103.32 rows=32766432 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..236523.53 rows=1856853 width=8)
-> Seq Scan on moreover_documents_y2010m07 moreover_documents
(cost=0.00..2073778.27 rows=16294327 width=8)
-> Seq Scan on moreover_documents_y2010m08 moreover_documents
(cost=0.00..1834740.32 rows=14615132 width=8)
-> Seq Scan on moreover_documents_y2010m09 moreover_documents
(cost=0.00..10.20 rows=20 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)
(11 rows)

Time: 31.961 ms
news=#

I have several partitions for the table, 3 of them are loaded. When I
search for the maximum value of the primary key column on a single
partition, the access path is as it should be, using the PK index. Each
of the partitions has the same PK. When I try selecting the maximum on
the entire table, the indexes on the partitions are not used, Postgres
does the full scan instead. The difference in timing is drastic:
news=# select max("document#") from moreover_documents_y2010m08;
max
------------
1175107508
(1 row)

Time: 56.778 ms
news=# select max("document#") from moreover_documents;
max
------------
1175107508
(1 row)

Time: 200490.228 ms
news=#

--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions