GIST index creation too slow on PostgreSQL

Question

I have a database in PostgreSQL with the following structure:

Column    |         Type          | Collation | Nullable |                    Default                     
-------------+-----------------------+-----------+----------+------------------------------------------------
 vessel_hash | integer               |           | not null | nextval('samplecol_vessel_hash_seq'::regclass)
 status      | character varying(50) |           |          | 
 station     | character varying(50) |           |          | 
 speed       | character varying(10) |           |          | 
 longitude   | numeric(12,8)         |           |          | 
 latitude    | numeric(12,8)         |           |          | 
 course      | character varying(50) |           |          | 
 heading     | character varying(50) |           |          | 
 timestamp   | character varying(50) |           |          | 
 the_geom    | geometry              |           |          | 
Check constraints:
    "enforce_dims_the_geom" CHECK (st_ndims(the_geom) = 2)
    "enforce_geotype_geom" CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL)
    "enforce_srid_the_geom" CHECK (st_srid(the_geom) = 4326)

The database contains ~146.000.000 records and the size of table that contains the data is:

public | samplecol   | table    | postgres | 31 GB      |

I try to create a GIST index on the geometry field the_geom with this command:

create index samplecol_the_geom_gist on samplecol using gist (the_geom);

but takes too long. It runs 2 hours already.

Based on this question Slow indexing of 300GB Postgis table Ask Question, before index creation I execute in psql console:

ALTER SYSTEM SET maintenance_work_mem = '1GB'; 
ALTER SYSTEM


SELCT pg_reload_conf();

pg_reload_conf 
----------------  
t 
(1 row)

But index creation takes too long. Does anyone know why? An how to fix this?

I have not this option unfortunately. So, you think that index creation would end up in a resonable time? — e7lT2P
– e7lT2P, Commented Jul 2, 2019 at 10:31

Laurenz Albe · Accepted Answer · 2019-07-02 14:06:52Z

3

I am afraid you'll have to sit it out.

Apart from high maintenance_work_mem, there is not really a tuning option. Increasing max_wal_size will help somewhat, since you will get fewer checkpoints.

If you can't live with an ACCESS EXCLUSIVE lock for that long, try CREATE INDEX CONCURRENTLY, which will be even slower, but won't block concurrent database activity.

answered Jul 2, 2019 at 14:06

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

1 Comment

e7lT2P Over a year ago

So there is nothing that I can do. I have to wait until finish

Collectives™ on Stack Overflow

GIST index creation too slow on PostgreSQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related