Partitionning using geometry

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);
------

Browse pgsql-general by date

  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