Very long index build time

From: John Scalia <jayknowsunix(at)gmail(dot)com>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Very long index build time
Date: 2017-02-09 13:07:51
Message-ID: CABzCKRCd=Wx2gHMFZSjBpPQGi4LgFQnGPrw6h+ZpZgV6TcUJFw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi all,

I'm building a test database for our data warehouse,and I'm trying to build
a trigram index on one of the tables with a 4000 character varchar field,
Yes, I want to change this table at some point to make that attribute a
text field, along with some other structural changes. but that's in the
future. This particular table reports to be approximately 420 Gb. My
question is fairly simple, this index has been building since Tuesday
morning @ 9:52AM EST - nearly 48 hours. Does this sound like a normal build
time? The command I used is:

CREATE INDEX CONCURRENTLY test_schema_notes_trigram_idx on
test_schema.notes USING gin (notes gin_trgm_ops);

Should I have used some other parameters? I'm rather new to these text
indexes and am not quite sure if this is correct, but 48+ hours - it's not
done yet, seems awfully long. The VM involved has 24Gb RAM assigned to it,
and I've set shared_buffers to 10Gb. Maybe I should add more, but there's
no way this whole table could fit in memory.
--
Jay

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Kevin Duffy 2017-02-09 17:10:06 Re: Change ownership of a database
Previous Message Lazaro Garcia 2017-02-09 09:59:53 Why pgpool TPS is lowest versus postgresql direct connections?