[postgis-users] partitioning based on spatial feature

[Benjamin Diedrichsen]

Hi,

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090204/8dceb9c2/attachment.html>