On 05/27/2010 07:06:50 AM, Sergio Charpinel Jr. wrote: > - I'm running analyze in every insert, within the create table > function: > > CREATE OR REPLACE FUNCTION cria_tab_pmacct(text) > RETURNS void AS $$ > DECLARE > myrec RECORD; > BEGIN > SELECT 1 INTO myrec FROM pg_catalog.pg_class WHERE relkind = > 'r' AND > relname = $1 AND pg_catalog.pg_table_is_visible(oid) LIMIT 1; > IF NOT FOUND > THEN > EXECUTE 'CREATE TABLE ' || quote_ident($1) || ' ( > ..... > CONSTRAINT ' || $1 ||'_pk PRIMARY KEY > (stamp_inserted, ip_src, ip_dst, port_src, port_dst, ip_proto) > )'; > EXECUTE 'CREATE INDEX ibytes_' || $1 || ' ON ' || > quote_ident($1) || '(bytes)'; > ELSE > EXECUTE 'ANALYZE ' || $1; > END IF; > END; > $$ LANGUAGE plpgsql;
Analyzing after every insert is not a good idea. The problem, or the one I've found, really only exists when you insert a lot of rows into a new table that has not been analyzed. There are, as of the last few years, knobs to turn in the postgresql config file that control how this is done automatically. There may or may not be a good setting that deals well with both loading empty tables and adding/removing from existing large tables. Then again, maybe you just need to turn it on and it will work for you out of the box. See: http://www.postgresql.org/docs/8.4/static/runtime-config- autovacuum.html http://www.postgresql.org/docs/8.4/static/routine- vacuuming.html#AUTOVACUUM Perhaps one approach would be to set a per-table "analyze threshold" low on newly created tables and then reset the threshold once the table reaches steady state. In any case, I've not thought much about this in a while and so may not be making the best recommendations. I would suggest contacting the postgresql people with problem specifics and see what they suggest. Regards, Karl <k...@meme.com> Free Software: "You don't pay back, you pay forward." -- Robert A. Heinlein _______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
