Thread: Help with indexes
Hi, I need some help with this query, which is currently taking minutes to run (it needs to be seconds!). SELECTUPPER(teams.team_name),players.first_name,players.last_name,NULL,teams.id,brackets.court,teams.team_number FROMteams JOINplayers ON (teams.id = players.team) JOINbrackets ON (teams.id = brackets.team) WHEREplayers.player_number = 1 ANDteams.complete ANDbrackets.court = 580 ORDER BYbrackets.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.12rows=1 width=48) -> Seq Scan on players (cost=0.00..1.05 rows=1 width=28) -> SeqScan 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
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 >
Just make sure you've 'vacuum analyze'd your tables. Chris > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of James Orr > Sent: Friday, 10 May 2002 3:04 AM > To: James Orr > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] Help with indexes > > > 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 >
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.