Finding PostGIS tables that are missing indexes?
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;
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)