Re: Partition table query performance

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';