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@postgresql.org so that your
> message can get through to the mailing list cleanly
>