From: Rajesh Kumar Mallah. <mallah@trade-india.com>
To: James Orr <james@lrgmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Help with indexes
Date: Fri, 10 May 2002 17:01:48 +0530
Message-ID: <200205101701.48837.mallah@trade-india.com> (raw)
In-Reply-To: <Pine.LNX.4.33.0205091503190.25064-100000@localhost.localdomain>
References: <Pine.LNX.4.33.0205091503190.25064-100000@localhost.localdomain>
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
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 = players.team) JOIN
> > brackets ON (teams.id = brackets.team)
> > WHERE
> > players.player_number = 1 AND
> > teams.complete AND
> > brackets.court = 580
> > ORDER BY
> > brackets.game_order,
> > teams.team_name
> > ;
> >
> > When I run it with EXPLAIN I get ...
> >
> >
> > Sort (cost=3.15..3.15 rows=1 width=60)
> > -> Nested Loop (cost=0.00..3.14 rows=1 width=60)
> > -> Nested Loop (cost=0.00..2.12 rows=1 width=48)
> > -> Seq Scan on players (cost=0.00..1.05 rows=1 width=28)
> > -> Seq Scan on teams (cost=0.00..1.04 rows=2 width=20)
> > -> Seq Scan on brackets (cost=0.00..1.01 rows=1 width=12)
> >
> > 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
--
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mallah@trade-india.com, james@lrgmail.com
Subject: Re: Help with indexes
In-Reply-To: <200205101701.48837.mallah@trade-india.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox