[postgis-users] Fwd: Partitionning using geometry
Nicolas Ribot
nicolas.ribot at gmail.com
Thu Apr 2 02:22:18 PDT 2015
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.cura at 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.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
>>
>
>
> _______________________________________________
> 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/d96afb40/attachment.html>
More information about the postgis-users
mailing list