<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Thank you Nicolas. <br>
<br>
Going for a smaller set of bigger tables is a good suggestion.
However, considering I'm having trouble getting constraint exclusion
to work altogether I might have to look at other solutions (such as
a custom function).<br>
<br>
Best regards,<br>
Rubio<br>
<br>
<div class="moz-cite-prefix">On 2-11-2015 19:53, Nicolas Ribot
wrote:<br>
</div>
<blockquote
cite="mid:CAGAwT=3cYCw8P0eHAgAqbdh9hc31LD93SAow8Jii00-z_5kOpQ@mail.gmail.com"
type="cite">
<div dir="ltr">Hi,
<div><br>
</div>
<div>AFAIU, the restriction on partitioned tables is even
bigger. From PG doc (<a moz-do-not-send="true"
href="http://www.postgresql.org/docs/current/interactive/ddl-partitioning.html#DDL-PARTITIONING-CONSTRAINT-EXCLUSION">http://www.postgresql.org/docs/current/interactive/ddl-partitioning.html#DDL-PARTITIONING-CONSTRAINT-EXCLUSION</a>)
:</div>
<div><br>
</div>
<div>"<span
style="color:rgb(0,0,0);font-family:verdana,sans-serif;font-size:12.16px;line-height:1.5em">The
following caveats apply to constraint exclusion:</span></div>
<ul
style="font-size:12.16px;line-height:1.5em;margin-top:0.2em;margin-bottom:0.1em;color:rgb(0,0,0);font-family:verdana,sans-serif">
<li
style="font-size:1em;line-height:1.5em;margin-top:0.2em;margin-bottom:0.1em">
<p style="font-size:1em;line-height:1.5em;margin:0.2em 0em
1.2em">Constraint exclusion only works when the query's <tt
class="" style="font-size:1.3em">WHERE</tt> clause
contains constants (or externally supplied parameters).
For example, a comparison against a non-immutable function
such as<code class="" style="font-size:1.3em">CURRENT_TIMESTAMP</code> cannot
be optimized, since the planner cannot know which
partition the function value might fall into at run time.</p>
</li>
</ul>
<div>"</div>
<div>Using a subquery to provide values won't trigger the
constraint exclusion.<br>
</div>
<div>One solution could be an anonymous block or a function to
explicitly write query with constant values got from a
previous query.</div>
<div><br>
</div>
<div>Rubio, take also care that a lot of partitioned tables is
not recommended by Pg doc:</div>
<div>"<span
style="color:rgb(0,0,0);font-family:verdana,sans-serif;font-size:12.16px;line-height:18.24px">All
constraints on all partitions of the master table are
examined during constraint exclusion, so large numbers of
partitions are likely to increase query planning time
considerably. Partitioning using these techniques will work
well with up to perhaps a hundred partitions; don't try to
use many thousands of partitions.</span>"</div>
<div><br>
</div>
<div>I would go for a smaller set of bigger tables (10's
millions rows or so).</div>
<div><br>
</div>
<div>Nicolas </div>
</div>
<div class="gmail_extra"><br>
<div class="gmail_quote">On 2 November 2015 at 18:28, Bborie
Park <span dir="ltr"><<a moz-do-not-send="true"
href="mailto:dustymugs@gmail.com" target="_blank">dustymugs@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">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"><span class="">CREATE
TABLE ahn3_pointcloud.ahn3_all<br>
(<br>
id integer NOT NULL,<br>
</span> 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" target="_blank">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">
<div>
<div class="h5">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"><a class="moz-txt-link-abbreviated" href="mailto:rubio.vaughan@geodan.nl">rubio.vaughan@geodan.nl</a></a>></span>
wrote:<br>
</div>
</div>
<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>
<div class="h5">
<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>
</div>
</div>
_______________________________________________<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>
_______________________________________________<br>
postgis-users mailing list<br>
<a moz-do-not-send="true"
href="mailto:postgis-users@lists.osgeo.org">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>
<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>