<div dir="ltr"><div><div><div><div>Hey Nicolas,<br><br></div>great answer.<br><br></div>At least it gives some hope, <br>because it is possible to compute value in plpgsql function and create on the fly a querry with those values hard written.<br><br></div><div>I still don't know if it would work with geometry tough<br></div><div><br></div>Thanks,<br><br></div>Rémi-C<br></div><div class="gmail_extra"><br><div class="gmail_quote">2015-04-02 10:59 GMT+02:00 Nicolas Ribot <span dir="ltr"><<a href="mailto:nicolas.ribot@gmail.com" target="_blank">nicolas.ribot@gmail.com</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Hi Remy,<div><br>As far as I understood table partitionning has one limiting caveat for some usages (from: <a href="http://www.postgresql.org/docs/9.4/static/ddl-partitioning.html" target="_blank">http://www.postgresql.org/docs/9.4/static/ddl-partitioning.html</a>):</div><div><br></div><div>"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."</div><div><br></div><div>Here are the plans of your query using function for check constraint, then using constant values:</div><div>The second plan shows partionning is used, not in the first:</div><div><br></div><div>-- fonctions as check parameters:</div><div><div>explain SELECT *</div><span class=""><div>FROM test_father , ST_Buffer(ST_MakePoint(5,5),1) as buf</div><div>WHERE</div><div> ST_X(geom) BETWEEN ST_XMin(buf) AND ST_Xmax(buf)</div><div> AND ST_Y(geom) BETWEEN ST_YMin(buf) AND ST_Ymax(buf);</div></span></div><div><br></div><div><div>"Nested Loop (cost=0.00..1206.78 rows=219 width=68)"</div><div>" 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)))"</div><div>" -> Function Scan on buf (cost=0.00..0.01 rows=1 width=32)"</div><div>" -> Append (cost=0.00..321.12 rows=17713 width=36)"</div><div>" -> Seq Scan on test_father (cost=0.00..0.00 rows=1 width=36)"</div><div>" -> Seq Scan on test_child_1 (cost=0.00..151.64 rows=8364 width=36)"</div><div>" -> Seq Scan on test_child_2 (cost=0.00..169.48 rows=9348 width=36)"</div></div><div><br></div><div>-- constants as check parameters:</div><div><div>explain SELECT *</div><span class=""><div>FROM test_father , ST_Buffer(ST_MakePoint(5,5),1) as buf</div></span><div>WHERE -- hard coded values</div><div> ST_X(geom) BETWEEN 4 and 6</div><div> AND ST_Y(geom) BETWEEN 4 and 6 ;</div></div><div><br></div><div><div>"Nested Loop (cost=0.00..318.95 rows=2 width=68)"</div><div>" -> Function Scan on buf (cost=0.00..0.01 rows=1 width=32)"</div><div>" -> Append (cost=0.00..318.92 rows=2 width=36)"</div><div>" -> Seq Scan on test_father (cost=0.00..0.00 rows=1 width=36)"</div><div>" 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))"</div><div>" -> Seq Scan on test_child_1 (cost=0.00..318.92 rows=1 width=36)"</div><div>" 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))"</div></div><div><br></div><div>The test_child2 table is excluded in this plan.</div><span class="HOEnZb"><font color="#888888"><div><br></div><div>Nicolas</div></font></span></div><div class="HOEnZb"><div class="h5"><div class="gmail_extra"><br><div class="gmail_quote">On 2 April 2015 at 10:12, Rémi Cura <span dir="ltr"><<a href="mailto:remi.cura@gmail.com" target="_blank">remi.cura@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div><div><div><div><div><br>In theory, they use check for partitionning. <br></div>It is possible (but inneficient) that check will overlaps (they give one such example in doc).<br></div><div>Thus you can partition into tables that may have some common space.<br><br></div><div><br></div>This is the theory.<br></div>I don't understand why it doesn't work, and I got no answers from postgres mailing list.<br><br></div>Cheers,<br></div>Rémi-C<br></div><div class="gmail_extra"><br><div class="gmail_quote"><div><div>2015-04-01 23:01 GMT+02:00 Stephen V. Mather <span dir="ltr"><<a href="mailto:svm@clevelandmetroparks.com" target="_blank">svm@clevelandmetroparks.com</a>></span>:<br></div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div>
<div dir="ltr">
<div style="font-size:12pt;color:#000000;background-color:#ffffff;font-family:Calibri,Arial,Helvetica,sans-serif">
<p style="margin-top:0px;margin-bottom:0px">Hi,<br>
</p>
<p style="margin-top:0px;margin-bottom:0px"><br>
</p>
<p style="margin-top:0px;margin-bottom:0px">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...).<br>
</p>
<p style="margin-top:0px;margin-bottom:0px"><br>
</p>
<p style="margin-top:0px;margin-bottom:0px">Cheers,<br>
</p>
<p style="margin-top:0px;margin-bottom:0px">Best,<br>
</p>
<p style="margin-top:0px;margin-bottom:0px">Steve<br>
</p>
<p style="margin-top:0px;margin-bottom:0px"><br>
</p>
<div>
<div style="font-size:12pt;color:#000000;background-color:#ffffff;font-family:Calibri,Arial,Helvetica,sans-serif">
<div style="font-family:Tahoma;font-size:13px">
<div style="font-family:Tahoma;font-size:13px">
<div style="font-family:Tahoma;font-size:13px">
<div>
<p class="MsoNormal" style="margin-top:0px;margin-bottom:0px"><img alt="http://sig.cmparks.net/cmp-ms-90x122.png" src="http://sig.cmparks.net/cmp-ms-90x122.png" align="left" height="122" hspace="12" width="90">
<span style="font-size:14pt;color:rgb(0,108,86);font-family:Helvetica,sans-serif">
<span style="font-family:Helvetica,sans-serif"><strong>Stephen V. Mather</strong></span><br style="font-family:Helvetica,sans-serif">
<span style="font-family:Helvetica,sans-serif"></span></span><span style="font-size:11pt;color:rgb(0,108,86);font-family:Helvetica,sans-serif"><span style="font-family:Helvetica,sans-serif">GIS Manager</span><br style="font-family:Helvetica,sans-serif">
<span style="font-family:Helvetica,sans-serif"></span></span><span style="font-size:9pt;color:rgb(0,108,86);font-family:Helvetica,sans-serif"><span style="font-family:Helvetica,sans-serif"><a href="tel:%28216%29%20635-3243" value="+12166353243" target="_blank">(216) 635-3243</a> (Work)
</span></span><span style="font-size:11pt;color:rgb(31,73,125);font-family:Helvetica,sans-serif"><a href="http://www.clemetparks.com" target="_blank"><span style="font-family:Helvetica,sans-serif"><br style="font-family:Helvetica,sans-serif">
<span style="font-family:Helvetica,sans-serif">clevelandmetroparks.com</span></span></a></span></p>
<p class="MsoNormal" style="margin-top:0px;margin-bottom:0px"></p>
<br>
<br>
<br>
</div>
</div>
<div></div>
</div>
</div>
</div>
</div>
<div style="color:rgb(33,33,33)">
<hr style="display:inline-block;width:98%">
<div dir="ltr"><font style="font-size:11pt" color="#000000" face="Calibri, sans-serif"><b>From:</b> <a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a> <<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>> on behalf of Rémi Cura <<a href="mailto:remi.cura@gmail.com" target="_blank">remi.cura@gmail.com</a>><br>
<b>Sent:</b> Wednesday, April 1, 2015 12:03 PM<br>
<b>To:</b> PostGIS Users Discussion<br>
<b>Subject:</b> [postgis-users] Fwd: Partitionning using geometry</font>
<div> </div>
</div><div><div>
<div>
<div dir="ltr">(cross-post from postgres list)<br>
<div>
<div class="gmail_quote">
<div dir="ltr">
<div>
<div>
<div>
<div>
<div>
<div>Hey dear list,<br>
<br>
</div>
I'd like to partition geographical (geometry) data with postgres mechanism.<br>
</div>
<div>(my usage is in fact related to pointcloud, but I use geometry as a work around)<br>
</div>
>From example I read on constraint, nothing should prevent it from working<br>
</div>
Here is a self contained example, the planner doesn"t seems to use the constraint_exclusion mechanism, whatever the constraint<br>
<br>
</div>
Thanks, <br>
</div>
Cheers,<br>
</div>
Rémi-C<br>
<div>
<div>
<div>
<div>
<div>
<div><br>
------<br>
<br>
CREATE SCHEMA IF NOT EXISTS test_partitionning;<br>
SET search_path TO test_partitionning, public ;<br>
<br>
DROP TABLE IF EXISTS test_father CASCADE;<br>
CREATE TABLE test_father (<br>
gid SERIAL PRIMARY KEY<br>
, geom geometry<br>
); <br>
<br>
create table test_child_1 (<br>
check (geometry_overlaps(geom,ST_Expand(ST_MakePoint(10,10),10 ) ) )<br>
,check ( geom&&ST_Expand(ST_MakePoint(10,10),10 ) ) <br>
, CHECK (ST_X(geom) BETWEEN 0 AND 20)<br>
, CHECK (ST_Y(geom) BETWEEN 0 AND 20)<br>
, CHECK ( ST_Intersects(geom, ST_Expand(ST_MakePoint(10,10),10 )) )<br>
) inherits (test_father);<br>
--CREATE INDEX ON test_child_1 USING GIST(geom);<br>
<br>
create table test_child_2 (<br>
check (geometry_overlaps(geom,ST_Expand(ST_MakePoint(30,10),10 ) ) )<br>
,check ( geom&&ST_Expand(ST_MakePoint(30,10),10 ) ) <br>
, CHECK (ST_X(geom) BETWEEN 20 AND 40)<br>
, CHECK (ST_Y(geom) BETWEEN 0 AND 20)<br>
, CHECK ( ST_Intersects(geom, ST_Expand(ST_MakePoint(30,10),10 )) )<br>
) inherits (test_father);<br>
--CREATE INDEX ON test_child_2 USING GIST(geom);<br>
<br>
<br>
INSERT INTO test_child_1 (geom)<br>
SELECT ST_MakePoint(s1/10.0+random(),s2/10.0+random())<br>
FROM generate_series(1,90) AS s1, generate_series(1,90) AS s2;<br>
<br>
INSERT INTO test_child_2 (geom)<br>
SELECT ST_MakePoint(s1/10.0+random(),s2/10.0+random())<br>
FROM generate_series(200,300) AS s1, generate_series(1,90) AS s2;<br>
<br>
<br>
SHOW constraint_exclusion;<br>
SET constraint_exclusion TO partition;<br>
<br>
<br>
WITH area_of_interest AS (<br>
SELECT ST_Buffer(ST_MakePoint(5,5),1) as buf<br>
)<br>
SELECT *<br>
FROM area_of_interest, test_father <br>
WHERE -- geom && buf <br>
ST_X(geom) BETWEEN ST_XMin(buf) AND ST_Xmax(buf)<br>
AND ST_Y(geom) BETWEEN ST_YMin(buf) AND ST_Ymax(buf) ;<br>
<br>
<br>
SELECT *<br>
FROM test_father , ST_Buffer(ST_MakePoint(5,5),1) as buf<br>
WHERE <br>
ST_X(geom) BETWEEN ST_XMin(buf) AND ST_Xmax(buf)<br>
AND ST_Y(geom) BETWEEN ST_YMin(buf) AND ST_Ymax(buf);<br>
------<br>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<br>
</div>
</div>
</div>
</div></div></div>
</div>
</div>
<br></div></div><span>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></span></blockquote></div><br></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>