Postgres Pro
Downloads
Home   >   mailing lists

Thread: Re: More PostgreSQL stuff

Re: More PostgreSQL stuff

From
"Oliver Elphick"
Date:
Copied to PostgreSQL lists, in the hope of comments from the experts...

Martin Schulze wrote:
  >Is there a way to speed up postgres?  I'm converting one of my
  >major apps from mSQL to PostgreSQL and PostgreSQL is at least three
  >times slower.  That's horrible.  With this slowlyness I cannot
  >install PostgreSQL in the office but only at home.
  >
  >So, is there a way to speed it up?  I have turned off debugging
  >since I hoped that it was the reason for the slowliness but
  >apparently it isn't.

It depends what you are doing: every update or insert is a separate
transaction, unless you declare transactions yourself.  So use
BEGIN TRANSACTION ... COMMIT ... END TRANSACTION to enclose related
updates and you should get a speed improvement.

If you are loading a lot of items, COPY is much faster than
successive INSERTs.

Consider whether to disable fsync; balance the speed improvement against the
slightly increased risk of corrupting your database in the event of a
system crash.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
               PGP key from public servers; key ID 32B8FAA1
                 ========================================
     "Set your affection on things above, not on things on
      the earth."          Colossians 3:2

Re: More PostgreSQL stuff

From
Martin Schulze
Date:
Oliver Elphick wrote:
> Copied to PostgreSQL lists, in the hope of comments from the experts...
>
> Martin Schulze wrote:
>   >Is there a way to speed up postgres?  I'm converting one of my
>   >major apps from mSQL to PostgreSQL and PostgreSQL is at least three
>   >times slower.  That's horrible.  With this slowlyness I cannot
>   >install PostgreSQL in the office but only at home.
>   >
>   >So, is there a way to speed it up?  I have turned off debugging
>   >since I hoped that it was the reason for the slowliness but
>   >apparently it isn't.
>
> It depends what you are doing: every update or insert is a separate
> transaction, unless you declare transactions yourself.  So use
> BEGIN TRANSACTION ... COMMIT ... END TRANSACTION to enclose related
> updates and you should get a speed improvement.
>
> If you are loading a lot of items, COPY is much faster than
> successive INSERTs.
>
> Consider whether to disable fsync; balance the speed improvement against the
> slightly increased risk of corrupting your database in the event of a
> system crash.

I'm making massive use of SELECT statements.  Insert/update is only rare.
Thanks for the hint wrt insert/update.  There is an INDEX on the main
select field already.

Regards,

    Joey

--
A mathematician is a machine for converting coffee into theorems.

Re: [GENERAL] Re: More PostgreSQL stuff

From
"Marc G. Fournier"
Date:
On Fri, 2 Oct 1998, Oliver Elphick wrote:

> Copied to PostgreSQL lists, in the hope of comments from the experts...
>
> Martin Schulze wrote:
>   >Is there a way to speed up postgres?  I'm converting one of my
>   >major apps from mSQL to PostgreSQL and PostgreSQL is at least three
>   >times slower.  That's horrible.  With this slowlyness I cannot
>   >install PostgreSQL in the office but only at home.
>   >
>   >So, is there a way to speed it up?  I have turned off debugging
>   >since I hoped that it was the reason for the slowliness but
>   >apparently it isn't.

    What version of PostgreSQL is being used?  Each one has gotten
progressively more efficient/faster.  Also, check out the -B and -S
options...one allows you to increase the SHM_* Buffers used, so that more
'data' gets cached to RAM, and the other increaess the amount of RAM used
for doing sort functions (ORDER BY and GROUP BY)...

    Also, use the 'EXPLAIN' function to determine how the query is
being performed...in particular, are there parts that creating an index
would help improve speed and performance, but you don't have an index
created?  I hit this one once, where I *thought* I had an index created on
one of the fields used in the query, but turned out I didn't.  Performance
difference with it added was dramatic...


 >
> It depends what you are doing: every update or insert is a separate
> transaction, unless you declare transactions yourself.  So use
> BEGIN TRANSACTION ... COMMIT ... END TRANSACTION to enclose related
> updates and you should get a speed improvement.
>
> If you are loading a lot of items, COPY is much faster than
> successive INSERTs.
>
> Consider whether to disable fsync; balance the speed improvement against the
> slightly increased risk of corrupting your database in the event of a
> system crash.
>
> --
> Oliver Elphick                                Oliver.Elphick@lfix.co.uk
> Isle of Wight                              http://www.lfix.co.uk/oliver
>                PGP key from public servers; key ID 32B8FAA1
>                  ========================================
>      "Set your affection on things above, not on things on
>       the earth."          Colossians 3:2
>
>
>

Marc G. Fournier                               scrappy@hub.org
Systems Administrator @ hub.org
scrappy@{postgresql|isc}.org                       ICQ#7615664