From: 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: Sun, 12 Feb 2006 11:33:57 -0800
Message-ID: <1ca1c1410602121133y279ea488nc38f049ac6ebc7f4@mail.gmail.com> (raw)
In-Reply-To: <16809.1139759674@sss.pgh.pa.us>
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>
On 2/12/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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.
Understood. I just wasn't sure if in general unique indexes are some
how more expensive then non-unique indexes.
> > 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.
Sounds a lot like what Marc mentioned.
> > 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.
Yep, everything exactly 48. Looks like I'll be storing it as a bytea
in the near future though.
> 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.
Yeah, that's what it's beginning to sound like. Thanks Tom.
--
Aaron Turner
http://synfin.net/
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: 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: <1ca1c1410602121133y279ea488nc38f049ac6ebc7f4@mail.gmail.com>
* 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