Re: Work table

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@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
>>
>


--
Adrian Klaver
adrian.klaver@gmail.com