Re: partition query using Seq Scan even when index is present

Thank you, Greg!  I tweaked your function to use recursion to search all
inherited tables; my inheritance structure is two levels deep.

This function is for integers only; I will copy/waste to create one for
timestamps.  Extra credit for anyone who can rewrite it to be polymorphic.

-- Same as max(_colname) from _relname but much faster for inherited
-- tables with an index on _colname.  In postgresql-8.3.6 a naive query
-- on a parent table will not use the indexes on the child tables.
create or replace function partition_max_int(_relname text, _colname text)
returns int AS
$$
declare
   childtable RECORD;
   childres RECORD;
   maxval int;
   tmpval int;
   sql text;
begin
   -- find max in this table (only)
   sql := 'select max('||_colname||') from only '||quote_ident(_relname);
   execute sql into maxval;

   -- recurse to find max in descendants
   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=_relname)
   LOOP
     tmpval := partition_max_int(childtable.relname, _colname);
     IF tmpval is not NULL and (tmpval > maxval or maxval is null) THEN
       maxval := tmpval;
     END IF;
   END LOOP;

   return maxval;
end;
$$
language 'plpgsql' STABLE;