On 10/28/2013 12:50 PM, Robert James wrote:
It could be > 1 Million rows.
Well that would be a reason.
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.
Not surprising there is less overhead for a SELECT than an INSERT.
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.
Below you say it takes 10s.
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
--
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
