X-Original-To: pgsql-novice-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 6802A3A6FFB for ; Fri, 10 Dec 2004 20:16:05 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 31895-08 for ; Fri, 10 Dec 2004 20:15:24 +0000 (GMT) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by svr1.postgresql.org (Postfix) with ESMTP id C2AB93AA7BD for ; Fri, 10 Dec 2004 18:34:50 +0000 (GMT) Received: from web60308.mail.yahoo.com (web60308.mail.yahoo.com [216.109.118.119]) by svr4.postgresql.org (Postfix) with SMTP id 55E635B100C for ; Wed, 8 Dec 2004 19:24:20 +0000 (GMT) Received: (qmail 95228 invoked by uid 60001); 8 Dec 2004 19:21:45 -0000 Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; b=Mjcc01UifwsNi3HZMnVjQYlkUIoUjmyoWx7IRYu+8Fpj20zG0Sh5lq7/ar0PlxMd8GN/puWWFju6Fe++X4ygxUZwTch/ihqiXSmqclTG3pUUgo1ZjjgT9F2S0MyduhnLj3p+GBx1Uz7mYPMp0IB/OiPKHUT5RjWjEatFV0tenYY= ; Message-ID: <20041208192145.95226.qmail@web60308.mail.yahoo.com> Received: from [216.223.54.66] by web60308.mail.yahoo.com via HTTP; Wed, 08 Dec 2004 11:21:45 PST Date: Wed, 8 Dec 2004 11:21:45 -0800 (PST) From: Matthew Engelbert Subject: Indexing Strategy To: pgsql-novice@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.89 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, FROM_ENDS_IN_NUMS X-Spam-Level: X-Archive-Number: 200412/55 X-Sequence-Number: 11632 I'm having trouble correctly indexing a large table. The table will be receiving about 30 million rows/day, and has the following definition: CREATE TABLE quote ( symbol varchar (5), price numeric (7,4), time timestamp ); I need to configure the indices so that I can minimize the time to request all the records for a particular symbol, for a particular day, ordered by time. I've tried clustering a single index based on symbol, then time, but this operation takes much too long. I think this is because this operation would have O(N^2) complexity(?). I think my next best option would be to cluster on an index based on symbol, then have another index on time. Does any one know of some resources on the web that discusses indexing strategies ? Any help would be appreciated? Thanks. - Matt