<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Thank you Bborie.<br>
<br>
Very nice of you to reply. I got the idea for spatial constraints on
partitioned tables from the PostGIS Cookbook (chapter 2, Extending
inheritance - table partitioning), of which you are co-author. The
example in that chapter uses spatial functions in the CHECK clause.
Does the inclusion in that book mean that there are scenarios in
which spatial constraints do work in combination with constraint
exclusion?<br>
<br>
I tried your suggestion, using a helper table with extents, and
unfortunately the JOIN does not result in constraint exclusion, it
still scans all indexes. Explicitly using the extent identifier in a
WHERE clause does optimize the query (see below). It seems
PostgreSQL is a bit sensitive about constraint exclusion. I might
look at writing a custom function in order to achieve our goal.<br>
<br>
Best regards,<br>
Rubio<br>
<br>
<tt>--------------------------------------------------------------------------------</tt><tt><br>
EXPLAIN<br>
WITH units AS (<br>
SELECT unit<br>
FROM ahn3_pointcloud.ahn_units<br>
WHERE ST_Intersects(geom,
ST_MakeEnvelope(120740,486076,121074,486292, 28992))<br>
)<br>
SELECT * FROM ahn3_pointcloud.ahn3_all a<br>
JOIN units u ON a.ahn_unit = u.unit<br>
LIMIT 10<br>
<br>
"Limit (cost=8.45..63.49 rows=10 width=66)"<br>
" CTE units"<br>
" -> Index Scan using ahn_units_geom_idx on ahn_units
(cost=0.14.<snip><br>
" Index Cond: (geom &&
'0103000020407100000100000005000000000</tt><tt><tt><snip></tt><br>
" Filter: _st_intersects(geom,
'01030000204071000001000000050</tt><tt><tt><tt><snip></tt></tt><br>
" -> Hash Join (cost=0.03..122289.50 rows=22216 width=66)"<br>
" Hash Cond: ((a.ahn_unit)::text = (u.unit)::text)"<br>
" -> Append (cost=0.00..105405.22 rows=4443223
width=42)"<br>
" -> Seq Scan on ahn3_all a (cost=0.00..0.00
rows=1 wid</tt><tt><tt><tt><snip></tt></tt><br>
" -> Seq Scan on c_25gn1 a_1
(cost=0.00..20843.10 rows=</tt><tt><tt><tt><snip></tt></tt><br>
" -> Seq Scan on c_30fz1 a_2
(cost=0.00..27248.34 rows=</tt><tt><tt><tt><snip></tt></tt><br>
" -> Seq Scan on c_30fz2 a_3
(cost=0.00..24983.57 rows=</tt><tt><tt><tt><snip></tt></tt><br>
" -> Seq Scan on c_25ez1 a_4 (cost=0.00..9141.21
rows=3</tt><tt><tt><tt><snip></tt></tt><br>
" -> Seq Scan on c_25dn2 a_5
(cost=0.00..23189.00 rows=</tt><tt><tt><tt><snip></tt></tt><br>
" -> Hash (cost=0.02..0.02 rows=1 width=24)"<br>
" -> CTE Scan on units u (cost=0.00..0.02 rows=1
width=24)"<br>
<br>
EXPLAIN<br>
WITH units AS (<br>
SELECT unit<br>
FROM ahn3_pointcloud.ahn_units<br>
WHERE ST_Intersects(geom,
ST_MakeEnvelope(120740,486076,121074,486292, 28992))<br>
)<br>
SELECT * FROM ahn3_pointcloud.ahn3_all a<br>
WHERE a.ahn_unit = '25gn1'<br>
LIMIT 10<br>
<br>
"Limit (cost=0.00..0.26 rows=10 width=42)"<br>
" -> Append (cost=0.00..23039.62 rows=878611 width=42)"<br>
" -> Seq Scan on ahn3_all a (cost=0.00..0.00 rows=1
width=60)"<br>
" Filter: ((ahn_unit)::text = '25gn1'::text)"<br>
" -> Seq Scan on c_25gn1 a_1 (cost=0.00..23039.62
rows=878</tt><tt><tt><tt><tt><snip></tt></tt></tt>"<br>
" Filter: ((ahn_unit)::text = '25gn1'::text)"<br>
</tt><tt>--------------------------------------------------------------------------------</tt><tt><br>
</tt><tt>
</tt><br>
<br>
<div class="moz-cite-prefix">On 2-11-2015 18:28, Bborie Park wrote:<br>
</div>
<blockquote
cite="mid:CAKVfRvFb99wPWO9uaAse0q5B_=ONi-5qhR2D5t6co=28JC825Q@mail.gmail.com"
type="cite">
<div dir="ltr">That check constraint is not going to help you as
it is too complicated for the partitioning task. I don't believe
the partitioning constraint can be functional but needs to be
simpler and built upon a basic data type...
<div><br>
</div>
<div>Something like...</div>
<div><br>
</div>
<font face="monospace, monospace">CREATE TABLE max_extent AS (<br>
id integer PRIMARY KEY,<br>
geom geometry(POLYGON)<br>
);</font>
<div><br>
</div>
<div>And then the parent/partition tables had a reference to
pc_extent...</div>
<div><br>
</div>
<font face="monospace, monospace">CREATE TABLE
ahn3_pointcloud.ahn3_all<br>
(<br>
id integer NOT NULL,<br>
pa pcpatch(7),</font>
<div><font face="monospace, monospace"> max_extent_id integer<br>
);</font></div>
<div><br>
</div>
<div>Then your queries become...</div>
<div><br>
</div>
<div><font face="monospace, monospace">WITH max_extents AS (</font></div>
<font face="monospace, monospace">SELECT</font>
<div><font face="monospace, monospace"> id<br>
</font>
<div><font face="monospace, monospace">FROM max_extent<br>
WHERE PC_Intersects(geom,
ST_MakeEnvelope(120740,486076,121074,486292, 28992))</font></div>
<div><font face="monospace, monospace">)</font></div>
<div><font face="monospace, monospace">SELECT<br>
*</font></div>
<div><font face="monospace, monospace">FROM ahn3_all</font></div>
<div><font face="monospace, monospace">JOIN max_extents</font></div>
<div><font face="monospace, monospace">
ON ahn3_all.max_extent_id = <a moz-do-not-send="true"
href="http://max_extents.id">max_extents.id</a></font></div>
<div><span style="font-family:monospace,monospace"><br>
</span></div>
<div>
<div>-bborie</div>
</div>
</div>
<div class="gmail_extra"><br>
<div class="gmail_quote">On Mon, Nov 2, 2015 at 8:45 AM, Rubio
Vaughan <span dir="ltr"><<a moz-do-not-send="true"
href="mailto:rubio.vaughan@geodan.nl" target="_blank">rubio.vaughan@geodan.nl</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0px 0px 0px
0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
<div bgcolor="#FFFFFF" text="#000000"> Dear all,<br>
<br>
We are trying to load a large LIDAR pointcloud dataset
into multiple tables in PostGIS, using the point cloud
extension. Eventually the whole data set will consist of
1400+ tables, containing about 1 million records each.
Using a union view on all these tables would result in
terrible performance, which is why we are trying to
optimize query performance using partitioned tables.
According to the documentation, the use of partitioned
tables with CHECK constraints should cause the query
planner to only scan those tables for which the CHECK
constraint matches. Excerpt from the documentation:<br>
--------------------------------------------------------------------------------<br>
<b>18.7.4. Other Planner Options</b><br>
<tt
style="font-size:1.3em;color:rgb(0,0,0);font-style:normal;font-variant:normal;font-weight:normal;letter-spacing:normal;line-height:normal;text-align:left;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px;background-color:rgb(255,255,255)">constraint_exclusion</tt><span
style="color:rgb(0,0,0);font-family:verdana,sans-serif;font-size:12.16px;font-style:normal;font-variant:normal;font-weight:normal;letter-spacing:normal;line-height:normal;text-align:left;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px;float:none;display:inline!important;background-color:rgb(255,255,255)"><span> </span>(</span><tt
style="font-size:1.3em;color:rgb(0,0,0);font-style:normal;font-variant:normal;font-weight:normal;letter-spacing:normal;line-height:normal;text-align:left;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px;background-color:rgb(255,255,255)">enum</tt><span
style="color:rgb(0,0,0);font-family:verdana,sans-serif;font-size:12.16px;font-style:normal;font-variant:normal;font-weight:normal;letter-spacing:normal;line-height:normal;text-align:left;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px;float:none;display:inline!important;background-color:rgb(255,255,255)">):
Controls the query planner's use of table constraints
to optimize queries. The allowed values of
constraint_exclusion are on (examine constraints for
all tables), off (never examine constraints), and
partition (examine constraints only for inheritance
child tables and UNION ALL subqueries). partition is
the default setting. It is often used with inheritance
and partitioned tables to improve performance. When
this parameter allows it for a particular table, the
planner compares query conditions with the table's
CHECK constraints, and omits scanning tables for which
the conditions contradict the constraints.</span><br>
--------------------------------------------------------------------------------<br>
<br>
However, as you can see from the example query below,
the indexes for all child tables are still scanned. I
would expect the query planner to only scan table
c_25gn1, which contains the queried region. Does anyone
here have experience with partitioned tables? I would be
delighted to get some pointers for figuring out this
problem.<br>
<br>
Thanks in advance for any help!<br>
<br>
Best regards,<br>
Rubio Vaughan<br>
<br>
<b>General info:</b><b><br>
</b><tt>-------------------</tt><tt><tt>----------</tt></tt><tt><tt><tt>----------</tt></tt></tt><tt><tt><tt><tt>----------</tt></tt></tt></tt><tt><tt><tt><tt><tt>----------</tt></tt></tt></tt></tt><tt><tt><tt><tt><tt><tt>----------</tt></tt></tt></tt></tt></tt><tt><tt><tt><tt><tt><tt><tt>----------</tt></tt></tt></tt></tt></tt>-</tt><tt><br>
SELECT postgis_full_version();<br>
"POSTGIS="2.2.0 r14208" GEOS="3.5.0-CAPI-1.9.0 r4084"
SFCGAL="1.2.0" PROJ="Rel. 4.8.0, 6 March 2012"
GDAL="GDAL 2.0.1, released 2015/09/15" LIBXML="2.9.1"
LIBJSON="0.11.99" TOPOLOGY (topology procs from "2.1.4
r12966" need upgrade) RASTER"<br>
<br>
SHOW "constraint_exclusion";<br>
"partition"<br>
</tt><tt>-------------------</tt><tt><tt>----------</tt></tt><tt><tt><tt>----------</tt></tt></tt><tt><tt><tt><tt>----------</tt></tt></tt></tt><tt><tt><tt><tt><tt>----------</tt></tt></tt></tt></tt><tt><tt><tt><tt><tt><tt>----------</tt></tt></tt></tt></tt></tt><tt><tt><tt><tt><tt><tt><tt>----------</tt></tt></tt></tt></tt></tt>-</tt><tt><br>
</tt><br>
<b>The parent table:</b><br>
<tt><tt>-------------------</tt><tt><tt>----------</tt></tt><tt><tt><tt>----------</tt></tt></tt><tt><tt><tt><tt>----------</tt></tt></tt></tt><tt><tt><tt><tt><tt>----------</tt></tt></tt></tt></tt><tt><tt><tt><tt><tt><tt>----------</tt></tt></tt></tt></tt></tt><tt><tt><tt><tt><tt><tt><tt>----------</tt></tt></tt></tt></tt></tt>-</tt><tt><br>
</tt>CREATE TABLE ahn3_pointcloud.ahn3_all<br>
(<br>
</tt><tt><tt> </tt>id integer NOT NULL,<br>
</tt><tt><tt> </tt>pa pcpatch(7)<br>
)<br>
</tt><tt>-------------------</tt><tt><tt>----------</tt></tt><tt><tt><tt>----------</tt></tt></tt><tt><tt><tt><tt>----------</tt></tt></tt></tt><tt><tt><tt><tt><tt>----------</tt></tt></tt></tt></tt><tt><tt><tt><tt><tt><tt>----------</tt></tt></tt></tt></tt></tt><tt><tt><tt><tt><tt><tt><tt>----------</tt></tt></tt></tt></tt></tt>-</tt><tt><br>
</tt><br>
<b>One of the child tables:</b><br>
<tt><tt>-------------------</tt><tt><tt>----------</tt></tt><tt><tt><tt>----------</tt></tt></tt><tt><tt><tt><tt>----------</tt></tt></tt></tt><tt><tt><tt><tt><tt>----------</tt></tt></tt></tt></tt><tt><tt><tt><tt><tt><tt>----------</tt></tt></tt></tt></tt></tt><tt><tt><tt><tt><tt><tt><tt>----------</tt></tt></tt></tt></tt></tt>-</tt><tt><br>
</tt>CREATE TABLE ahn3_pointcloud.c_25dn2<br>
(<br>
id serial NOT NULL,<br>
pa pcpatch(7),<br>
CONSTRAINT c_25dn2_pkey PRIMARY KEY (id),<br>
CONSTRAINT check_extent CHECK (pc_intersects(pa,
st_geomfromtext('POLYGON((115000 487500,120000
487500,120000 481250,115000 481250,115000
487500))'::text, 28992)))<br>
)<br>
INHERITS (ahn3_pointcloud.ahn3_all)<br>
</tt><tt>-------------------</tt><tt><tt>----------</tt></tt><tt><tt><tt>----------</tt></tt></tt><tt><tt><tt><tt>----------</tt></tt></tt></tt><tt><tt><tt><tt><tt>----------</tt></tt></tt></tt></tt><tt><tt><tt><tt><tt><tt>----------</tt></tt></tt></tt></tt></tt><tt><tt><tt><tt><tt><tt><tt>----------</tt></tt></tt></tt></tt></tt>-</tt><tt><br>
</tt><br>
<b>Example query:</b><br>
<tt><tt>-------------------</tt><tt><tt>----------</tt></tt><tt><tt><tt>----------</tt></tt></tt><tt><tt><tt><tt>----------</tt></tt></tt></tt><tt><tt><tt><tt><tt>----------</tt></tt></tt></tt></tt><tt><tt><tt><tt><tt><tt>----------</tt></tt></tt></tt></tt></tt><tt><tt><tt><tt><tt><tt><tt>----------</tt></tt></tt></tt></tt></tt>-</tt><tt><br>
</tt>EXPLAIN SELECT COUNT(pa) FROM
ahn3_pointcloud.ahn3_all <br>
WHERE PC_Intersects(pa,
ST_MakeEnvelope(120740,486076,121074,486292, 28992))<br>
<br>
"Aggregate (cost=301989.36..301989.37 rows=1
width=32)"<br>
" -> Append (cost=0.00..301248.82 rows=296216
width=32)"<br>
" -> Seq Scan on ahn3_all (cost=0.00..0.00
rows=1 width=32)"<br>
" Filter:
(('010300002040710000010000000500000000000000407AFD4000000000<snip><br>
" -> Bitmap Heap Scan on c_25gn1
(cost=6064.85..59546.09 rows=58574 width=32)"<br>
" Recheck Cond:
('010300002040710000010000000500000000000000407AFD40000<snip><br>
" Filter:
_st_intersects('010300002040710000010000000500000000000000407<snip><br>
" -> Bitmap Index Scan on
c_25gn1_idx (cost=0.00..6050.20 rows=175722 width=0)"<br>
" Index Cond:
('010300002040710000010000000500000000000000407AFD4<snip><br>
" -> Bitmap Heap Scan on c_30fz1
(cost=7990.39..77907.99 rows=76576 width=32)"<br>
" Recheck Cond:
('010300002040710000010000000500000000000000407AFD40000<snip><br>
" Filter:
_st_intersects('010300002040710000010000000500000000000000407<snip><br>
" -> Bitmap Index Scan on
c_30fz1_idx (cost=0.00..7971.24 rows=229727 width=0)"<br>
" Index Cond:
('010300002040710000010000000500000000000000407AFD4<snip><br>
" -> Bitmap Heap Scan on c_30fz2
(cost=7393.57..71499.52 rows=70210 width=32)"<br>
" Recheck Cond:
('010300002040710000010000000500000000000000407AFD40000<snip><br>
" Filter:
_st_intersects('010300002040710000010000000500000000000000407<snip><br>
" -> Bitmap Index Scan on
c_30fz2_idx (cost=0.00..7376.02 rows=210631 width=0)"<br>
" Index Cond:
('010300002040710000010000000500000000000000407AFD4<snip><br>
" -> Bitmap Heap Scan on c_25ez1
(cost=2624.69..26079.63 rows=25688 width=32)"<br>
" Recheck Cond:
('010300002040710000010000000500000000000000407AFD40000<snip><br>
" Filter:
_st_intersects('010300002040710000010000000500000000000000407<snip><br>
" -> Bitmap Index Scan on
c_25ez1_idx (cost=0.00..2618.27 rows=77064 width=0)"<br>
" Index Cond:
('010300002040710000010000000500000000000000407AFD4<snip><br>
" -> Bitmap Heap Scan on c_25dn2
(cost=6714.83..66215.58 rows=65167 width=32)"<br>
" Recheck Cond:
('010300002040710000010000000500000000000000407AFD40000<snip><br>
" Filter:
_st_intersects('010300002040710000010000000500000000000000407<snip><br>
" -> Bitmap Index Scan on
c_25dn2_idx (cost=0.00..6698.54 rows=195500 width=0)"<br>
" Index Cond:
('010300002040710000010000000500000000000000407AFD4<snip><br>
</tt><tt>-------------------</tt><tt><tt>----------</tt></tt><tt><tt><tt>----------</tt></tt></tt><tt><tt><tt><tt>----------</tt></tt></tt></tt><tt><tt><tt><tt><tt>----------</tt></tt></tt></tt></tt><tt><tt><tt><tt><tt><tt>----------</tt></tt></tt></tt></tt></tt><tt><tt><tt><tt><tt><tt><tt>----------</tt></tt></tt></tt></tt></tt>-</tt><tt><br>
</tt><br>
<br>
<br>
</div>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a moz-do-not-send="true"
href="mailto:postgis-users@lists.osgeo.org"
target="_blank">postgis-users@lists.osgeo.org</a><br>
<a moz-do-not-send="true"
href="http://lists.osgeo.org/mailman/listinfo/postgis-users"
rel="noreferrer" target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
</blockquote>
</div>
<br>
</div>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="http://lists.osgeo.org/mailman/listinfo/postgis-users">http://lists.osgeo.org/mailman/listinfo/postgis-users</a></pre>
</blockquote>
<br>
</body>
</html>