It could be > 1 Million rows. SELECT is: SELECT * FROM another_table WHERE eventtime > (SELECT e FROM tags WHERE id = $1) AND eventtime < (SELECT e FROM tags WHERE id = $2) ; $1 and $2 are integers. SELECT ran just now, returning >1Million rows, in 1.6 seconds. Inserting into work table causes weird behavior - it takes over a minute, PG CPU climbs to 100%, but then other subsequent queries sometimes seem to slow down too. After a lot of these, sometimes PG acts irresponsive until I restart it. The function is just a wrapper to set $1 and $2. I get the same behavior when I try just its SQL, no function. On 10/27/13, Adrian Klaver <adrian.klaver@xxxxxxxxx> wrote: > On 10/27/2013 02:48 PM, Robert James wrote: >> On 10/27/13, Adrian Klaver <adrian.klaver@xxxxxxxxx> wrote: > > >>>> Is there another problem here? Perhaps something to do with >>>> triggerring autovacuum? >>> >>> Is there a FK relationship involved? >>> >>> Could we see the schema for another_table? >> >> >> 1. No FK >> 2. I removed the indexes from the table >> TRUNCATE takes only 40 ms, but the INSERT still takes 10s! > > So how many records are we talking about? > > Also complete this sentence :) > > INSERT INTO > another_table SELECT ... > > In other words what is the SELECT statement for the INSERT? > > Also, you mentioned the above was in a function. What is the function > body and how is it being called? > >> 3. ALTER TABLE another_table SET (autovacuum_enabled = true, >> toast.autovacuum_enabled = true); didn't seem to make a difference >> >> 4. Here's the schema: >> >> >> CREATE TABLE another_table >> ( >> id serial NOT NULL, >> eventtime timestamp without time zone NOT NULL, >> reporter character varying NOT NULL, >> loc character varying NOT NULL, >> city character varying NOT NULL, >> stanza character varying, >> purdue character varying, >> CONSTRAINT segment_pkey PRIMARY KEY (id) >> ) >> >> > > > -- > Adrian Klaver > adrian.klaver@xxxxxxxxx > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
