Hey dear list,
I'd like to partition geographical (geometry) data with postgres mechanism.
(my usage is in fact related to pointcloud, but I use geometry as a work
around)
>From example I read on constraint, nothing should prevent it from working
Here is a self contained example, the planner doesn"t seems to use the
constraint_exclusion mechanism, whatever the constraint
Thanks,
Cheers,
Rémi-C
------
CREATE SCHEMA IF NOT EXISTS test_partitionning;
SET search_path TO test_partitionning, public ;
DROP TABLE IF EXISTS test_father CASCADE;
CREATE TABLE test_father (
gid SERIAL PRIMARY KEY
, geom geometry
);
create table test_child_1 (
check (geometry_overlaps(geom,ST_Expand(ST_MakePoint(10,10),10 ) ) )
,check ( geom&&ST_Expand(ST_MakePoint(10,10),10 ) )
, CHECK (ST_X(geom) BETWEEN 0 AND 20)
, CHECK (ST_Y(geom) BETWEEN 0 AND 20)
, CHECK ( ST_Intersects(geom, ST_Expand(ST_MakePoint(10,10),10 )) )
) inherits (test_father);
--CREATE INDEX ON test_child_1 USING GIST(geom);
create table test_child_2 (
check (geometry_overlaps(geom,ST_Expand(ST_MakePoint(30,10),10 ) ) )
,check ( geom&&ST_Expand(ST_MakePoint(30,10),10 ) )
, CHECK (ST_X(geom) BETWEEN 20 AND 40)
, CHECK (ST_Y(geom) BETWEEN 0 AND 20)
, CHECK ( ST_Intersects(geom, ST_Expand(ST_MakePoint(30,10),10 )) )
) inherits (test_father);
--CREATE INDEX ON test_child_2 USING GIST(geom);
INSERT INTO test_child_1 (geom)
SELECT ST_MakePoint(s1/10.0+random(),s2/10.0+random())
FROM generate_series(1,90) AS s1, generate_series(1,90) AS s2;
INSERT INTO test_child_2 (geom)
SELECT ST_MakePoint(s1/10.0+random(),s2/10.0+random())
FROM generate_series(200,300) AS s1, generate_series(1,90) AS s2;
SHOW constraint_exclusion;
SET constraint_exclusion TO partition;
WITH area_of_interest AS (
SELECT ST_Buffer(ST_MakePoint(5,5),1) as buf
)
SELECT *
FROM area_of_interest, test_father
WHERE -- geom && buf
ST_X(geom) BETWEEN ST_XMin(buf) AND ST_Xmax(buf)
AND ST_Y(geom) BETWEEN ST_YMin(buf) AND ST_Ymax(buf) ;
SELECT *
FROM test_father , ST_Buffer(ST_MakePoint(5,5),1) as buf
WHERE
ST_X(geom) BETWEEN ST_XMin(buf) AND ST_Xmax(buf)
AND ST_Y(geom) BETWEEN ST_YMin(buf) AND ST_Ymax(buf);
------
