1

This is similar to a recent problem I posted where COPY command was hanging for a large data set. In that instance, it was due to a foreign key constraint. But in this case I'm creating an index, so I would think an FK wouldn't be an issue, even though I still disabled triggers on the table just in case. I'm trying to add a regular btree index on a table with 10 billion rows. The index is on two int fields. I tried running it and it was going forever, so I thought it might just be too slow, I increased max_parallel_maintenance_workers to 8 and maintenance_work_mem to 2047MB (I'm on Windows, so it's the max).

At that point, things seemed to go faster, but the same problem happened: I can see the files growing in the pgsql_tmp/pgsql_tmpxxxx.x.sharedfileset folder, until they just stop but the index creation never seems to finish.

I wondered if I'd set too many workers for whatever reason, so I tried setting it to 4, same problem. Files were last modified around 3:20am, it's 7:35am and it's still running. The files in the folder are 261GB, which looks about right compared to the table size and every time I run the process it stalls at that size, so I assume it's done with creating the index, I just have no clue what it might be doing at this point. In case it matters, the table has a foreign key on another table that has 1 billion records, but the triggers are disabled on the table, which has worked for me in loading data in the table. I checked for locks, there are none, it's not waiting on any lock, which makes sense because this is a test database with dummy data that I created to test some things, so nobody else even knows it exists or has any use for it.

Improve this question
2
  • 10 billion rows is a lot. It might just take a long time, especially if you're not on an SSD. What's your resource usage like? Is it using CPU and Disk? Commented Jun 23, 2020 at 18:21
  • I got the answer I needed, but figured I'd reply just to give further info on the process. CPU usage was pretty low, even when I tried with 8 worker processes. It was running off a decommissioned server, so not a dev machine, so the resources were pretty good. Having said that, not sure what kind of disk it was using. In the end, mother time did the job. Commented Jun 24, 2020 at 13:12

1 Answer 1

Reset to default
7

Creating an index runs in several stages. The table has to be read, the values have to be sorted, and the index has to be created on disk.

In certain stages you will see temporary files growing, in others not, even though CREATE INDEX is still working. Perhaps it is writing the index file at the moment.

So be patient, it will finish.

If you are nervous look into pg_locks to see if the CREATE INDEX is blocked by something. That may be the case if it is a CREATE INDEX CONCURRENTLY, which has do do more complicated processing.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

If I had to summarize this answer, it would be "quit your whining, sit your ass down, and wait!" That's what I did, and it worked wonders, 17 hours later the index was finally created! I guess it really does take that long. Just wish there was a better way to track progress. But thanks for the answer, you kept hope alive!
I hope I was not that rude, and I am glad it worked eventually. To say something positive: recent PostgreSQL versions support parallel processing for index creation, which speeds up progress.
You were most certainly NOT rude. My comment probably unintentionally conveyed that it's how I perceived it when in fact I just wanted to emphasize how sometimes, the answer is to just be patient and wait. Thanks again!
@LaurenzAlbe but how about GiST indexes in the maintenance_work_mem context? I mean, does inceasing maintenance_work_mem help to speed up the build time of GiST indexes on big tables?
@joseluisq Definitely.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.