Received: from megazone.bigpanda.com ([63.150.15.178]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fA9Fvbm96052 for ; Fri, 9 Nov 2001 10:57:37 -0500 (EST) (envelope-from sszabo@megazone23.bigpanda.com) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 73596D5DB; Fri, 9 Nov 2001 07:57:41 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 690A05BDF; Fri, 9 Nov 2001 07:57:41 -0800 (PST) Date: Fri, 9 Nov 2001 07:57:41 -0800 (PST) From: Stephan Szabo To: Jeff Sack Cc: Subject: Re: Optimization, etc In-Reply-To: <005101c168e9$a7aac5a0$019ca8c0@jeff> Message-ID: <20011109074346.L57927-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200111/146 X-Sequence-Number: 5530 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.