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

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com