Re: seq scan in the case of max() on the primary key column

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
>