Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 63BFA476301 for ; Fri, 10 May 2002 07:32:27 -0400 (EDT) Received: from mail-relay.trade-india.com (mail-relay.trade-india.com [203.196.129.235]) by postgresql.org (Postfix) with SMTP id 71EDA475A98 for ; Fri, 10 May 2002 07:32:22 -0400 (EDT) Received: (qmail 11417 invoked from network); 10 May 2002 11:25:11 -0000 Received: from system29.trade-india-local.com (HELO server) (192.168.0.29) by ptil-236-129-del.primus-india.net with SMTP; 10 May 2002 11:25:11 -0000 Content-Type: text/plain; charset="iso-8859-1" From: "Rajesh Kumar Mallah." Organization: Infocom Network Limited. To: James Orr Subject: Re: Help with indexes Date: Fri, 10 May 2002 17:01:48 +0530 X-Mailer: KMail [version 1.4] References: In-Reply-To: Cc: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200205101701.48837.mallah@trade-india.com> X-Archive-Number: 200205/90 X-Sequence-Number: 7726 Hi , I recently upgraded from 7.1.3 to 7.2.1 and performance improvements are immense , mainly (i feel) becoz of better index utilization by the=20 planner. i strongly feel its worth taking the trouble of upgrading... regds mallah. On Friday 10 May 2002 12:33 am, James Orr wrote: > Never mind. I got it running faster. > > On Thu, 9 May 2002, James Orr wrote: > > Hi, > > > > I need some help with this query, which is currently taking minutes to > > run (it needs to be seconds!). > > > > SELECT > > UPPER(teams.team_name), > > players.first_name, > > players.last_name, > > NULL, > > teams.id, > > brackets.court, > > teams.team_number > > FROM > > teams JOIN > > players ON (teams.id =3D players.team) JOIN > > brackets ON (teams.id =3D brackets.team) > > WHERE > > players.player_number =3D 1 AND > > teams.complete AND > > brackets.court =3D 580 > > ORDER BY > > brackets.game_order, > > teams.team_name > > ; > > > > When I run it with EXPLAIN I get ... > > > > > > Sort (cost=3D3.15..3.15 rows=3D1 width=3D60) > > -> Nested Loop (cost=3D0.00..3.14 rows=3D1 width=3D60) > > -> Nested Loop (cost=3D0.00..2.12 rows=3D1 width=3D48) > > -> Seq Scan on players (cost=3D0.00..1.05 rows=3D1 widt= h=3D28) > > -> Seq Scan on teams (cost=3D0.00..1.04 rows=3D2 width= =3D20) > > -> Seq Scan on brackets (cost=3D0.00..1.01 rows=3D1 width=3D1= 2) > > > > Now the indexes I have are: > > On table players - unique btree on id > > - unique btree on team, player_number > > On table teams - unique btree on id > > On table brackets - btree on court > > - btree on team > > - unique btree on court, team > > > > This is on version 7.1.3. Would upgrading to 7.2 make it faster? > > > > - James > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that your > > message can get through to the mailing list cleanly > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html --=20 Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.