From: Jason Earl <jason.earl@simplot.com>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: Jeff Sack <sackj@alum.rpi.edu>
Cc: pgsql-sql@postgresql.org
Subject: Re: Optimization, etc
Date: 09 Nov 2001 11:22:06 -0700
Message-ID: <87itcjby9d.fsf@npa01zz001.simplot.com> (raw)
In-Reply-To: <20011109074346.L57927-100000@megazone23.bigpanda.com>
References: <20011109074346.L57927-100000@megazone23.bigpanda.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jason.earl@simplot.com, sszabo@megazone23.bigpanda.com, sackj@alum.rpi.edu
Subject: Re: Optimization, etc
In-Reply-To: <87itcjby9d.fsf@npa01zz001.simplot.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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