From: Greg Jaman <gjaman@gmail.com>
To: Gregory Stark <stark@enterprisedb.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Partition table query performance
Date: Thu, 27 Nov 2008 09:25:47 -0800
Message-ID: <b72893ad0811270925n2ea1a586ked1d3c1fdaf8dba8@mail.gmail.com> (raw)
In-Reply-To: <8763maouy9.fsf@oxford.xeocode.com>
References: <b72893ad0811252007h33cf477dx54d5468705facaaa@mail.gmail.com>
<8763maouy9.fsf@oxford.xeocode.com>
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!
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gjaman@gmail.com, stark@enterprisedb.com
Subject: Re: Partition table query performance
In-Reply-To: <b72893ad0811270925n2ea1a586ked1d3c1fdaf8dba8@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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