[postgis-devel] partitioning based on spatial feature

[Benjamin Diedrichsen]

Hi,

although I am not involved in postgis development, I have a question 
that I think requires deeper implementation knowledge to be answered. I 
posted this issue to the users mailing list but did not receive an 
answer yet, so I hope you don't mind that I (mis-)use this mailing list 
to make use of some developer knowledge....

I am currently playing around with postgis and its ability to handle 
large tables with more then 1 mio. data rows. My test scenario consists 
of a table that stores tracks in form of simple linestrings. Issueing a 
nearest neighbour search, using a bounding box comparison and a spatial 
index, took quite a while (> 10 sec, with 750.000 rows of data). 
Therefore, I tried to use partitioning based on the location of the 
track (basically I devided the area of interest into two sections) to 
reduce the amount of data to be searched. The problem is that apparently 
constraint_exclusion is not fully supported (or not at all supported) 
for the spatial operators. The query plan clearly shows that still all 
partitions are scanned although the information in the table constraints 
and the where clause of the query suffice for exclusion of one of the 
partitions. Does anyone know to which extent if at all 
constraint_exclusion is supported or might point me to some further 
documentation? Maybe even someone knows if this feature is going to be 
supported with next release of postgis/postgres?

Here are the table and query defs:

CREATE TABLE tracks
(
  id integer,
  track geometry,
  CONSTRAINT enforce_dims_track CHECK (ndims(track) = 2),
  CONSTRAINT enforce_geotype_track CHECK (geometrytype(track) = 
'LINESTRING'::text OR track IS NULL),
  CONSTRAINT enforce_srid_track CHECK (srid(track) = 32632)
)
WITH (OIDS=FALSE);
ALTER TABLE tracks OWNER TO postgres;

CREATE TABLE tracks_north
(
-- Vererbt:   id integer,
-- Vererbt:   track geometry,
  CONSTRAINT below_border CHECK (track |>> transform(geomfromtext('
LINESTRING ( 9.5 55, 9.71667 55)'::text, 4326), 32632)),
  CONSTRAINT enforce_dims_track CHECK (ndims(track) = 2),
  CONSTRAINT enforce_geotype_track CHECK (geometrytype(track) = 
'LINESTRING'::text OR track IS NULL),
  CONSTRAINT enforce_srid_track CHECK (srid(track) = 32632)
)
INHERITS (tracks)
WITH (OIDS=FALSE);
ALTER TABLE tracks_south OWNER TO postgres;

CREATE TABLE tracks_south
(
-- Vererbt:   id integer,
-- Vererbt:   track geometry,
  CONSTRAINT enforce_dims_track CHECK (ndims(track) = 2),
  CONSTRAINT enforce_geotype_track CHECK (geometrytype(track) = 
'LINESTRING'::text OR track IS NULL),
  CONSTRAINT enforce_srid_track CHECK (srid(track) = 32632),
  CONSTRAINT tracks_north_track_check CHECK (track <<| 
transform(geomfromtext('LINESTRING ( 9.5 55, 9.71667 55)'::text, 4326), 
32632))
)
INHERITS (tracks)
WITH (OIDS=FALSE);
ALTER TABLE tracks_north OWNER TO postgres;


This query still scans both partitions although all tracks below the 
given line in the query clearly cannot be above the line from the 
constraint of table tracks_north

select * from tracks where track <<| transform(geomfromtext('LINESTRING( 
9.5 54, 9.71667 54)'::text, 4326), 32632)

Any ideas and comments, greatly appreciated here. Thnx.

Regards,
Benni