pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Very long index build time 2+ messages / 2 participants [nested] [flat]
* Very long index build time @ 2017-02-09 13:07 John Scalia <jayknowsunix@gmail.com> 2017-02-09 17:46 ` Re: Very long index build time Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 1 reply; 2+ messages in thread From: John Scalia @ 2017-02-09 13:07 UTC (permalink / raw) To: 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Very long index build time 2017-02-09 13:07 Very long index build time John Scalia <jayknowsunix@gmail.com> @ 2017-02-09 17:46 ` Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2017-02-09 17:46 UTC (permalink / raw) To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin John Scalia <jayknowsunix@gmail.com> writes: > 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); Well, the first question is whether the index building session is actually doing anything or just waiting. Is it consuming CPU time? It might just be waiting around for some other transaction to finish --- there are multiple places in a CREATE INDEX CONCURRENTLY sequence where we have to wait out all other open transactions. regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2017-02-09 17:46 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2017-02-09 13:07 Very long index build time John Scalia <jayknowsunix@gmail.com> 2017-02-09 17:46 ` Tom Lane <tgl@sss.pgh.pa.us>
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