2

I have to run a whole bunch of GIST index creation, on some tables containing lots of records (scale of many countries, OSM data):

CREATE INDEX CONCURRENTLY IF NOT EXISTS osm_footways_gist ON osm_footways USING GIST (geom);
....

(more info here: https://www.2ndquadrant.com/en/blog/create-index-concurrently/ )

The time needed to run them all is quite long, it can be up to several hours on a common desktop computer.

But I noticed that it uses only one single CPU core.

Would it be possible to run such index creation queries using all available CPU cores (or a predetermined number of them)?

202002192007 Edit

According to https://www.cybertec-postgresql.com/en/postgresql-parallel-create-index-for-better-performance/ I've tried this:

SET max_parallel_maintenance_workers TO 48;
SET maintenance_work_mem TO '128 GB';
ALTER TABLE osm_footways SET (parallel_workers = 48);
CREATE INDEX CONCURRENTLY IF NOT EXISTS osm_footways_gist ON osm_footways USING GIST (geom);
....

But still have 1 CPU core working.

Info:

>>> psql
# select Version();
                 version  
---------------------------------------
 PostgreSQL 11.10 (Debian 11.10-1.pgdg90+1) on x86_64-pc-linux-gnu,
 compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
(1 row)

# select postgis_version();
            postgis_version            
---------------------------------------
 2.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)

Dockerized.

4
  • 2
    SET max_parallel_maintenance_workers TO 2; +using tablespaces some good notes on better indexing > cybertec-postgresql.com/en/…
    – Mapperz
    Feb 19 '21 at 16:45
  • It seems promising, I went through the article and SET max_parallel_maintenance_workers and maintenance_work_mem to high values before running the index creation but I always have only 1 CPU core working. :/ (hard drive setup doesn't allow to play much with tablespaces). Feb 19 '21 at 20:01
  • What version of PostGIS? Feb 19 '21 at 20:31
  • psql 11.10.1, updated main question accordingly. Also added postgis version. Feb 19 '21 at 21:04
2

GIST index building cannot utilize multiple cores yet.

PostgreSQL can build indexes while leveraging multiple CPUs in order to process the table rows faster. This feature is known as parallel index build. For index methods that support building indexes in parallel (currently, only B-tree)

https://www.postgresql.org/docs/current/sql-createindex.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.