From: | Svetlin Manavski <svetlin(dot)manavski(at)gmail(dot)com> |
---|---|
To: | sthomas(at)peak6(dot)com |
Cc: | Magnus Hagander <magnus(at)hagander(dot)net>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: seq scan in the case of max() on the primary key column |
Date: | 2011-06-17 11:22:21 |
Message-ID: | BANLkTikohJ71z=Q0XmEwME6YRD8ev33mwQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Yes, confirmed that the problem is in the partitioned table.
Shaun, that solution is brilliant.
Thank you,
Svetlin Manavski
On Thu, Jun 16, 2011 at 7:36 PM, Shaun Thomas <sthomas(at)peak6(dot)com> wrote:
> On 06/16/2011 12:25 PM, Magnus Hagander wrote:
>
> PostgreSQL 9.0 is unable to use an index scan to find min/max on a
>> partitioned table. 9.1, however, can do that.
>>
>
> Unfortunately this is true. You can fake it this way though:
>
> /**
> * Return the Maximum INT Value for a Partitioned Table Column
> *
> * @param string Name of Schema of the base partition table.
> * @param string Name of the base partition table.
> * @param string Name of column to search.
> */
> CREATE OR REPLACE FUNCTION spc_max_part_int(VARCHAR, VARCHAR, VARCHAR)
> RETURNS INT AS
> $$
> DECLARE
>
> sSchema ALIAS FOR $1;
> sTable ALIAS FOR $2;
> sColName ALIAS FOR $3;
>
> sChild VARCHAR;
> nMax INT;
> nTemp INT;
> nParent OID;
>
> BEGIN
>
> EXECUTE '
> SELECT max(' || sColName ||')
> FROM ONLY ' || sSchema || '.' || quote_ident(sTable)
> INTO nMax;
>
> SELECT INTO nParent t.oid
> FROM pg_class t
> JOIN pg_namespace n ON (t.relnamespace=n.oid)
> WHERE n.nspname = sSchema
> AND t.relname = sTable;
>
> FOR sChild IN
> SELECT t.relname
> FROM pg_class t
> JOIN pg_inherits c ON (c.inhrelid=t.oid AND c.inhparent=nParent)
> LOOP
> nTemp := utility.spc_max_part_int(sSchema, sChild, sColName);
> nMax := greatest(nTemp, nMax);
> END LOOP;
>
> RETURN nMax;
>
> END;
> $$ LANGUAGE plpgsql STABLE;
>
>
> You can call that instead of max, and it'll be much faster. You can create
> an analog for min if you need it. So for this, you'd call:
>
> SELECT spc_max_part_int('appqosdata', 'tcpsessions', 'id');
>
> Someone probably has a better solution. :)
>
> --
> Shaun Thomas
> OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
> 312-676-8870
> sthomas(at)peak6(dot)com
>
> ______________________________________________
>
> See http://www.peak6.com/email_disclaimer.php
> for terms and conditions related to this email
>
From | Date | Subject | |
---|---|---|---|
Next Message | Shaun Thomas | 2011-06-17 12:43:46 | Re: seq scan in the case of max() on the primary key column |
Previous Message | jesper | 2011-06-17 09:30:33 | Re: Performance advice for a new low(er)-power server |