I have a problem with partitioning and I'm wondering if anyone can provide
some insight. I'm trying to find the max value of a column across multiple
partitions. The query against the partition set is quite slow while queries
against child partitions is very fast!
I setup a basic Range Partition table definition.
A parent table: Data { dataID, sensorID, value, ts }
child tables Data_YYYY_WEEKNO { dataID, sensorID, value, ts} inherited
from Data
Each child tables has a primary key index on dataID and a
composite index on (sensorID, ts).
Each child has check constraints for the week range identified in
the table name (non overlapping)
I want to perform a simple operation: select the max ts (timestamp) giving
a sensorID. Given my indexs on the table, this should be a simple and fast
operation.
DB=# EXPLAIN ANALYZE select max(ts) from "Data" where valid=true and
"sensorID"=8293 ;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=334862.92..334862.93 rows=1 width=8) (actual
time=85183.381..85183.383 rows=1 loops=1)
-> Append (cost=2.30..329397.68 rows=2186096 width=8) (actual
time=1.263..76592.755 rows=2205408 loops=1)
-> Bitmap Heap Scan on "Data" (cost=2.30..8.84 rows=3 width=8)
(actual time=0.027..0.027 rows=0 loops=1)
Recheck Cond: ("sensorID" = 8293)
Filter: valid
-> Bitmap Index Scan on "def_data_sensorID_ts"
(cost=0.00..2.30 rows=6 width=0) (actual time=0.021..0.021 rows=0 loops=1)
Index Cond: ("sensorID" = 8293)
-> *Index Scan using "Data_2008_01_sensorID_ts_index" on
"Data_2008_01" "Data"* (cost=0.00..4.27 rows=1 width=8) (actual
time=0.014..0.014 rows=0 loops=1)
Index Cond: ("sensorID" = 8293)
Filter: valid
-> *Bitmap Heap Scan on "Data_2008_02" "Data"* (cost=3.01..121.08
rows=98 width=8) (actual time=0.017..0.017 rows=0 loops=1)
Recheck Cond: ("sensorID" = 8293)
Filter: valid
-> Bitmap Index Scan on "Data_2008_02_sensorID_ts_index"
(cost=0.00..2.99 rows=98 width=0) (actual time=0.011..0.011 rows=0 loops=1)
Index Cond: ("sensorID" = 8293)
.
.
. (omitted a list of all partitions with same as data above)
.
Total runtime: 85188.694 ms
When I query against a specific partition:
DB=# EXPLAIN ANALYZE select max(ts) from "Data_2008_48" where valid=true
and "sensorID"=8293 ;
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.10..0.11 rows=1 width=0) (actual time=3.830..3.832 rows=1
loops=1)
InitPlan
-> Limit (cost=0.00..0.10 rows=1 width=8) (actual time=3.817..3.819
rows=1 loops=1)
-> Index Scan Backward using "Data_2008_48_sensorID_ts_index" on
"Data_2008_48" (cost=0.00..15304.55 rows=148959 width=8) (actual
time=3.813..3.813 rows=1 loops=1)
Index Cond: ("sensorID" = 8293)
Filter: ((ts IS NOT NULL) AND valid)
Total runtime: 0.225 ms
The query plan against the child partition makes sense - Uses the index to
find the max value. The query plan for the partitions uses a combination of
bitmap heap scans and index scans.
Why would the query plan choose to use a bitmap heap scan after bitmap index
scan or is that the best choice? (what is it doing?) and what can I do to
speed up this query?
As a sanity check I did a union query of all partitions to find the max(ts).
My manual union query executed in 13ms vs the query against the parent table
that was 85,188ms!!!.
Greg Jaman
