pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPartition table query performance
3+ messages / 2 participants
[nested] [flat]
* Partition table query performance
@ 2008-11-26 04:07 Greg Jaman <gjaman@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Greg Jaman @ 2008-11-26 04:07 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Partition table query performance
@ 2008-11-27 00:48 Gregory Stark <stark@enterprisedb.com>
parent: Greg Jaman <gjaman@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Gregory Stark @ 2008-11-27 00:48 UTC (permalink / raw)
To: Greg Jaman <gjaman@gmail.com>; +Cc: pgsql-performance
"Greg Jaman" <gjaman@gmail.com> writes:
> 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'm afraid this is a known problematic use case of Postgres's current
partitioning support. Postgres is not capable of finding the plan which you're
undoubtedly looking for where it uses the same plan as your child table query
iterating over the partitions.
There are several groups working to improve this in different ways but none of
them appear to be on track to be in 8.4 so it will be 8.5 or later before they
appear. Sorry.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Partition table query performance
@ 2008-11-27 17:25 Greg Jaman <gjaman@gmail.com>
parent: Gregory Stark <stark@enterprisedb.com>
0 siblings, 0 replies; 3+ messages in thread
From: Greg Jaman @ 2008-11-27 17:25 UTC (permalink / raw)
To: Gregory Stark <stark@enterprisedb.com>; +Cc: pgsql-performance
Thanks Gregory,
I was on IRC yesterday and a few people indicated the same thing...
Searching for the last reading is a very important function for our
database. I wrote the below function searches all child tables for the
max. It is not optimization because it doesn't omit tables by look at the
check constraints on child tables to see if the last found max is greater
than the constraints. Right now this function executes in 50ms vs the 80+
for the same query against the partition set.
create or replace function Data_max(in_sensorID integer) returns bigint AS
$$
DECLARE
childtable RECORD;
childres RECORD;
max_dataID bigint := NULL;
max_ts timestamp without time zone;
BEGIN
FOR childtable in select pc.relname as relname from pg_class pc join
pg_inherits pi on pc.oid=pi.inhrelid where inhparent=(select oid from
pg_class where relname='Data')
LOOP
EXECUTE ' SELECT "dataID", ts FROM ' || quote_ident(
childtable.relname )
|| ' WHERE "sensorID"=' || quote_literal(in_sensorID) || ' order
by ts desc limit 1 ' INTO childres;
IF childres is not NULL THEN
IF max_ts is NULL OR childres.ts > max_ts THEN
max_ts:= childres.ts;
max_dataID:= childres."dataID";
END IF;
END IF;
END LOOP;
return max_dataID;
END;
$$
language 'plpgsql';
On Wed, Nov 26, 2008 at 4:48 PM, Gregory Stark <stark@enterprisedb.com>wrote:
> "Greg Jaman" <gjaman@gmail.com> writes:
>
> > 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'm afraid this is a known problematic use case of Postgres's current
> partitioning support. Postgres is not capable of finding the plan which
> you're
> undoubtedly looking for where it uses the same plan as your child table
> query
> iterating over the partitions.
>
> There are several groups working to improve this in different ways but none
> of
> them appear to be on track to be in 8.4 so it will be 8.5 or later before
> they
> appear. Sorry.
>
> --
> Gregory Stark
> EnterpriseDB http://www.enterprisedb.com
> Ask me about EnterpriseDB's 24x7 Postgres support!
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-11-27 17:25 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-11-26 04:07 Partition table query performance Greg Jaman <gjaman@gmail.com>
2008-11-27 00:48 ` Gregory Stark <stark@enterprisedb.com>
2008-11-27 17:25 ` Greg Jaman <gjaman@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox