From: | "Kenneth Cox" <kenstir(at)gmail(dot)com> |
---|---|
To: | "Greg Jaman" <gjaman(at)gmail(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: partition query using Seq Scan even when index is present |
Date: | 2009-09-03 16:13:36 |
Message-ID: | op.uzovcy2n5ru9c3@kent60.office.vivox.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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;
From | Date | Subject | |
---|---|---|---|
Next Message | Andy Colson | 2009-09-03 17:12:37 | Re: Seeking performance advice and explanation for high I/O on 8.3 |
Previous Message | Kevin Grittner | 2009-09-03 15:27:50 | Re: Slow select times on select with xpath |