pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedOptimization, etc
4+ messages / 4 participants
[nested] [flat]
* Optimization, etc
@ 2001-11-09 06:42 Jeff Sack <sackj@alum.rpi.edu>
0 siblings, 1 reply; 4+ messages in thread
From: Jeff Sack @ 2001-11-09 06:42 UTC (permalink / raw)
To: pgsql-sql
(apologies if this was sent twice)
Hello all. I have an academic project that I'm working on and, as I'm
relatively new to optimization techniques and database design, I'm
wondering if some of you can give me some pointers. Below is the schema
(to model baseball statistics), and I'm pretty much stuck with it at
this point. If anyone has any suggestions involving changing the
schema, I'd appreciate hearing them just for future reference.
There are several interesting queries that I've been constructing just
to get a feel for this schema, and given that some of these tables
contain 100,000-200,000 tuples, some queries are taking a good 5-10
seconds to execute. I'm just wondering if this is simply the fault of
my schema or are these queries poorly constructed? Here are some
queries I've been trying:
Here's a query for the top ten all time home run leaders:
select P.first_name, P.last_name, S.player_id, sum(B.hr) as hr,
(sum(B.h)::float / sum(B.ab)::float) ab
from statistics S, batting_stats B, players P
where S.id=B.id and S.player_id=P.id and B.ab>0
group by P.first_name, P.last_name, S.player_id
order by hr desc limit 10;
Select the 10 highest batting averages of all time where the batter had
600 or more at bats. Also gather the name, year, team, hits, at bats...
select (B.h::float / B.ab) as avg, B.h, B.ab, S.year, P.first_name,
P.last_name, T.city, T.name
from batting_stats B, statistics S, players P, Teams T
where B.ab > 600 and S.id=B.id and S.player_id=P.id and S.team_id=T.id
order by avg desc limit 10;
To find the names of the single season home run leaders, along with the
total number of home runs, the team name/city and the year:
select P.first_name, P.last_name, B.hr, T.name, T.city, S.year from
statistics S, batting_stats B, players P, teams T
where (S.id=B.id) and
(S.player_id=P.id) and
(B.hr>30) and
(T.id=S.team_id) limit 10;
You get the idea. These queries take a while. Is this just the way it
is or there things that can be done to optimize this?
One separate issue (the reason why the above examples are all about
batting statistics) I'm having is representing the innings pitched
statistic. The way it is often represented (and the way it is done in
this schema) is something like this 123.0 means exactly 123 innings
pitched, 123.1 means 123 1/3 innings, and 123.2 means 123 2/3 innings
pitched. I'm contemplating the best way to write a function that knows
how to sum these values accurately. Is this something that can be done
with PL/PGSQL or should I go straight to something like PLPERL?
Alternatively, I could research a way to represent fractions in the DB
and write a script to convert all values in this column. Any advice
here??
Thanks in advance for any thoughts, comments, suggestions.
-Jeff
create table leagues
(
-- create an integer id field for easier and efficient FK
referencing
id serial,
name varchar(50),
first_year integer not null
check (first_year > 1850 and first_year < 2010),
last_year integer
check (first_year > 1850 and first_year < 2010),
primary key(id),
constraint chronological_order
check (last_year >= first_year)
);
create table teams
(
id serial,
name varchar(50) not null,
city varchar(50),
first_year integer not null
check (first_year > 1850 and first_year < 2010),
last_year integer
check (first_year > 1850 and first_year < 2010),
alt_id integer,
league_id integer not null,
primary key(id),
foreign key(alt_id) references teams(id),
foreign key(league_id) references leagues(id)
on delete cascade,
constraint chronological_order
check (last_year >= first_year)
);
create table players
(
id serial,
first_name varchar(30),
last_name varchar(30) not null,
bats char(1) check (bats in ('L','R','S')),
throws char(1) check (throws in ('L','R')),
dob date,
primary key(id)
);
create table statistics
(
id serial,
year integer not null,
g integer,
player_id integer not null,
team_id integer not null,
foreign key(player_id) references players(id)
on delete cascade,
foreign key(team_id) references teams(id)
on delete cascade,
primary key(id)
);
create table managing_stats
(
id integer not null,
w integer,
l integer,
primary key(id),
foreign key(id) references statistics(id)
on delete cascade
);
create table fielding_stats
(
id integer not null,
pos char(5),
po integer,
a integer,
e integer,
dp integer,
primary key(id),
foreign key(id) references statistics(id)
on delete cascade
);
create table batting_stats
(
id integer not null,
ab integer,
r integer,
h integer,
doubles integer,
triples integer,
hr integer,
rbi integer,
sb integer,
cs integer,
bb integer,
so integer,
sh integer,
sf integer,
ibb integer,
hbp integer,
primary key(id),
foreign key(id) references statistics(id)
on delete cascade
);
create table pitching_stats
(
id integer not null,
w integer,
l integer,
gs integer,
cg integer,
sh integer,
sv integer,
ip numeric(5,1),
h integer,
er integer,
hr integer,
bb integer,
so integer,
primary key(id),
foreign key(id) references statistics(id)
on delete cascade
);
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Optimization, etc
@ 2001-11-09 15:57 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Jeff Sack <sackj@alum.rpi.edu>
0 siblings, 2 replies; 4+ messages in thread
From: Stephan Szabo @ 2001-11-09 15:57 UTC (permalink / raw)
To: Jeff Sack <sackj@alum.rpi.edu>; +Cc: pgsql-sql
On Fri, 9 Nov 2001, Jeff Sack wrote:
> To find the names of the single season home run leaders, along with the
> total number of home runs, the team name/city and the year:
>
> select P.first_name, P.last_name, B.hr, T.name, T.city, S.year from
> statistics S, batting_stats B, players P, teams T
> where (S.id=B.id) and
> (S.player_id=P.id) and
> (B.hr>30) and
> (T.id=S.team_id) limit 10;
>
> You get the idea. These queries take a while. Is this just the way it
> is or there things that can be done to optimize this?
As a starting point, have you run vacuum analyze and what does
explain show for the query. Also, do you have indexes on fields that
you're limiting on (like hr, etc...).
> One separate issue (the reason why the above examples are all about
> batting statistics) I'm having is representing the innings pitched
> statistic. The way it is often represented (and the way it is done in
> this schema) is something like this 123.0 means exactly 123 innings
> pitched, 123.1 means 123 1/3 innings, and 123.2 means 123 2/3 innings
> pitched. I'm contemplating the best way to write a function that knows
> how to sum these values accurately. Is this something that can be done
> with PL/PGSQL or should I go straight to something like PLPERL?
> Alternatively, I could research a way to represent fractions in the DB
> and write a script to convert all values in this column. Any advice
> here??
You'd probably be best off doing the conversion at insert time into an
additional field assuming that inserts are much less likely that
selects on your data.
(col-floor(col)*(10/3::numeric) seems to get back an appropriate value
but is probably reasonably expensive.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Optimization, etc
@ 2001-11-09 18:22 Jason Earl <jason.earl@simplot.com>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
1 sibling, 0 replies; 4+ messages in thread
From: Jason Earl @ 2001-11-09 18:22 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Jeff Sack <sackj@alum.rpi.edu>; pgsql-sql
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> On Fri, 9 Nov 2001, Jeff Sack wrote:
>
> > To find the names of the single season home run leaders, along with the
> > total number of home runs, the team name/city and the year:
> >
> > select P.first_name, P.last_name, B.hr, T.name, T.city, S.year from
> > statistics S, batting_stats B, players P, teams T
> > where (S.id=B.id) and
> > (S.player_id=P.id) and
> > (B.hr>30) and
> > (T.id=S.team_id) limit 10;
> >
> > You get the idea. These queries take a while. Is this just the way it
> > is or there things that can be done to optimize this?
>
> As a starting point, have you run vacuum analyze and what does
> explain show for the query. Also, do you have indexes on fields that
> you're limiting on (like hr, etc...).
>
> > One separate issue (the reason why the above examples are all about
> > batting statistics) I'm having is representing the innings pitched
> > statistic. The way it is often represented (and the way it is done in
> > this schema) is something like this 123.0 means exactly 123 innings
> > pitched, 123.1 means 123 1/3 innings, and 123.2 means 123 2/3 innings
> > pitched. I'm contemplating the best way to write a function that knows
> > how to sum these values accurately. Is this something that can be done
> > with PL/PGSQL or should I go straight to something like PLPERL?
> > Alternatively, I could research a way to represent fractions in the DB
> > and write a script to convert all values in this column. Any advice
> > here??
>
> You'd probably be best off doing the conversion at insert time into an
> additional field assuming that inserts are much less likely that
> selects on your data.
>
> (col-floor(col)*(10/3::numeric) seems to get back an appropriate value
> but is probably reasonably expensive.
Another thought would be to simply store this as an integer that is
the number of thirds of an inning that the pitcher pitched. In other
words your 123.1 would be stored as 370. That would allow you a very
easy way to manipulate these values mathematically, and it would be
easy to write a simple function to format these values so that your
uses would see the 123.1 that they expect.
Heck something like:
SELECT (370 / 3)::text || '.' || (370 % 3)::text;
would do exactly what you want.
Throw in some indexes like Josh and Stephen suggest and you'll be
cooking with gasoline.
You also might want to take a look at Bruce Momjian's article about
performance tuning:
http://www2.linuxjournal.com/lj-issues/issue88/4791.html
Jason
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Optimization, etc
@ 2001-11-17 13:30 Masaru Sugawara <rk73@echna.ne.jp>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
1 sibling, 0 replies; 4+ messages in thread
From: Masaru Sugawara @ 2001-11-17 13:30 UTC (permalink / raw)
To: pgsql-sql
On Fri, 9 Nov 2001 07:57:41 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:
> > On Fri, 9 Nov 2001, Jeff Sack wrote:
> >
> > One separate issue (the reason why the above examples are all about
> > batting statistics) I'm having is representing the innings pitched
> > statistic. The way it is often represented (and the way it is done in
> > this schema) is something like this 123.0 means exactly 123 innings
> > pitched, 123.1 means 123 1/3 innings, and 123.2 means 123 2/3 innings
> > pitched. I'm contemplating the best way to write a function that knows
> > how to sum these values accurately. Is this something that can be done
> > with PL/PGSQL or should I go straight to something like PLPERL?
> > Alternatively, I could research a way to represent fractions in the DB
> > and write a script to convert all values in this column. Any advice
> > here??
>
> You'd probably be best off doing the conversion at insert time into an
> additional field assuming that inserts are much less likely that
> selects on your data.
>
> (col-floor(col)*(10/3::numeric) seems to get back an appropriate value
> but is probably reasonably expensive.
Hi,
I tried to put your nice idea into the GROUP BY clause, since I wanted
to know how expensive it is. There are several players in the players
table and about 60k rows in the pitching_stats, which have only columns
concerned with the SUM() aggregate function. In case of my PC with 333MHz
clock, 256M SDRAM, and ATA33 HDD, the costs of executing the following
queries are about 4 sec and 6 sec, respectively. It seems to me that
they are reasonable. However the ratio of the sort time appears to occupy
quite a bit of time.
CREATE TABLE players (id serial
CONSTRAINT pkey_players
PRIMARY KEY,
first_name varchar(30));
CREATE TABLE pitching_stats(id integer
NOT NULL
CONSTRAINT key_pitching_stats
REFERENCES players(id),
ip numeric(5,1));
----- 1st version. It costs about 4 sec.
SELECT pl.id, pl.first_name, t.ret
FROM (SELECT ps.id, sum(ps.ip-floor(ps.ip)) % 0.3::numeric
+ floor((sum(ps.ip-floor(ps.ip))) / 0.3::numeric)
+ sum(floor(ps.ip)) AS ret
FROM pitching_stats AS ps
GROUP BY ps.id
) AS t INNER JOIN
players AS pl ON (t.id = pl.id)
----- 2nd version. It costs about 6 sec.
SELECT pl.id, pl.first_name,
sum(ps.ip-floor(ps.ip)) % 0.3::numeric
+ floor((sum(ps.ip-floor(ps.ip))) / 0.3::numeric)
+ sum(floor(ps.ip)) AS ret
FROM players AS pl INNER JOIN
pitching_stats AS ps ON (pl.id = ps.id)
GROUP BY pl.id, pl.first_name
QUERY PLAN:
----- 1st version.
Hash Join (cost=6357.01..7197.06 rows=6000 width=32)
-> Subquery Scan t (cost=6355.96..6955.96 rows=6000 width=16)
-> Aggregate (cost=6355.96..6955.96 rows=6000 width=16)
-> Group (cost=6355.96..6505.96 rows=60000 width=16)
-> Sort (cost=6355.96..6355.96 rows=60000 width=16)
-> Seq Scan on pitching_stats ps
(cost=0.00..983.00 rows=60000 width=16)
-> Hash (cost=1.04..1.04 rows=4 width=16)
-> Seq Scan on players pl (cost=0.00..1.04 rows=4 width=16)
----- 2nd version.
Aggregate (cost=9037.33..9787.33 rows=6000 width=32)
-> Group (cost=9037.33..9337.33 rows=60000 width=32)
-> Sort (cost=9037.33..9037.33 rows=60000 width=32)
-> Hash Join (cost=1.05..3384.10 rows=60000 width=32)
-> Seq Scan on pitching_stats ps
(cost=0.00..983.00 rows=6 0000 width=16)
-> Hash (cost=1.04..1.04 rows=4 width=16)
-> Seq Scan on players pl
(cost=0.00..1.04 rows=4 width=16)
regards,
Masaru Sugawara
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2001-11-17 13:30 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-11-09 06:42 Optimization, etc Jeff Sack <sackj@alum.rpi.edu>
2001-11-09 15:57 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-11-09 18:22 ` Jason Earl <jason.earl@simplot.com>
2001-11-17 13:30 ` Masaru Sugawara <rk73@echna.ne.jp>
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