Re: Update Performance from 6.5.0 to 6.5.3 to 7.0

* Bryan White <bryan@arcamax.com> [000526 13:18] wrote:
> > Please describe your customer table better.
>
> CREATE TABLE "customer" (
>     "custid" int4 NOT NULL,
>     "lname" text DEFAULT '',
>     "fname" text DEFAULT '',
>     "email" text,
>     "offersubscribed" character DEFAULT '1',
>     "addr1" text DEFAULT '',
>     "addr2" text DEFAULT '',
>     "city" text DEFAULT '',
>     "state" text DEFAULT '',
>     "zip" text DEFAULT '',
>     "country" text DEFAULT '',
>     "phone" text DEFAULT '',
>     "fax" text DEFAULT '',
>     "firstcontactdate" date DEFAULT date(now()),
>     "note" text DEFAULT '',
>     "deliverable" character DEFAULT '1',
>     "mastersubscribed" character DEFAULT '1',
>     "url" text DEFAULT '',
>     "company" text DEFAULT '',
>     "title" text DEFAULT '',
>     "poregdate" date,
>     "bouncecount" int4,
>     "bouncedate" date
> );
> CREATE  INDEX "icusln" on "customer" using btree ( "lname" "text_ops" );
> CREATE UNIQUE INDEX "icusem2" on "customer" using btree ( "email"
> "text_ops" );
> CREATE  INDEX "icusph" on "customer" using btree ( "phone" "text_ops" );
> CREATE UNIQUE INDEX "icusid" on "customer" using btree ( "custid"
> "int4_ops" );
>
> > One thing I found was that postgresql (and just about any other
> > database) is excrutiatingly slow on update/insert if you made too
> > many indecies on the table being updated.
> >
> > how many indecies do you have on this table?
>
> 4, I could probably get by with just 2 If I had to.  I will give it a try.

yes! this should fix it for you.

>
> > you may want to try a combined index on both bouncedate and email.
>
> Why, Email is a unique index and the explain says it is using it.

yah, don't do that.

--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."