Re: 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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>