X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 9F7D69DCBC8 for ; Mon, 13 Feb 2006 04:55:18 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 67063-04 for ; Mon, 13 Feb 2006 04:55:19 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from peufeu.com (unknown [82.67.9.10]) by postgresql.org (Postfix) with ESMTP id B050D9DCA1A for ; Mon, 13 Feb 2006 04:55:15 -0400 (AST) Received: (qmail 31259 invoked from network); 13 Feb 2006 09:55:21 +0100 Received: from unknown (HELO apollo13) (82.67.9.10) by boutiquenumerique.com with SMTP; 13 Feb 2006 09:55:21 +0100 Date: Mon, 13 Feb 2006 09:55:20 +0100 To: "Aaron Turner" , "Tom Lane" Subject: Re: 10+hrs vs 15min because of just one index Cc: "Jim C. Nasby" , "Matthew T. O'Connor" , pgsql-performance@postgresql.org References: <1ca1c1410602100016p2b5fdcc4wbf45612d7efc5fdf@mail.gmail.com> <43ECC9BF.2030107@zeut.net> <1ca1c1410602100924s191e7cddo4bda80d8f8d7cb6d@mail.gmail.com> <20060211212453.GT57845@pervasive.com> <1ca1c1410602112358h1e0e9696lff8f172769dc587e@mail.gmail.com> <16809.1139759674@sss.pgh.pa.us> <1ca1c1410602121133y279ea488nc38f049ac6ebc7f4@mail.gmail.com> From: PFC Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-ID: In-Reply-To: <1ca1c1410602121133y279ea488nc38f049ac6ebc7f4@mail.gmail.com> User-Agent: Opera M2/8.51 (Linux, build 1462) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.09 required=5 tests=[AWL=0.090] X-Spam-Score: 0.09 X-Spam-Level: X-Archive-Number: 200602/141 X-Sequence-Number: 17127 >> Are the key values really all 48 chars long? If not, you made a >> bad datatype choice: varchar(n) (or even text) would be a lot >> smarter. char(n) wastes space on blank-padding. > > Yep, everything exactly 48. Looks like I'll be storing it as a bytea > in the near future though. It's a good idea not to bloat a column by base64 encoding it if you want to index it. BYTEA should be your friend. If your values are not random, you might want to exploit the correlation. But if they are already quite uncorrelated, and you don't need the index for < >, just for =, you can create an index on the md5 of your column and use it to search. It will use a lot less data but the data will be more random. With a functional index, you don't need to modify your application too much.