Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 229BC4765BF for ; Thu, 9 May 2002 14:50:44 -0400 (EDT) Received: from thirstydog.dsl.net (thirstydog.dsl.net [65.84.81.4]) by postgresql.org (Postfix) with ESMTP id 73A804764CC for ; Thu, 9 May 2002 14:45:19 -0400 (EDT) Received: from 65-85-235-194.client.dsl.net (65-85-235-194.client.dsl.net [65.85.235.194]) by thirstydog.dsl.net (Postfix) with ESMTP id 5106A191AA4 for ; Thu, 9 May 2002 14:45:18 -0400 (EDT) Date: Thu, 9 May 2002 14:45:15 -0400 (EDT) From: James Orr X-Sender: jorr@database.lrg.office To: pgsql-sql@postgresql.org Subject: Help with indexes Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200205/78 X-Sequence-Number: 7714 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