simplymap_thematic=# \d+ geometry_columns View "public.geometry_columns" Column | Type | Modifiers | Storage | Description -------------------+------------------------+-----------+----------+------------- f_table_catalog | character varying(256) | | extended | f_table_schema | character varying(256) | | extended | f_table_name | character varying(256) | | extended | f_geometry_column | character varying(256) | | extended | coord_dimension | integer | | plain | srid | integer | | plain | type | character varying(30) | | extended | View definition: SELECT current_database()::character varying(256) AS f_table_catalog, n.nspname::character varying(256) AS f_table_schema, c.relname::character varying(256) AS f_table_name, a.attname::character varying(256) AS f_geometry_column, COALESCE(NULLIF(postgis_typmod_dims(a.atttypmod), 2), postgis_constraint_dims(n.nspname::text, c.relname::text, a.attname::text), 2) AS coord_dimension, COALESCE(NULLIF(postgis_typmod_srid(a.atttypmod), 0), postgis_constraint_srid(n.nspname::text, c.relname::text, a.attname::text), 0) AS srid, replace(replace(COALESCE(NULLIF(upper(postgis_typmod_type(a.atttypmod)), 'GEOMETRY'::text), postgis_constraint_type(n.nspname::text, c.relname::text, a.attname::text)::text, 'GEOMETRY'::text), 'ZM'::text, ''::text), 'Z'::text, ''::text)::character varying(30) AS type FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n WHERE t.typname = 'geometry'::name AND a.attisdropped = false AND a.atttypid = t.oid AND a.attrelid = c.oid AND c.relnamespace = n.oid AND (c.relkind = 'r'::"char" OR c.relkind = 'v'::"char" OR c.relkind = 'm'::"char" OR c.relkind = 'f'::"char") AND NOT pg_is_other_temp_schema(c.relnamespace) AND NOT (n.nspname = 'public'::name AND c.relname = 'raster_columns'::name) AND has_table_privilege(c.oid, 'SELECT'::text); Rules: geometry_columns_delete AS ON DELETE TO geometry_columns DO INSTEAD NOTHING geometry_columns_insert AS ON INSERT TO geometry_columns DO INSTEAD NOTHING geometry_columns_update AS ON UPDATE TO geometry_columns DO INSTEAD NOTHING simplymap_thematic=# EXPLAIN ANALYZE SELECT * FROM geometry_columns; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------- Nested Loop (cost=2.37..278359.57 rows=1 width=196) (actual time=18298.975..69196.229 rows=740 loops=1) Join Filter: ((c.relnamespace = n.oid) AND ((n.nspname <> 'public'::name) OR (c.relname <> 'raster_columns'::name))) Rows Removed by Join Filter: 28900 -> Nested Loop (cost=2.37..278358.26 rows=1 width=136) (actual time=18285.087..64394.662 rows=741 loops=1) -> Hash Join (cost=2.29..278342.54 rows=146 width=72) (actual time=18285.030..64375.789 rows=745 loops=1) Hash Cond: (a.atttypid = t.oid) -> Seq Scan on pg_attribute a (cost=0.00..270512.15 rows=10437075 width=76) (actual time=0.005..35956.111 rows=11255363 loops=1) Filter: (NOT attisdropped) -> Hash (cost=2.29..2.29 rows=1 width=4) (actual time=0.017..0.017 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB -> Index Scan using pg_type_typname_nsp_index on pg_type t (cost=0.08..2.29 rows=1 width=4) (actual time=0.010..0.012 rows=1 loops=1) Index Cond: (typname = 'geometry'::name) -> Index Scan using pg_class_oid_index on pg_class c (cost=0.09..0.11 rows=1 width=72) (actual time=0.011..0.014 rows=1 loops=745) Index Cond: (oid = a.attrelid) Filter: ((NOT pg_is_other_temp_schema(relnamespace)) AND has_table_privilege(oid, 'SELECT'::text) AND ((relkind = 'r'::"char") OR (relkind = 'v'::"char") OR (relkind = 'm'::"char") OR ( relkind = 'f'::"char"))) Rows Removed by Filter: 0 -> Seq Scan on pg_namespace n (cost=0.00..1.06 rows=28 width=68) (actual time=0.003..0.112 rows=40 loops=741) Total runtime: 69197.337 ms (18 rows) simplymap_thematic=# ALTER VIEW geometry_columns RENAME TO geometry_columns_old; ALTER VIEW simplymap_thematic=# CREATE MATERIALIZED VIEW geometry_columns AS SELECT current_database()::varchar(256) AS f_table_catalog, n.nspname::varchar(256) AS f_table_schema, c.relname::varchar(256) AS f_table_name, a.attname::varchar(256) AS f_geometry_column, COALESCE(NULLIF(postgis_typmod_dims(a.atttypmod),2), postgis_constraint_dims(n.nspname, c.relname, a.attname), 2) AS coord_dimension, COALESCE(NULLIF(postgis_typmod_srid(a.atttypmod),0), postgis_constraint_srid(n.nspname, c.relname, a.attname), 0) AS srid, -- force to be uppercase with no ZM so is backwards compatible -- with old geometry_columns replace( replace( COALESCE( NULLIF(upper(postgis_typmod_type(a.atttypmod)::text), 'GEOMETRY'), postgis_constraint_type(n.nspname, c.relname, a.attname), 'GEOMETRY' ), 'ZM', '' ), 'Z', '' )::varchar(30) AS type FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n WHERE t.typname = 'geometry'::name AND a.attisdropped = false AND a.atttypid = t.oid AND a.attrelid = c.oid AND c.relnamespace = n.oid AND (c.relkind = 'r'::"char" OR c.relkind = 'v'::"char" OR c.relkind = 'm'::"char") AND NOT pg_is_other_temp_schema(c.relnamespace) AND NOT ( n.nspname = 'public' AND c.relname = 'raster_columns' ) AND has_table_privilege( c.oid, 'SELECT'::text ); SELECT 740 simplymap_thematic=# EXPLAIN ANALYZE SELECT * FROM geometry_columns; QUERY PLAN ----------------------------------------------------------------------------------------------------------------- Seq Scan on geometry_columns (cost=0.00..12.07 rows=36 width=2150) (actual time=0.005..0.774 rows=740 loops=1) Total runtime: 1.523 ms (2 rows)