Re: Update Performance from 6.5.0 to 6.5.3 to 7.0

* Bryan White <bryan(at)arcamax(dot)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(at)wintelcom(dot)net|alfred(at)freebsd(dot)org]
"I have the heart of a child; I keep it in a jar on my desk."