Re: [GENERAL] Re: More PostgreSQL stuff

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(dot)Elphick(at)lfix(dot)co(dot)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(at)hub(dot)org
Systems Administrator @ hub.org
scrappy(at){postgresql|isc}.org ICQ#7615664