X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C3ADFD8B81 for ; Fri, 14 Oct 2005 13:53:09 -0300 (ADT) 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 42551-02 for ; Fri, 14 Oct 2005 16:53:06 +0000 (GMT) Received: from mail.envyfinancial.com (unknown [206.248.142.186]) by svr1.postgresql.org (Postfix) with ESMTP id 83467D863C for ; Fri, 14 Oct 2005 13:53:06 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by mail.envyfinancial.com (Postfix) with ESMTP id 48AC51CBF0; Fri, 14 Oct 2005 12:38:20 -0400 (EDT) Received: from mail.envyfinancial.com ([127.0.0.1]) by localhost (mark.mielke.cc [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 26745-07; Fri, 14 Oct 2005 12:38:20 -0400 (EDT) Received: by mail.envyfinancial.com (Postfix, from userid 500) id F355E1CBF1; Fri, 14 Oct 2005 12:38:19 -0400 (EDT) Date: Fri, 14 Oct 2005 12:38:19 -0400 From: mark@mark.mielke.cc To: ext-christian.roche@nokia.com Cc: pgsql-performance@postgresql.org Subject: Re: Best way to get all different values in a column Message-ID: <20051014163819.GA26955@mark.mielke.cc> References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: amavisd-new at mail.envyfinancial.com X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.191 required=5 tests=[AWL=0.013, NO_REAL_NAME=0.178] X-Spam-Level: X-Archive-Number: 200510/235 X-Sequence-Number: 15003 On Fri, Oct 14, 2005 at 06:02:56PM +0200, ext-christian.roche@nokia.com wrote: > Does anyone here know what is the most efficient way to list all > different values of a given column with low cardinality ? For instance > I have a table with columns DAY, NAME, ID, etc. The table is updated > about each week with thousands of records with the same (current) date. > Now I would like to list all values for DAY, only if possible without > scanning all the table each time I submit the request. > I can think of: > ... > Solution 6: Store the values in a separate table, recreated each time > TABLE is updated. I've found a variant on 6 to work well for this problem domain. Why not insert into the separate table, when you insert into the table? Either as a trigger, or in your application. Cheers, mark -- mark@mielke.cc / markm@ncf.ca / markm@nortel.com __________________________ . . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/