Re: Help with indexes

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
>