[postgis-users] Fwd: Partitionning using geometry
Rémi Cura
remi.cura at gmail.com
Thu Apr 2 02:09:12 PDT 2015
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.ribot at 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.cura at 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 <svm at 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-bounces at lists.osgeo.org <
>>> postgis-users-bounces at lists.osgeo.org> on behalf of Rémi Cura <
>>> remi.cura at 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 at lists.osgeo.org
>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150402/6a5a301a/attachment.html>
More information about the postgis-users
mailing list