Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id CE6516503A3 for ; Thu, 27 Nov 2008 13:25:52 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 18755-01-5 for ; Thu, 27 Nov 2008 13:25:49 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.181]) by mail.postgresql.org (Postfix) with ESMTP id 5F9FB6503B8 for ; Thu, 27 Nov 2008 13:25:49 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id j4so515528wah.19 for ; Thu, 27 Nov 2008 09:25:48 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type:references; bh=8UnBvREP8ixhliLOvmFKCXNXOU6YUUdl1EMZHOvBl4w=; b=oU+iJ9XsLQm47Wq0T21y728JTB3oax8v7pSIMZB1QuEN0okWglKPrCfBVZc6uu6gz+ wIomkGU1RlVxA/F9eAW8lzxOQSuVzPD5KuZdTWTXaBWl4f5gCVzLzNdu8pj9B8tbv2eU lTPSCeLCDZa7eMn04yakS9umzSPT8Kym+RXu8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:references; b=keqs5Kpv+FI1Gjo9obtTTMehRvvEszE58RYttpzJYXmw7xq6S02NrvN+4v+x4u4pWi 234ypl7iJJL2Z3dRnxQpLBcthk2mq1CvI++MhSWwpmwptphkcbRexrySy/QLMLwG4TV0 BFUMFQZidg2tizuvAXo+lPDeSn7yVL6kyTDsk= Received: by 10.114.201.1 with SMTP id y1mr4098238waf.23.1227806747993; Thu, 27 Nov 2008 09:25:47 -0800 (PST) Received: by 10.115.22.20 with HTTP; Thu, 27 Nov 2008 09:25:47 -0800 (PST) Message-ID: Date: Thu, 27 Nov 2008 09:25:47 -0800 From: "Greg Jaman" To: "Gregory Stark" Subject: Re: Partition table query performance Cc: pgsql-performance@postgresql.org In-Reply-To: <8763maouy9.fsf@oxford.xeocode.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_22203_10983892.1227806747992" References: <8763maouy9.fsf@oxford.xeocode.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.411 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001, SUBJECT_FUZZY_TION=0.41 X-Spam-Level: X-Archive-Number: 200811/285 X-Sequence-Number: 32016 ------=_Part_22203_10983892.1227806747992 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 wrote: > "Greg Jaman" 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! > ------=_Part_22203_10983892.1227806747992 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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!

------=_Part_22203_10983892.1227806747992--