Обсуждение: INSERT does not finish except if it is carried out a few minutes after the creation of the table
INSERT does not finish except if it is carried out a few minutes after the creation of the table
От
Matthieu Guamis
Дата:
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.
>
>