pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Help with indexes
4+ messages / 3 participants
[nested] [flat]

* Help with indexes
@ 2002-05-09 18:45  James Orr <james@lrgmail.com>
  0 siblings, 1 reply; 4+ messages in thread

From: James Orr @ 2002-05-09 18:45 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Help with indexes
@ 2002-05-09 19:03  James Orr <james@lrgmail.com>
  parent: James Orr <james@lrgmail.com>
  0 siblings, 2 replies; 4+ messages in thread

From: James Orr @ 2002-05-09 19:03 UTC (permalink / raw)
  To: James Orr <james@lrgmail.com>; +Cc: pgsql-sql

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
>




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Help with indexes
@ 2002-05-10 02:31  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: James Orr <james@lrgmail.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Christopher Kings-Lynne @ 2002-05-10 02:31 UTC (permalink / raw)
  To: James Orr <james@lrgmail.com>; +Cc: pgsql-sql

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
>




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Help with indexes
@ 2002-05-10 11:31  Rajesh Kumar Mallah. <mallah@trade-india.com>
  parent: James Orr <james@lrgmail.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Rajesh Kumar Mallah. @ 2002-05-10 11:31 UTC (permalink / raw)
  To: James Orr <james@lrgmail.com>; +Cc: pgsql-sql

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.





^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2002-05-10 11:31 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-05-09 18:45 Help with indexes James Orr <james@lrgmail.com>
2002-05-09 19:03 ` James Orr <james@lrgmail.com>
2002-05-10 02:31   ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-05-10 11:31   ` Rajesh Kumar Mallah. <mallah@trade-india.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox