pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.





view thread (4+ messages)  latest in thread

Message-ID: <20011109074346.L57927-100000@megazone23.bigpanda.com>
Permalink:  ../20011109074346.L57927-100000@megazone23.bigpanda.com/
Also on:    postgresql.org/message-id/20011109074346.L57927-100000@megazone23.bigpanda.com

 · 

reply

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