[postgis-users] Partitioning - Create statements partitioning

[Bernhard Reimar Hoefle]

Hi Arnaud!
I'm interested in your last post on the PostGIS mailing list. Could you
please post to the mailing list how you created the tables and
indices for the partitioning.

I would like to partition a huge table with point geometries (>tens of millions)
with two geometry columns.
The check constraint should be a spatial query on a polygon where all points of
a "slave" table belong to. But the polygons can also overlap.
Can anyone suggest how to install such a table structure?

I want to try the following:

CREATE TABLE master (
	id serial,
) WITHOUT OIDS;

SELECT AddGeometryColumn('public','master','geom_first',-1,'POINT',3);
SELECT AddGeometryColumn('public','master','geom_last',-1,'POINT',3);

CREATE TABLE slave1 ( ) INHERITS (master);

INSERT INTO geometry_columns
values('','public','slave1','geom_first',3,-1,'POINT');
INSERT INTO geometry_columns
values('','public','slave1','geom_last',3,-1,'POINT');
ALTER TABLE slave1 ADD PRIMARY KEY(id);
CREATE INDEX slave1_geom_first on slave1 USING GIST (geom_first
gist_geometry_ops);
CREATE INDEX slave1_geom_last on slave1 USING GIST (geom_last
gist_geometry_ops);

ALTER TABLE slave1 ADD CHECK(geom_first && 'POLYGON((32636721.9110775
5191166.84296152,32636275.2935075 5191560.08108307,32634869.5543309
5189963.52220268,32635316.1719008 5189570.28408112,32636721.9110775
5191166.84296152))'::GEOMETRY or geom_last && 'POLYGON((32636721.9110775
5191166.84296152,32636275.2935075 5191560.08108307,32634869.5543309
5189963.52220268,32635316.1719008 5189570.28408112,32636721.9110775
5191166.84296152))'::GEOMETRY);

...
CREATE TABLE slaveXX ( ) INHERITS (master);
...

A scan should only be done on tables where the requested spatial query (on
geom_first or geom_last) overlaps with the bounding box of the polygon. Better
would be if it overlaps with the polygon but I don't know if it reduces
performance. I want to test it.

Bernhard