DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=nCfj97yiw9frUugSfNG3sv3gr789araCkD+t3hq+VKdJmXeMR1Dne0VkqEwPgWcW6PuPqkqhqqkvULAvEs5MTPFQmgsYTitK6bG2MR2iW53/7xlFYLa0RgAIdx/Mtj9fh6TQezhj0JLXmiG83b9uaMt54kXamADNXlV3CMV33fU=
Message-ID: <1ca1c1410602121133y279ea488nc38f049ac6ebc7f4@mail.gmail.com>
Date: Sun, 12 Feb 2006 11:33:57 -0800
From: Aaron Turner <synfinatic@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: 10+hrs vs 15min because of just one index
Cc: "Jim C. Nasby" <jnasby@pervasive.com>,
	"Matthew T. O'Connor" <matthew@zeut.net>, pgsql-performance@postgresql.org
In-Reply-To: <16809.1139759674@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
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/