Hi,
In Postgres 8.1, MAX() was rewritten to use index backward search without
doing seq_scan. This achieves a huge performance gain. But I found that when
I use MAX() on a partitioned table, it reverses back to the old seq_scan
plan. Wouldn't it be more efficient to get MAX() from each partition table
(using index) and then select the MAX() among the max values?
Attached is the EXPLAIN output. I have index on 'time' on each partition
table.
--Qiao
----------------------------------------------------------------------------
----
report_log=> explain select max(time) from url_access_base;
QUERY PLAN
----------------------------------------------------------------------------
--------------------------------
Aggregate (cost=542341.71..542341.72 rows=1 width=8)
-> Append (cost=0.00..490943.97 rows=20559097 width=8)
-> Seq Scan on url_access_base (cost=0.00..15.00 rows=500
width=8)
-> Seq Scan on url_access_2006_06_02 url_access_base
(cost=0.00..7728.43 rows=326343 width=8)
-> Seq Scan on url_access_2006_06_03 url_access_base
(cost=0.00..23818.00 rows=1005000 width=8)
-> Seq Scan on url_access_2006_06_04 url_access_base
(cost=0.00..23890.00 rows=1008000 width=8)
-> Seq Scan on url_access_2006_06_05 url_access_base
(cost=0.00..126426.85 rows=5291885 width=8)
-> Seq Scan on url_access_2006_05_31 url_access_base
(cost=0.00..7220.01 rows=302001 width=8)
-> Seq Scan on url_access_2006_05_22 url_access_base
(cost=0.00..292.11 rows=12211 width=8)
-> Seq Scan on url_access_2006_05_23 url_access_base
(cost=0.00..8.00 rows=300 width=8)
-> Seq Scan on url_access_2006_05_24 url_access_base
(cost=0.00..13021.30 rows=544630 width=8)
-> Seq Scan on url_access_2006_05_25 url_access_base
(cost=0.00..59.50 rows=2450 width=8)
-> Seq Scan on url_access_2006_05_26 url_access_base
(cost=0.00..735.00 rows=31000 width=8)
-> Seq Scan on url_access_2006_06_01 url_access_base
(cost=0.00..208633.43 rows=8726643 width=8)
-> Seq Scan on url_access_2006_06_07 url_access_base
(cost=0.00..78903.17 rows=3300017 width=8)
-> Seq Scan on url_access_2006_06_08 url_access_base
(cost=0.00..190.00 rows=8000 width=8)
-> Seq Scan on url_access_2006_05_01 url_access_base
(cost=0.00..3.17 rows=117 width=8)
(17 rows)
report_log=> explain select max(time) from url_access_2006_05_23;
QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------
Result (cost=0.03..0.04 rows=1 width=0)
InitPlan
-> Limit (cost=0.00..0.03 rows=1 width=8)
-> Index Scan Backward using url_access_2006_05_23_time_idx on
url_access_2006_05_23 (cost=0.00..10.30 rows=300 width=8)
Filter: ("time" IS NOT NULL)
(5 rows)
report_log=> explain select max(time) from url_access_2006_05_24;
QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------
Result (cost=0.03..0.04 rows=1 width=0)
InitPlan
-> Limit (cost=0.00..0.03 rows=1 width=8)
-> Index Scan Backward using url_access_2006_05_24_time_idx on
url_access_2006_05_24 (cost=0.00..15458.93 rows=544630 width=8)
Filter: ("time" IS NOT NULL)
(5 rows)
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
