X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 33606D1C942 for ; Wed, 23 Jul 2003 01:38:33 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 10729-05 for ; Tue, 22 Jul 2003 22:37:47 -0300 (ADT) Received: from joeconway.com (66-146-172-86.skyriver.net [66.146.172.86]) by svr1.postgresql.org (Postfix) with ESMTP id D8958D1C508 for ; Tue, 22 Jul 2003 22:38:27 -0300 (ADT) Received: from [206.19.64.3] (account jconway HELO joeconway.com) by joeconway.com (CommuniGate Pro SMTP 4.0.6) with ESMTP-TLS id 12092; Tue, 22 Jul 2003 18:03:11 -0700 Message-ID: <3F1DE694.30201@joeconway.com> Date: Tue, 22 Jul 2003 18:36:20 -0700 From: Joe Conway User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.3) Gecko/20030312 X-Accept-Language: en-us, en MIME-Version: 1.0 To: "Castle, Lindsay" Cc: pgsql-performance@postgresql.org Subject: Re: One table or many tables for data set References: In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/383 X-Sequence-Number: 2676 Castle, Lindsay wrote: > The data structure looks like this: > element > date > num1 > num2 > num3 > num4 > units > > There are approx 12,000 distinct elements for a total of about 6 million > rows of data. Ahh, that helps! So are the elements evenly distributed, i.e. are there approx 500 rows of each element? If so, it should be plenty quick to put all the data in one table with an index on "element" (and maybe a multicolumn key, depending on other factors). > The scanning technology I want to use may need a different number of rows > and different columns depending on the scan formula; > eg scan1 may need num1, num2 and num3 from the last 200 rows for > element "x" > scan2 may need num1, units from the last 10 rows for element "y" When you say "last X rows", do you mean sorted by "date"? If so, you might want that index to be on (element, date). Then do: SELECT num1, num2, num3 FROM mytable WHERE element = 'an_element' order by date DESC LIMIT 20; Replace num1, num2, num3 by whatever columns you want, and "LIMIT X" as the number of rows you want. HTH, Joe