Re: AUTOCOMMIT didn't work... - Mailing list pgsql-general

From Oliver Elphick
Subject Re: AUTOCOMMIT didn't work...
Date
Msg-id 1069195351.16544.1015.camel@linda.lfix.co.uk
Whole thread Raw
In response to AUTOCOMMIT didn't work...  (Lynn.Tilby@asu.edu)
List pgsql-general
On Mon, 2003-11-17 at 20:39, Lynn.Tilby@asu.edu wrote:
> Hi,
>
> Please see the history below...
>
> Does AUTOCOMMIT as used here functionally replace VACUUM ANALYZE?

No; they are completely separate concepts.  Autocommit treats every
statement as a separate transaction; as you have found, this will slow
things down.

VACUUM ANALYZE reads through all rows in a table or all the tables in a
database to set up some statistics to guide the planner.  It needs to be
used regularly or the planner will start to produce invalid results and
queries will take a long time.  That is not the same reason for taking a
long time as making every statement a separate transaction.

> If I turn on AUTOCOMMIT is every transaction commited as it is
> executed?  If this is the case the query will take about twice as
> long... I have actually tested this scenario.
>
> Using AUTOCOMMIT as suggested didn't solve the problem.

I think someone said turn it on when he meant turn it off?  It is always
on by default.

To put many statements into one transaction, enclose them all in BEGIN;
and END;  They should then run a lot faster.


Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "A Song for the sabbath day. It is a good thing to
      give thanks unto the LORD, and to sing praises unto
      thy name, O most High."   Psalms 92:1


pgsql-general by date:

Previous
From: Oliver Elphick
Date:
Subject: Re: unix_socket_directory not fully recognized by pg_ctl?
Next
From: "scott.marlowe"
Date:
Subject: Re: Point-in-time data recovery - v.7.4