[postgis-users] Fwd: Partitionning using geometry
Nicolas Ribot
nicolas.ribot at gmail.com
Thu Apr 2 01:59:00 PDT 2015
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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150402/e4571d90/attachment.html>
More information about the postgis-users
mailing list