From: PFC <lists@peufeu.com>
To: Aaron Turner <synfinatic@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jim C. Nasby <jnasby@pervasive.com>
Cc: Matthew T. O'Connor <matthew@zeut.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: 10+hrs vs 15min because of just one index
Date: Mon, 13 Feb 2006 09:55:20 +0100
Message-ID: <op.s4wlqiikcigqcu@apollo13> (raw)
In-Reply-To: <1ca1c1410602121133y279ea488nc38f049ac6ebc7f4@mail.gmail.com>
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>
>> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@peufeu.com, synfinatic@gmail.com, tgl@sss.pgh.pa.us, jnasby@pervasive.com, matthew@zeut.net
Subject: Re: 10+hrs vs 15min because of just one index
In-Reply-To: <op.s4wlqiikcigqcu@apollo13>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox