[postgis-users] Can't build GIST index on table with > 5, 000, 000 rows

[Gregory S. Williamson]

We've got a lot of tables with more than 10 million rows, all using GIST indexes, and have no problem (this is with 0.8 and 1.x).

However, it looks as if you may be missing an argument to the index function:
CREATE INDEX sid_citi2sx ON sid_content_citi2 USING GIST ( the_geom GIST_GEOMETRY_OPS);

That might be the problem, althogh I don't know why it wouldn't produce an error.

HTH,

Greg Williamson
DBA
GlobeXplorer LLC

-----Original Message-----
From:	postgis-users-bounces at postgis.refractions.net on behalf of Chip Masters
Sent:	Wed 7/27/2005 8:02 PM
To:	postgis-users at postgis.refractions.net
Cc:	
Subject:	[postgis-users] Can't build GIST index on table with > 5, 000,000 rows
I downloaded the NGA Geographical Names dataset (from
http://earth-info.nga.mil/gns/html/cntry_files.html)
and built a spatial database using the attached
script, nga.sql. The resulting table has over 5.5
million rows. Everything went fine until it reached
the step in my script that builds the GIST index on
the_geom column:

> create index gns_the_geom_gist on gns using gist 
> (the_geom);

I let this run for around 24 hours before I realized
that postgres had died without producing any error
message. Unfortunately, I don't have any further
debugging information to provide.

I was wondering if there is a known limitation on the
size of a GIST index, or if there is another method I
could use to build the index incrementally.

I'm running PostgreSQL 8.0.3. Here's my postgis
version info:

nationalatlas=# select postgis_full_version();
                                        
postgis_full_version                                  
      
------------------------------------------------------------------------------------------------------
 POSTGIS="1.0.1" GEOS="2.1.2" PROJ="Rel. 4.4.9, 29 Oct
2004" USE_STATS DBPROC="0.3.0" RELPROC="0.3.0"
(1 row)

Thanks in advance,

Chip Masters




		
____________________________________________________
Start your day with Yahoo! - make it your home page 
http://www.yahoo.com/r/hs 
 

!DSPAM:42e84ac648551957720581!