Yes, hope I only understand now, by using anonymous code block to provide WHERE clauses with constant values...
Some months ago I found this constant-value limitation too strong and did not use partitionning... ;) Nicolas On 2 April 2015 at 11:09, Rémi Cura <remi.c...@gmail.com> wrote: > 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 >
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users