Hey Nicolas, great answer.
At least it gives some hope, because it is possible to compute value in plpgsql function and create on the fly a querry with those values hard written. I still don't know if it would work with geometry tough Thanks, Rémi-C 2015-04-02 10:59 GMT+02:00 Nicolas Ribot <nicolas.ri...@gmail.com>: > Hi Remy, > > As far as I understood table partitionning has one limiting caveat for > some usages (from: > http://www.postgresql.org/docs/9.4/static/ddl-partitioning.html): > > "Constraint exclusion only works when the query's WHERE clause contains > constants (or externally supplied parameters). For example, a comparison > against a non-immutable function such as CURRENT_TIMESTAMP cannot be > optimized, since the planner cannot know which partition the function value > might fall into at run time." > > Here are the plans of your query using function for check constraint, then > using constant values: > The second plan shows partionning is used, not in the first: > > -- fonctions as check parameters: > explain 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); > > "Nested Loop (cost=0.00..1206.78 rows=219 width=68)" > " Join Filter: ((st_x(test_father.geom) >= st_xmin((buf.buf)::box3d)) AND > (st_x(test_father.geom) <= st_xmax((buf.buf)::box3d)) AND > (st_y(test_father.geom) >= st_ymin((buf.buf)::box3d)) AND > (st_y(test_father.geom) <= st_ymax((buf.buf)::box3d)))" > " -> Function Scan on buf (cost=0.00..0.01 rows=1 width=32)" > " -> Append (cost=0.00..321.12 rows=17713 width=36)" > " -> Seq Scan on test_father (cost=0.00..0.00 rows=1 width=36)" > " -> Seq Scan on test_child_1 (cost=0.00..151.64 rows=8364 > width=36)" > " -> Seq Scan on test_child_2 (cost=0.00..169.48 rows=9348 > width=36)" > > -- constants as check parameters: > explain SELECT * > FROM test_father , ST_Buffer(ST_MakePoint(5,5),1) as buf > WHERE -- hard coded values > ST_X(geom) BETWEEN 4 and 6 > AND ST_Y(geom) BETWEEN 4 and 6 ; > > "Nested Loop (cost=0.00..318.95 rows=2 width=68)" > " -> Function Scan on buf (cost=0.00..0.01 rows=1 width=32)" > " -> Append (cost=0.00..318.92 rows=2 width=36)" > " -> Seq Scan on test_father (cost=0.00..0.00 rows=1 width=36)" > " Filter: ((st_x(geom) >= 4::double precision) AND > (st_x(geom) <= 6::double precision) AND (st_y(geom) >= 4::double precision) > AND (st_y(geom) <= 6::double precision))" > " -> Seq Scan on test_child_1 (cost=0.00..318.92 rows=1 width=36)" > " Filter: ((st_x(geom) >= 4::double precision) AND > (st_x(geom) <= 6::double precision) AND (st_y(geom) >= 4::double precision) > AND (st_y(geom) <= 6::double precision))" > > The test_child2 table is excluded in this plan. > > Nicolas > > On 2 April 2015 at 10:12, Rémi Cura <remi.c...@gmail.com> wrote: > >> >> In theory, they use check for partitionning. >> It is possible (but inneficient) that check will overlaps (they give one >> such example in doc). >> Thus you can partition into tables that may have some common space. >> >> >> This is the theory. >> I don't understand why it doesn't work, and I got no answers from >> postgres mailing list. >> >> Cheers, >> Rémi-C >> >> 2015-04-01 23:01 GMT+02:00 Stephen V. Mather <s...@clevelandmetroparks.com >> >: >> >>> Hi, >>> >>> >>> Can you reliably partition with anything other than points? I thought >>> Postgres hadn't implemented ranges appropriately for the inevitable >>> linestring or polygon that crosses a partition boundary. (Fuzzy 2-year-old >>> memories, so perhaps something has changed...). >>> >>> >>> Cheers, >>> >>> Best, >>> >>> Steve >>> >>> >>> [image: http://sig.cmparks.net/cmp-ms-90x122.png] *Stephen V. >>> Mather* >>> GIS Manager >>> (216) 635-3243 (Work) >>> clevelandmetroparks.com <http://www.clemetparks.com> >>> >>> >>> >>> >>> ------------------------------ >>> *From:* postgis-users-boun...@lists.osgeo.org < >>> postgis-users-boun...@lists.osgeo.org> on behalf of Rémi Cura < >>> remi.c...@gmail.com> >>> *Sent:* Wednesday, April 1, 2015 12:03 PM >>> *To:* PostGIS Users Discussion >>> *Subject:* [postgis-users] Fwd: Partitionning using geometry >>> >>> (cross-post from postgres list) >>> 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); >>> ------ >>> >>> >>> _______________________________________________ >>> postgis-users mailing list >>> postgis-users@lists.osgeo.org >>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>> >> >> >> _______________________________________________ >> postgis-users mailing list >> postgis-users@lists.osgeo.org >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> > > > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users