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 <stho...@peak6.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
> stho...@peak6.com
>
> ______________________________________________
>
> See http://www.peak6.com/email_disclaimer.php
> for terms and conditions related to this email
>
