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

Search Discussions

  • Michael Fuhr at Sep 13, 2006 at 3:29 am
    [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
  • Matthieu Guamis at Sep 13, 2006 at 10:15 am
    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.
  • Matthieu Guamis at Sep 13, 2006 at 1:12 pm
    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.
  • Michael Fuhr at Sep 13, 2006 at 1:35 pm

    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
  • Matthieu Guamis at Sep 13, 2006 at 1:51 pm

    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.
  • Brandon Aiken at Sep 13, 2006 at 1:35 pm
    Why drop and recreate the table? Why not TRUNCATE it?

    --
    Brandon Aiken
    CS/IT Systems Engineer

    -----Original Message-----
    From: 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedSep 12, '06 at 12:42p
activeSep 13, '06 at 1:51p
posts7
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase