10

Recently I was looking around in pg_stat_user_tables and was surprised to see high numbers of sequential scans on some of my spatial tables. Sure enough, these tables were missing spatial indexes.

How can I find all tables with an un-indexed geometry column?

  • Thanks for the ref to pg_stat_user_tables. It is heartening that someone of your knowledge admits to such mistakes. To the young ones at work that I mentor, I always say: If there is no natural candidate for a primary key, add a serial column. Always define the SRID and geometry type. Always add a spatial index. Because sequence scans might work with a million rows, but, there comes a point..... Do as I say, rather than as I did :D. – John Powell May 25 '17 at 14:07
9

Tables with missing spatial indexes can be found by querying the system tables:

SELECT g.* 
FROM 
  (SELECT 
     n.nspname, 
     c.relname, 
     c.oid AS relid, 
     a.attname, 
     a.attnum 
   FROM pg_attribute a 
   INNER JOIN pg_class c ON (a.attrelid=c.oid)
   INNER JOIN pg_type t ON (a.atttypid=t.oid)
   INNER JOIN pg_namespace n ON (c.relnamespace=n.oid) 
   WHERE t.typname='geometry' 
   AND   c.relkind='r'
 ) g 
LEFT JOIN pg_index i ON (g.relid = i.indrelid AND g.attnum = ANY(i.indkey)) 
WHERE i IS NULL;
  • Could it be even better as WHERE t.typname IN ('geometry', 'geography') AND t.typtype='b'? See trac.osgeo.org/gdal/ticket/6896. – user30184 May 24 '17 at 17:54
  • @user30184 Can you explain the t.typtype = 'b' piece of that? – dbaston May 24 '17 at 18:04
  • 1
    It is actually useless piece. The code change in GDAL was for dealing with a rare situation when standard PostgreSQL database has a table named "geometry". That has also an entry in pg_type but with typtype='c'. However, if you have PostGIS installed it is not possible to end to such situation. create table "geometry" (foo text); gives ERROR: type "geometry" already exists HINT: A relation has an associated type of the same name, so you must use a name that doesn't conflict with any existing type. – user30184 May 24 '17 at 20:05
6

I have created a function which can create automatically all missing indexes. An "simulate" parameter allows to get the list of the missing spatial indexes, but performs not CREATE INDEX

See https://gist.github.com/mdouchin/cfa0e37058bcf102ed490bc59d762042

To get the list of missing indexes, run:

SELECT * FROM create_missing_spatial_indexes(True)

To create the needed indexes, run:

SELECT * FROM create_missing_spatial_indexes()

or

SELECT * FROM create_missing_spatial_indexes(False)
  • Worked like a charm! Great tool. – RyanDalton Dec 27 '18 at 4:33

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.