INSERTs slow

1) Do you have constraints on the index ie Unique or Primary Key?

2) Do you have any triggers or rules on the table.

3) How often do you do a VACUUM ANALYZE

All these will influence the speed

http://www.pgexplorer.com

GUI Frontend for PostgreSQL

----- Original Message -----

From: "Dominique ROUSSEAU" <d.rousseau@nnx.com>
To: <pgsql-general@postgresql.org>
Sent: Tuesday, March 26, 2002 11:07 AM
Subject: [GENERAL] INSERTs slow

Le Tue, Mar 26, 2002 at 03:30:58PM +0200, PGMailList [pgmail@pgexplorer.com] a ï¿œcrit:

1) Do you have constraints on the index ie Unique or Primary Key?

they are just :
CREATE INDEX idx1 ON table(field1);

2) Do you have any triggers or rules on the table.

3) How often do you do a VACUUM ANALYZE

that's what I started doing, but doesn't change much :/
once a day
(there are thousands of INSERTs each day)

Dom

----- Original Message -----

From: "Dominique ROUSSEAU" <d.rousseau@nnx.com>
To: <pgsql-general@postgresql.org>
Sent: Tuesday, March 26, 2002 11:07 AM
Subject: [GENERAL] INSERTs slow

I have a table with around 1.5 millions entries, and INSERTs are very
slowwwww.
This table has 3 indices on "char(32)" fields.

Without indices, the INSERTs are fluent, with indices, it crawls :/

Any idea ?

As a sidenote, it was going rather well when the table contained only 1
million entries.

Dom

--
Dominique Rousseau <d.rousseau@nnx.com>
Neuronnexion - http://www.neuronnexion.com

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

--
Dominique Rousseau <d.rousseau@nnx.com>
Neuronnexion - http://www.neuronnexion.com

Have you tried dropping and recreating the index.

or

REINDEX { TABLE | DATABASE | INDEX } name [ FORCE ]

Maybe that index of yours is not so kosher

http://www.pgexplorer.com

----- Original Message -----
From: "Dominique ROUSSEAU" <d.rousseau@nnx.com>
To: <pgsql-general@postgresql.org>
Sent: Tuesday, March 26, 2002 3:40 PM
Subject: Re: [GENERAL] INSERTs slow

Le Tue, Mar 26, 2002 at 03:30:58PM +0200, PGMailList

[pgmail@pgexplorer.com] a ï¿œcrit:

1) Do you have constraints on the index ie Unique or Primary Key?

they are just :
CREATE INDEX idx1 ON table(field1);

2) Do you have any triggers or rules on the table.

3) How often do you do a VACUUM ANALYZE

that's what I started doing, but doesn't change much :/
once a day
(there are thousands of INSERTs each day)

Dom

----- Original Message -----

From: "Dominique ROUSSEAU" <d.rousseau@nnx.com>
To: <pgsql-general@postgresql.org>
Sent: Tuesday, March 26, 2002 11:07 AM
Subject: [GENERAL] INSERTs slow

I have a table with around 1.5 millions entries, and INSERTs are very
slowwwww.
This table has 3 indices on "char(32)" fields.

Without indices, the INSERTs are fluent, with indices, it crawls :/

Any idea ?

As a sidenote, it was going rather well when the table contained only

1

million entries.

Dom

--
Dominique Rousseau <d.rousseau@nnx.com>
Neuronnexion - http://www.neuronnexion.com

---------------------------(end of

broadcast)---------------------------

You can also start the server with
-F
[Disables fsync calls for performance improvement, at the risk of data
corruption in event of a system crash. Read the detailed documentation
before using this! ]

But your server HAS TO BE ON A VERY GOOD UPS...

http://www.pgexplorer.com

----- Original Message -----
From: "Dominique ROUSSEAU" <d.rousseau@nnx.com>
To: <pgsql-general@postgresql.org>
Sent: Tuesday, March 26, 2002 3:40 PM
Subject: Re: [GENERAL] INSERTs slow

Le Tue, Mar 26, 2002 at 03:30:58PM +0200, PGMailList

[pgmail@pgexplorer.com] a ï¿œcrit:

1) Do you have constraints on the index ie Unique or Primary Key?

they are just :
CREATE INDEX idx1 ON table(field1);

2) Do you have any triggers or rules on the table.

3) How often do you do a VACUUM ANALYZE

that's what I started doing, but doesn't change much :/
once a day
(there are thousands of INSERTs each day)

Dom

----- Original Message -----

From: "Dominique ROUSSEAU" <d.rousseau@nnx.com>
To: <pgsql-general@postgresql.org>
Sent: Tuesday, March 26, 2002 11:07 AM
Subject: [GENERAL] INSERTs slow

I have a table with around 1.5 millions entries, and INSERTs are very
slowwwww.
This table has 3 indices on "char(32)" fields.

Without indices, the INSERTs are fluent, with indices, it crawls :/

Any idea ?

As a sidenote, it was going rather well when the table contained only

1

million entries.

Dom

--
Dominique Rousseau <d.rousseau@nnx.com>
Neuronnexion - http://www.neuronnexion.com

---------------------------(end of

broadcast)---------------------------

Le Wed, Mar 27, 2002 at 07:22:01AM +0200, Dirk Broodryk [dirkb%ferreiragroup.co.za@ferreiragroup.co.za] a ï¿œcrit:

Just a notion.. try varchar . .read someware that it's faster accessing it
than normal char.. I have a table with 8 mill records, and find it not to
bad..

you mean that indices on VARCHAR() would be faster than indices on
CHAR() ??

Dom

--
Dominique Rousseau <d.rousseau@nnx.com>
Neuronnexion - http://www.neuronnexion.com