One of the big changes in PostGIS 2.0 was that the geometry_columns table became a read only view. This change was not without sacrifice. On the plus
it meant a table no longer needed to be maintained which was a relief for those who had to constantly ensure the sideline table was up to date. Also on the plus it meant we could base the views on the current user so that if a user connected, they would only see tables they had access to. The sacrifice made was a bit of speed. In most use cases, the speed difference is hardly noticeable
and only noticeable if you have a query constantly polling this table. However if you have a database of 15,000 geometry tables it could take as long as 450ms as noted in this ticket.
So just playing with ideas, could the new PostgreSQL 9.3 event triggers and materialized views provide a solution. Keep in mind this is just food
for thought. We're not condoning people go out and hack their PostGIS install. We suspect if we implement such a thing in PostGIS core it will change from what we propose here.
If you saw our prior article Caching data with materialized views and statement level triggers you know where this is going. We'll add yet another concept to this cocktail and that is what we'll call schema hiding which by that we mean counting on the order of a search_path to hide a named table/view you don't want.
Step 1: Install PostGIS in its own schema
Step one is to install PostGIS in its own schema and make sure it comes after the public schema. This insures that if you do for some reason create
a geometry_columns view or table or what have you in public, it will be used before the postgis packaged one.
CREATE SCHEMA postgis;
CREATE EXTENSION postgis SCHEMA postgis;
ALTER DATABASE test_gis SET search_path ="$user",public,postgis;
Step 2: Create materialized geometry_columns
Now we'll create a geometry_columns materialized view in the public scheam. Note that because in step 1 we set the search path
such that the geometry_columns view provide by postgis would be used after one in the user's schema or public schema. So now we are in position
to put forth an alternative geometry_columns and one that will be used instead of the installed one.
The view we build is almost identical to the one provided with postgis except we took out the logic that limits the list of tables to just those the current user has select rights to. We don't want the materialized view to be user dependent.
For this magic step, we are going to define a data definition language trigger (DDL) that refreshes our view whenever a new table is created
or altered or we add constraints. We ned the constraint one to catch constraint based geometry tables.
CREATEORREPLACEFUNCTIONtrig_refresh_geometry_columns() RETURNS event_trigger AS $$
BEGINIF tg_tag IN('CREATETABLE','CREATETABLEAS'
, 'CREATEFOREIGHTABLE', 'DROPTABLE', 'ALTERTABLE'
, 'CREATEVIEW', 'ALTERVIEW')THENREFRESH MATERIALIZED VIEWpublic.geometry_columns;
$$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER trig_01_refresh_geometry_columns ON ddl_command_end
Step 4: Test Drive
CREATETABLEtestgeom(gid serial primary key, geom geometry(POINT,4326));
ALTERTABLE testgeom ADD COLUMN geom2 geometry(POLYGON, 4326);
SELECT f_table_name As tname, f_geometry_column As gc, srid, typeFROMpublic.geometry_columns;
DECLARE i int;
BEGINFOR i IN1..1000LOOPEXECUTE'CREATETABLEgtest'|| i::text ||'(gidserialprimarykey,geomgeometry(POINT,4326))';
If run with the trigger in place, it takes 63062 ms (a little over a minute to finish). Without the trigger it takes 3,831ms ~ 3seconds). This is because each call to CREATE TABLE causes a REFRESH VIEW. So moral of that story, if you plan to
create 1000 tables in one go, then disable the trigger and manually refresh the view yourself.
Now to test speed of querying. Since we put the public first in our search_path, when we connect to database, the public.geometry_columns one will be used if we don't schema qualify.
--thisusestheoneinpublic-takes11msSELECT srid, type, count(*)as tot
--this uses the one in postgis takes 61 msSELECT srid, type, count(*)as tot