To: "Guillaume Smet" <guillaume.smet@gmail.com>
cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Index creation time and distribution 
In-reply-to: <1d4e0c10805220638x64f80609ob9a6fe00e4587362@mail.gmail.com> 
References: <1d4e0c10805220532v514c2bd5je4c6c20ceb485495@mail.gmail.com>
	<29529.1211462076@sss.pgh.pa.us>
	<1d4e0c10805220638x64f80609ob9a6fe00e4587362@mail.gmail.com>
Comments: In-reply-to "Guillaume Smet" <guillaume.smet@gmail.com>
	message dated "Thu, 22 May 2008 15:38:25 +0200"
Date: Thu, 22 May 2008 15:18:00 -0400
Message-ID: <3606.1211483880@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Guillaume Smet" <guillaume.smet@gmail.com> writes:
> On Thu, May 22, 2008 at 3:14 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Do you have maintenance_work_mem set large enough that the index
>> creation sort is done in-memory?  8.1 depends on the platform's qsort
>> and a lot of them are kinda pessimal for input like this.

> maintenance_work_mem is set to 256 MB and the size of the index is 400 MB.

> Should I try to raise it up to 512 MB? The server only has 2GB of RAM
> so it seems a bit high.

Hmm, that's most likely not going to be enough to get it to do an
in-memory sort ... try turning on trace_sort to see.  But anyway,
if you are in the on-disk sort regime, 8.3 is only going to be
marginally faster for such a case --- it's going to have to write
all the index entries out and read 'em back in anyway.

>> 8.2 (which uses our own qsort) seems to perform better in a quick
>> test.

> Mmmmh OK. I was considering an upgrade to 8.3 in the next months anyway.

> Do we agree that in the case of unnamed prepared statement, 8.3 plans
> the query after the BIND? The partial index seems to be a better
> solution anyway, considering that it's 12 MB vs 400 MB.

Ermm .. this is in fact mostly broken in 8.3.0 and 8.3.1.  If you don't
want to wait for 8.3.2, you need this patch:
http://archives.postgresql.org/pgsql-committers/2008-03/msg00566.php

			regards, tom lane