Thread: INSERTs slow

INSERTs slow

From

Dominique ROUSSEAU

Date:

26 March 2002, 07:07:53

Hi,


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

Re: INSERTs slow

From

"PGMailList"

Date:

26 March 2002, 11:31:30

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


> Hi,
>
>
> 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
>

Re: INSERTs slow

From

Dominique ROUSSEAU

Date:

26 March 2002, 11:40:39

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

Re: INSERTs slow

From

"PGMailList"

Date:

26 March 2002, 11:48:52

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)---------------------------
> > > TIP 6: Have you searched our list archives?
> > >
> > > http://archives.postgresql.org
> > >
>
> --
> Dominique Rousseau <d.rousseau@nnx.com>
> Neuronnexion - http://www.neuronnexion.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

Re: INSERTs slow

From

"PGMailList"

Date:

26 March 2002, 11:53:03

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)---------------------------
> > > TIP 6: Have you searched our list archives?
> > >
> > > http://archives.postgresql.org
> > >
>
> --
> Dominique Rousseau <d.rousseau@nnx.com>
> Neuronnexion - http://www.neuronnexion.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

Re: INSERTs slow

From

Dominique ROUSSEAU

Date:

27 March 2002, 06:45:21

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