Finding PostGIS tables that are missing indexes?

Question

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?

Answer 1

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;

Answer 2

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)