Re: GIST create index very very slow

On Fri, Aug 15, 2014 at 5:59 PM, worthy7 <worthy(dot)vii(at)gmail(dot)com> wrote:
> CREATE INDEX USING GIST(timerange);
>
> On 1.3 million rows this took only 30 seconds.
> on 70 million its already taken over a day. I swear it didn't take this long
> on version 9.3
>
>
> Is there some kind of known bug with GIST? CPU is at 4% or less and ram is
> at 150mbs
> IO usage is at 100% but most of it is writes? (like 3.5mbps!) which looks
> good but actually the size of the disk is only increasing by like 8 BYTES
> per second.
>
> This is really odd and I don't want to wait an indefinite amount of time.

Sounds pretty weird, but I'm not sure anybody here will be able to
help unless you can provide a more detailed problem report, such as a
careful comparison of 9.3 vs. 9.4 behavior. Off-hand, the only thing
that occurs to me is that a nearly-full disk often has much worse
performance than one with some free space remaining, because the
system is no longer able to find chunks of consecutive free space.
But even if that's an issue, 8 bytes per second is sort of ridiculous.
I think something's missing from your report, though, because if there
is 3.5 Mb/s of write I/O and only 8 b/s of file growth, nearly all of
the writes are doing something other than extending that file. If you
can track down what that other thing is, it might shed quite a bit of
light on the situation.

You might also want to verify that you're using the same
maintenance_work_mem setting on both versions.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company