Thread: INSERT does not finish except if it is carried out a few minutes after the creation of the table

hi all,

During the execution of the following requests, INSERT does not finish
except if it is carried out a few minutes after the
creation of the table. How to explain this latency time?

CREATE produces a table with the number of events of a product (id1)
for a customer (id2) having attribute “ABCD”.
INSERT adds a row for each product a client did not buy whereas others
of group "ABCD" did. That is done by selecting the
Cartesian product between the attributes id1 and id2 then removing
(EXCEPT) lines whose couple (id1, id2) is already in…

-----------------------------------------
drop table maTable;

create table maTable as (
select id1,id2,count(*)
from table1
where cle = 'ABCD'
group by id1, id2
order by id2,id1);

insert into maTable (select * from
((select a.id1 ,b.id2 ,0
from maTable a, maTable b
group by a.id1,b.id2
order by b.id2,a.id1)
EXCEPT
(select c.id1 ,c.id2 ,0
from maTable c
))as tt;
-----------------------------------------

DROP and CREATE do their job but INSERT does not finish if it is
carried out immediately after the CREATE. On the other hand

if it is carried out a few minutes (~5min) later then INSERT commits in
a few seconds.

Rq: If drop/create/insert is replaced by delete/insert/insert then it's
ok.
Finally the creation of a “Temporary” table leads to the same
problem.   

Thank you for your assistance,

Mat

[Please don't post HTML.]

On Tue, Sep 12, 2006 at 02:09:40PM +0200, Matthieu Guamis wrote:
> During the execution of the following requests, INSERT does not finish
> except if it is carried out a few minutes after the
> creation of the table. How to explain this latency time?
[...]
> insert into maTable (select * from
> ((select a.id1 ,b.id2 ,0
> from maTable a, maTable b
> group by a.id1,b.id2
> order by b.id2,a.id1)
> EXCEPT
> (select c.id1 ,c.id2 ,0
> from maTable c
> ))as tt;

This statement isn't syntactically correct; it has an unmatched
open parenthesis.  If I paste the statement into psql it appears
to hang, presumably because the parser thinks it's incomplete and
is waiting for more input.  Are you sure you've diagnosed the problem
correctly?  If so then please post a test case without errors so
others can attempt to duplicate the problem.

What version of PostgreSQL are you running and on what platform?
What client interface are you using?

> DROP and CREATE do their job but INSERT does not finish if it is
> carried out immediately after the CREATE. On the other hand
> if it is carried out a few minutes (~5min) later then INSERT commits
> in a few seconds.

A five-minute delay could hint at some possible causes, but first
let's find out whether syntax is the problem.

--
Michael Fuhr

Hello,

PostgreSQL 8.1 is running on Ubuntu 6.06 server edition.

Please trust me, when I use DELETE/INSERT/INSERT statements the job is
done in a few seconds whereas with DROP/CREATE AS /SELECT it takes
several minutes (to achieve the SELECT statement). But in this last
case, if I wait few minutes between  CREATE AS and SELECT then the
SELECT is done in a few seconds.

Sorry for previous syntax errors (I did not paste statements but wrote
them with simplified names for fields and tables... it may explain the
unmatched open parenthesis).

Could you tell me more about some possible causes of the delay?

Regards


Michael Fuhr a écrit :
> [Please don't post HTML.]
>
> On Tue, Sep 12, 2006 at 02:09:40PM +0200, Matthieu Guamis wrote:
>
>> During the execution of the following requests, INSERT does not finish
>> except if it is carried out a few minutes after the
>> creation of the table. How to explain this latency time?
>>
> [...]
>
>> insert into maTable (select * from
>> ((select a.id1 ,b.id2 ,0
>> from maTable a, maTable b
>> group by a.id1,b.id2
>> order by b.id2,a.id1)
>> EXCEPT
>> (select c.id1 ,c.id2 ,0
>> from maTable c
>> ))as tt;
>>
>
> This statement isn't syntactically correct; it has an unmatched
> open parenthesis.  If I paste the statement into psql it appears
> to hang, presumably because the parser thinks it's incomplete and
> is waiting for more input.  Are you sure you've diagnosed the problem
> correctly?  If so then please post a test case without errors so
> others can attempt to duplicate the problem.
>
> What version of PostgreSQL are you running and on what platform?
> What client interface are you using?
>
>
>> DROP and CREATE do their job but INSERT does not finish if it is
>> carried out immediately after the CREATE. On the other hand
>> if it is carried out a few minutes (~5min) later then INSERT commits
>> in a few seconds.
>>
>
> A five-minute delay could hint at some possible causes, but first
> let's find out whether syntax is the problem.
>
>

Hi all,

If I use "VACUUM ANALYSE maTable" after CREATE AS of the DROP/CREATE
AS/INSERT statements then INSERT commits in a few seconds.
Documentation says :"VACUUM ANALYZE: Updates statistics used by the
planner to determine the most efficient way to execute a query."

I don't understand really why it is necessary to use it but it works.

Mat



Matthieu Guamis a écrit :
> Hello,
>
> PostgreSQL 8.1 is running on Ubuntu 6.06 server edition.
>
> Please trust me, when I use DELETE/INSERT/INSERT statements the job is
> done in a few seconds whereas with DROP/CREATE AS /SELECT it takes
> several minutes (to achieve the SELECT statement). But in this last
> case, if I wait few minutes between  CREATE AS and SELECT then the
> SELECT is done in a few seconds.
>
> Sorry for previous syntax errors (I did not paste statements but wrote
> them with simplified names for fields and tables... it may explain the
> unmatched open parenthesis).
>
> Could you tell me more about some possible causes of the delay?
>
> Regards
>
>
> Michael Fuhr a écrit :
>> [Please don't post HTML.]
>>
>> On Tue, Sep 12, 2006 at 02:09:40PM +0200, Matthieu Guamis wrote:
>>
>>> During the execution of the following requests, INSERT does not finish
>>> except if it is carried out a few minutes after the
>>> creation of the table. How to explain this latency time?
>>>
>> [...]
>>
>>> insert into maTable (select * from
>>> ((select a.id1 ,b.id2 ,0
>>> from maTable a, maTable b
>>> group by a.id1,b.id2
>>> order by b.id2,a.id1)
>>> EXCEPT
>>> (select c.id1 ,c.id2 ,0
>>> from maTable c
>>> ))as tt;
>>>
>>
>> This statement isn't syntactically correct; it has an unmatched
>> open parenthesis.  If I paste the statement into psql it appears
>> to hang, presumably because the parser thinks it's incomplete and
>> is waiting for more input.  Are you sure you've diagnosed the problem
>> correctly?  If so then please post a test case without errors so
>> others can attempt to duplicate the problem.
>>
>> What version of PostgreSQL are you running and on what platform?
>> What client interface are you using?
>>
>>
>>> DROP and CREATE do their job but INSERT does not finish if it is
>>> carried out immediately after the CREATE. On the other hand
>>> if it is carried out a few minutes (~5min) later then INSERT commits
>>> in a few seconds.
>>>
>>
>> A five-minute delay could hint at some possible causes, but first
>> let's find out whether syntax is the problem.
>>
>>
>

Why drop and recreate the table?  Why not TRUNCATE it?

--
Brandon Aiken
CS/IT Systems Engineer

-----Original Message-----
From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Matthieu Guamis
Sent: Wednesday, September 13, 2006 6:15 AM
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] INSERT does not finish except if it is carried out a

Hello,

PostgreSQL 8.1 is running on Ubuntu 6.06 server edition.

Please trust me, when I use DELETE/INSERT/INSERT statements the job is
done in a few seconds whereas with DROP/CREATE AS /SELECT it takes
several minutes (to achieve the SELECT statement). But in this last
case, if I wait few minutes between  CREATE AS and SELECT then the
SELECT is done in a few seconds.

Sorry for previous syntax errors (I did not paste statements but wrote
them with simplified names for fields and tables... it may explain the
unmatched open parenthesis).

Could you tell me more about some possible causes of the delay?

Regards


Michael Fuhr a écrit :
> [Please don't post HTML.]
>
> On Tue, Sep 12, 2006 at 02:09:40PM +0200, Matthieu Guamis wrote:
>
>> During the execution of the following requests, INSERT does not finish
>> except if it is carried out a few minutes after the
>> creation of the table. How to explain this latency time?
>>
> [...]
>
>> insert into maTable (select * from
>> ((select a.id1 ,b.id2 ,0
>> from maTable a, maTable b
>> group by a.id1,b.id2
>> order by b.id2,a.id1)
>> EXCEPT
>> (select c.id1 ,c.id2 ,0
>> from maTable c
>> ))as tt;
>>
>
> This statement isn't syntactically correct; it has an unmatched
> open parenthesis.  If I paste the statement into psql it appears
> to hang, presumably because the parser thinks it's incomplete and
> is waiting for more input.  Are you sure you've diagnosed the problem
> correctly?  If so then please post a test case without errors so
> others can attempt to duplicate the problem.
>
> What version of PostgreSQL are you running and on what platform?
> What client interface are you using?
>
>
>> DROP and CREATE do their job but INSERT does not finish if it is
>> carried out immediately after the CREATE. On the other hand
>> if it is carried out a few minutes (~5min) later then INSERT commits
>> in a few seconds.
>>
>
> A five-minute delay could hint at some possible causes, but first
> let's find out whether syntax is the problem.
>
>

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org

On Wed, Sep 13, 2006 at 03:12:22PM +0200, Matthieu Guamis wrote:
> If I use "VACUUM ANALYSE maTable" after CREATE AS of the DROP/CREATE
> AS/INSERT statements then INSERT commits in a few seconds.
> Documentation says :"VACUUM ANALYZE: Updates statistics used by the
> planner to determine the most efficient way to execute a query."

Are you running autovacuum?  If so then that might explain why the
query runs faster after waiting a little while.  When you first
create the table the planner doesn't have good statistics about it
so it might use a sub-optimal query plan.  After autovacuum runs
and analyzes the table, the statistics are more accurate and the
planner uses a better plan.  When you delete rows rather than drop
and recreate the table, the planner can use statistics based on the
table's previous contents and choose a good plan right away.  You
could use EXPLAIN ANALYZE on the problematic SELECT statement to
see if this is what's happening.

--
Michael Fuhr

>
> Are you running autovacuum?
Yes I am, ("autovacuum = on" in postgres.conf).
> You could use EXPLAIN ANALYZE
I'll do it soon.

Thank you very much for the explanation.



Michael Fuhr a écrit :
> On Wed, Sep 13, 2006 at 03:12:22PM +0200, Matthieu Guamis wrote:
>
>> If I use "VACUUM ANALYSE maTable" after CREATE AS of the DROP/CREATE
>> AS/INSERT statements then INSERT commits in a few seconds.
>> Documentation says :"VACUUM ANALYZE: Updates statistics used by the
>> planner to determine the most efficient way to execute a query."
>>
>
> Are you running autovacuum?  If so then that might explain why the
> query runs faster after waiting a little while.  When you first
> create the table the planner doesn't have good statistics about it
> so it might use a sub-optimal query plan.  After autovacuum runs
> and analyzes the table, the statistics are more accurate and the
> planner uses a better plan.  When you delete rows rather than drop
> and recreate the table, the planner can use statistics based on the
> table's previous contents and choose a good plan right away.  You
> could use EXPLAIN ANALYZE on the problematic SELECT statement to
> see if this is what's happening.
>
>