I have a table with ~200million spatial records in it, and it is taking days to build a spatial index on the one geometry column: should this be taking so long?

The geom column is of the type geometry(Linestring) and is representing features on chromosomes. I used copy to load these data in roughly 10 minutes, but when I try to build a spatial index on the geometry column, it appears to spin indefinitely (specifics on my setup below the questions). Building a standard index on a table representing the Linestring not as geometry but as three columns: y_coord, x_start, x_end only takes minutes... I feel like I'm doing something wrong in building the spatial index.

My questions are:

  1. Should a spatial index on 200 million records take days to generate?
  2. What is the best configuration to build spatial indexes on datasets of 100s of millions to billions of data points?

My table looks like:

CREATE table JEM2(
idx serial, 
var geometry(Linestring), 
record text);

I load the 200million rows into the table with copy. Records loaded into the table look correct:

SELECT * from jem2 where idx=1;
 idx |                                        var                               
          |                   record                    
   1 | 010200000002000000000000000091C340000000000000F03F000000000092C3400000000
00000F03F | Record(CHROM=1, POS=10019, REF=TA, ALT=[T])

My PostgreSQL server is running on a pretty big server: 96 cores, 60Gig of RAM. PostgreSQL and PostGIS versions below:

SELECT version();

PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~
14.04.4) 4.8.4, 64-bit
(1 row)

SELECT postgis_full_version();

POSTGIS="2.5.2 r17328" [EXTENSION] PGSQL="110" GEOS="3.7.1-CAPI-1.11.1 27a5e771
" PROJ="Rel. 6.0.0, March 1st, 2019" GDAL="GDAL 2.4.1, released 2019/03/15" LIBX
(1 row)

I researched spatial index generation tuning, and from what I found I tweaked some settings before attempting to create the index:

SET work_mem to '2GB';
SET maintenance_work_mem to '2GB';
SELECT pg_reload_conf();
ANALYZE jem2 (var);

Then I try to create the index:

CREATE index jem2_spatial_index on JEM2 using GIST (var);

As I monitor top on the server, postgres takes 100% of 1 cpu, but uses nearly no memory.... I find this unusual as I expected the index creation to be pretty memory intensive. The index generation on the other table representing the linestrings in 3 integer columns shows multiple processes being fired up to build the index and sizable memory used briefly. Also of note, there is effectively nothing else running on the server, no IO or other resource drains.

Something I did not do was:

alter table jem2 alter column var set storage external;

despite reading it improved query performance, it sounded like it would add time to index creation. I wanted to factor it out of this slow index creation problem first.

closed as too broad by PolyGeo Mar 31 at 0:23

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • As per the Tour there should be only one question asked per question. – PolyGeo Mar 31 at 0:24
  • I would create a small subset of the table. Say 10k records, and try the indexing with different options there first. We create indexes on tables with 10's millions of records and it never takes hours. – HeikkiVesanto Mar 31 at 12:01
  • Thank you PolyGeo- I had not caught the 1Q per Q guidance. I'll keep that in mind moving ahead. – John Major Mar 31 at 21:32

Yes, spatial index building can be slow in PostGIS / PostgreSQL. One issue is that PostgreSQL, even in the latest official 11.2 release, still only supports parallel indexing using multiple cores on B-tree type indexes, not GiST, which is the most common type of spatial index used. As such, spatial indexing in PostGIS / PostgreSQL cannot yet take advantage of parallel indexing.

If the data you are using can be clustered / sorted in meaningful way, you might consider using a BRIN index though. These are far easier and faster to build, and require far less disk space. They are only efficient though, if the data is properly spatially sorted or clustered, if I understood it well, and primarily used with point type geometry, from the few examples I saw.

I have a hard time visualizing your specific purpose of putting this chromosome information in PostGIS and spatially indexing it. What is it you are trying to achieve? Aren't the other non-spatial indexes you already mentioned enough for your purpose?

  • If you consider each chromosome a number line, and all features on that chromosome linestrings (ie: gene boundaries, variant locations, etc), then this is what I am mapping into the Linestring here. I can use traditional indexing, but i prefer the spatial indexing b/c (a) it is faster to query, and (b) traditional indexing of these features makes detecting features which overlap but are not contained in your query range hard to do in a single query(minimally, you'll need 3, and they will be slow). – John Major Mar 31 at 21:22
  • I also looked into brin indexes, but am not comfortable that I understand them enough to use it confidently. My features are not points, but all linestrings. And the data will be regularly added to, so it will not remain in a state amenable to BRIN indexing. It also seemed like BRIN did not support all of the spatial comparison operators I needed (same, intersects, overlaps, contains). – John Major Mar 31 at 21:26
  • It appears that I had not comitted the memory parameter changes, and they might have been to blame for the slow performance.... I tried the load again, from a freshly generated table & using set storage external && the index built in 30min, which is totally acceptable. I am planning to run some benchmark tests in 100million datapoint chunks and will post that here when it is publicly available. – John Major Mar 31 at 21:30
  • Good you have sorted it. I was going to state that I have created gist indexes on billions of rows of polygonal data in hours. It's cool to see chromosone data stored as a Linestring, a creative use to be sure. – John Powell Apr 2 at 14:38

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