X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 9466AD1B508 for ; Mon, 13 Oct 2003 20:53:34 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 34808-03 for ; Mon, 13 Oct 2003 17:53:03 -0300 (ADT) Received: from lakemtao04.cox.net (lakemtao04.cox.net [68.1.17.241]) by svr1.postgresql.org (Postfix) with ESMTP id 4CA34D1B50D for ; Mon, 13 Oct 2003 17:53:03 -0300 (ADT) Received: from lhosts ([68.11.66.83]) by lakemtao04.cox.net (InterMail vM.5.01.06.05 201-253-122-130-105-20030824) with ESMTP id <20031013205305.IPZT5790.lakemtao04.cox.net@lhosts> for ; Mon, 13 Oct 2003 16:53:05 -0400 Subject: Re: Any issues with my tuning... From: Ron Johnson To: PgSQL Performance ML In-Reply-To: <039801c391c2$4b83d600$6501a8c0@griffiths2> References: <039801c391c2$4b83d600$6501a8c0@griffiths2> Content-Type: text/plain Message-Id: <1066078384.12390.26.camel@haggis> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.4 Date: Mon, 13 Oct 2003 15:53:04 -0500 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/433 X-Sequence-Number: 4181 On Mon, 2003-10-13 at 14:43, David Griffiths wrote: > I've been having performance issues with Postgres (sequential scans vs > index scans in an update statement). I've read that optimizer will > change it's plan based on the resources it thinks are available. In > addition, I've read alot of conflicting info on various parameters, so > I'd like to sort those out as well. > > Here's the query I've been having problems with: > > UPDATE user_account SET last_name='abc' > FROM commercial_entity ce, commercial_service cs > WHERE user_account.user_account_id = ce.user_account_id AND > ce.commercial_entity_id=cs.commercial_entity_id; > > or > > UPDATE user_account SET last_name = 'abc' > WHERE EXISTS (SELECT 1 FROM commercial_entity ce, commercial_service > cs > WHERE user_account.user_account_id = ce.user_account_id AND > ce.commercial_entity_id = cs.commercial_entity_id); > > Both are about the same. > > All columns are indexed; all column-types are the same > (numeric(10,0)). A vacuum analyze was run just before the last attempt > at running the above statement. First thing is to change ce.user_account_id, ce.commercial_entity_id, and cs.commercial_entity_id from numeric(10,0) to INTEGER. PG uses them much more efficiently than it does NUMERIC, since it's a simple scalar type. -- ----------------------------------------------------------------- Ron Johnson, Jr. ron.l.johnson@cox.net Jefferson, LA USA LUKE: Is Perl better than Python? YODA: No... no... no. Quicker, easier, more seductive. LUKE: But how will I know why Python is better than Perl? YODA: You will know. When your code you try to read six months from now.