Skip site navigation (1) Skip section navigation (2)

Re: [RESOLVED] INSERT does not finish except if it is carried

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Matthieu Guamis <matthieu(dot)guamis(at)axege(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [RESOLVED] INSERT does not finish except if it is carried
Date: 2006-09-13 13:35:14
Message-ID: 20060913133514.GA88599@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-novice
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

In response to

Responses

pgsql-novice by date

Next:From: Matthieu GuamisDate: 2006-09-13 13:50:58
Subject: Re: [RESOLVED] INSERT does not finish except if it is carried
Previous:From: Brandon AikenDate: 2006-09-13 13:34:58
Subject: Re: INSERT does not finish except if it is carried out a

pgsql-general by date

Next:From: didier tantiDate: 2006-09-13 13:46:40
Subject: Load "myLib.so" error cannot load obj file...
Previous:From: Brandon AikenDate: 2006-09-13 13:34:58
Subject: Re: INSERT does not finish except if it is carried out a

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group