From: | Rémi Cura <remi(dot)cura(at)gmail(dot)com> |
---|---|
To: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Partitionning using geometry |
Date: | 2015-04-01 15:43:17 |
Message-ID: | CAJvUf_tW49gTU1UeOMEkRv2Ojbp5DEniex=hk=z-a6hjKBwdkQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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);
------
From | Date | Subject | |
---|---|---|---|
Next Message | FarjadFarid(ChkNet) | 2015-04-01 16:13:47 | Re: Would like to know how analyze works technically |
Previous Message | Adrian Klaver | 2015-04-01 15:23:42 | Re: stack builder |