Create Spatial Index in PostGIS on an entire Schema
If you want to batch create indexes on geometry columns, you could try this plpgsql function I've just knocked up:
CREATE OR REPLACE FUNCTION BatchIndex(sn text, tn text, cn text) RETURNS void AS $$
DECLARE i_exists integer;
DECLARE idxname text;
BEGIN
idxname := 'idx_' || tn || '_' || cn;
select into i_exists count(*) from pg_class where relname = idxname;
IF i_exists = 0 THEN
EXECUTE 'CREATE INDEX ' ||idxname || ' ON '
|| sn || '.' || tn
|| ' USING GIST(' || cn || ')';
END IF;
END;
$$ LANGUAGE plpgsql;
I've not tried it in anger on my database, but it seems to do the job.
To use it, just run a SELECT
statement like this:
select BatchIndex('public', f_table_name, f_geometry_column) from geometry_columns where f_table_schema = 'public';
To create indexes on all geometry columns, you can use it like this:
select BatchIndex(f_table_schema, f_table_name, f_geometry_column) from geometry_columns;
Afterwards, run a VACUUM ANALYZE
to tidy everything up.
say your table is 'building', you can index using GIST
CREATE INDEX building_gindx ON building USING GIST (geom);
Is that what you are looking for?
The top answer won't work if you have views with geometry. Changing the 'IF' statement to check that you're not trying to build a index on a view solves that issue. If you do wish to use views with geometry, replace this line:
IF i_exists = 0
with this:
IF i_exists = 0 AND tn IN (SELECT table_name, table_type FROM information_schema.tables WHERE table_type = 'BASE TABLE')