From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Jeff Sack <sackj@alum.rpi.edu>
Cc: pgsql-sql@postgresql.org
Subject: Re: Optimization, etc
Date: Fri, 9 Nov 2001 07:57:41 -0800 (PST)
Message-ID: <20011109074346.L57927-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <005101c168e9$a7aac5a0$019ca8c0@jeff>
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.
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: sszabo@megazone23.bigpanda.com, sackj@alum.rpi.edu
Subject: Re: Optimization, etc
In-Reply-To: <20011109074346.L57927-100000@megazone23.bigpanda.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