<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8">
</head>
<body 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 class="VARNAME" 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; orphans: auto;
text-align: left; text-indent: 0px; text-transform: none;
white-space: normal; widows: 1; word-spacing: 0px;
-webkit-text-stroke-width: 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; orphans: auto; text-align: left;
text-indent: 0px; text-transform: none; white-space: normal;
widows: 1; word-spacing: 0px; -webkit-text-stroke-width: 0px;
display: inline !important; float: none; background-color:
rgb(255, 255, 255);"><span class="Apple-converted-space"> </span>(</span><tt
class="TYPE" 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; orphans: auto;
text-align: left; text-indent: 0px; text-transform: none;
white-space: normal; widows: 1; word-spacing: 0px;
-webkit-text-stroke-width: 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; orphans: auto; text-align: left; text-indent:
0px; text-transform: none; white-space: normal; widows: 1;
word-spacing: 0px; -webkit-text-stroke-width: 0px; display: inline
!important; float: none; 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></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>
</body>
</html>