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@gmail.com> wrote:
> On 10/27/2013 02:48 PM, Robert James wrote:
>> On 10/27/13, Adrian Klaver <adrian.klaver@gmail.com> 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@gmail.com
>