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.

Akhilesh Mishra
  • 5,876
  • 3
  • 16
  • 32
Rocket04
  • 1,801
  • 5
  • 25
  • 47
  • 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? – Schwern Jun 23 '20 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. – Rocket04 Jun 24 '20 at 13:12

1 Answers1

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 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! – Rocket04 Jun 23 '20 at 21:13
  • 1
    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. – Laurenz Albe Jun 24 '20 at 05:28
  • 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! – Rocket04 Jun 24 '20 at 11:34
  • @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 Aug 12 '22 at 12:57
  • 1
    @joseluisq Definitely. – Laurenz Albe Aug 12 '22 at 18:09