Thread: on PostgreSQL performance optimization.

Hi,

 For a month I was working on a NS-proxy parser that moved all necessary
information into a postgreSQL relation. The problem is that this app is
run every midnight so supposed to be process the information relatively
fast. It isn't. Today's proxy log is 3041705 bytes and the app is still
working on it.
 I'd like to know how to optimize the performance of it. The app does not
consume too much CPU time, while postgreSQL does. How could I find out if
what is suboptimal - the scheme of relations or the SQL queries I wrote,
etc.

            Thanks in advance.

--------------------------------------------------------------------------------
Sebestyén Zoltán <szoli@netvisor.hu>    I'm believing that the Holy Spirit is
                    gonna allow the hand, and the foot, and
MAKE INSTALL NOT WAR            the mouth, just to begin to speak, and
                                        to minister, and to heal coordinated by
                    the head.

I use UNIX because reboots are for hardware upgrades.

            -- Eagerly waiting for FreeBSD 2.2.8 --

Memphisto wrote:
>
> Hi,
>
>  For a month I was working on a NS-proxy parser that moved all necessary
> information into a postgreSQL relation. The problem is that this app is
> run every midnight so supposed to be process the information relatively
> fast. It isn't. Today's proxy log is 3041705 bytes and the app is still
> working on it.
>  I'd like to know how to optimize the performance of it. The app does not
> consume too much CPU time, while postgreSQL does. How could I find out if
> what is suboptimal - the scheme of relations or the SQL queries I wrote,
> etc.

First, do you use BEGIN/END to run many queries in one transaction?
Two fsync per query (without BEGIN/END) is great overhead...

And, as usual, man vacuum -:)

Vadim