From: Tom Lane <tgl@sss.pgh.pa.us>
To: Aaron Turner <synfinatic@gmail.com>
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: Sun, 12 Feb 2006 10:54:34 -0500
Message-ID: <16809.1139759674@sss.pgh.pa.us> (raw)
In-Reply-To: <1ca1c1410602112358h1e0e9696lff8f172769dc587e@mail.gmail.com>
References: <1ca1c1410602100016p2b5fdcc4wbf45612d7efc5fdf@mail.gmail.com>
<43ECC9BF.2030107@zeut.net>
<1ca1c1410602100924s191e7cddo4bda80d8f8d7cb6d@mail.gmail.com>
<20060211212453.GT57845@pervasive.com>
<1ca1c1410602112358h1e0e9696lff8f172769dc587e@mail.gmail.com>
Aaron Turner <synfinatic@gmail.com> writes:
> Well before I go about re-architecting things, it would be good to
> have a strong understanding of just what is going on. Obviously, the
> unique index on the char(48) is the killer. What I don't know is:
You have another unique index on the integer primary key, so it's not
the mere fact of a unique index that's hurting you.
> 1) Is this because the column is so long?
Possibly. Allowing for 12 bytes index-entry overhead, the char keys
would be 60 bytes vs 16 for the integer column, so this index is
physically almost 4x larger than the other. You might say "but that
should only cause 4x more I/O" but it's not necessarily so. What's
hard to tell is whether you are running out of RAM disk cache space,
resulting in re-reads of pages that could have stayed in memory when
dealing with one-fifth as much index data. You did not show us the
iostat numbers for the two cases, but it'd be interesting to look at
the proportion of writes to reads on the data drive in both cases.
> 2) Is this because PG is not optimized for char(48) (maybe it wants
> powers of 2? or doesn't like even numbers... I don't know, just
> throwing it out there)
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.
Another thing to think about is whether this is C locale or not.
String comparisons in non-C locales can be horrendously expensive
... though I'd expect that to cost CPU not I/O. (Hmm ... is it
possible your libc is hitting locale config files constantly?
Might be worth strace'ing to confirm exactly where the I/O is
going.)
> 4) Does decoding the data (currently base64) and storing the binary
> data improve the distribution of the index, thereby masking it more
> efficent?
No, but it'd reduce the size of the index, which you certainly want.
Storing as bytea would also eliminate any questions about wasteful
locale-dependent comparisons.
The only one of these effects that looks to me like it could result in
worse-than-linear degradation of I/O demand is maxing out the available
RAM for disk cache. So while improving the datatype choice would
probably be worth your while, you should first see if fooling with
shared_buffers helps, and if not it's time to buy RAM not disk.
regards, tom lane
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: tgl@sss.pgh.pa.us, synfinatic@gmail.com, jnasby@pervasive.com, matthew@zeut.net
Subject: Re: 10+hrs vs 15min because of just one index
In-Reply-To: <16809.1139759674@sss.pgh.pa.us>
* 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