<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Thank you Remic. I'm going to look into this and report back.<br>
<br>
Cheers<br>
Rubio<br>
<br>
<div class="moz-cite-prefix">On 3-11-2015 11:45, Rémi Cura wrote:<br>
</div>
<blockquote
cite="mid:CAJvUf_tZCNyEFah6Q8x0KeZUOnK1YHZfw67X9Rs1gQhTYfFxOQ@mail.gmail.com"
type="cite">
<p dir="ltr">Hey, <br>
i tried to do exactly this,<br>
as it is currently the only thing preventing postgis (thus
pgpointcloud) to scale well.<br>
No strategy succeded, postgres mailing list was also clueless.</p>
<p dir="ltr">So un short no way to use partitionning without
extensive tricks with postgres rules.</p>
<p dir="ltr">Instead, it is very simple to design a table holding
extent of each lidar tables, then a fonction performing the
check and creating the union statment on the fly.<br>
Maybe you can even use rule on father table so this mechanism is
totally transparent to the user.</p>
<p dir="ltr">Please keep us posted !<br>
Cheers,<br>
Remic<br>
</p>
<div class="gmail_quote">Le 3 nov. 2015 05:47, "Andy Colson" <<a
moz-do-not-send="true" href="mailto:andy@squeakycode.net"><a class="moz-txt-link-abbreviated" href="mailto:andy@squeakycode.net">andy@squeakycode.net</a></a>>
a écrit :<br type="attribution">
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">Yep, +1 do
all this.<br>
<br>
Additionally: select performance is not a reason to use
partitioned tables. Its only update/delete performance that's
increased.<br>
<br>
Ok, _some_ selects might be faster with partitions, but I'd
bet not indexed select statements. You'd have to benchmark
it.<br>
<br>
If you really want 1400+ tables you might be able to write
yourself a function that figures out what table/tables to
query and generates a sql statement dynamically.<br>
<br>
Like from the docs:<br>
<br>
<a moz-do-not-send="true"
href="http://www.postgresql.org/docs/9.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN"
rel="noreferrer" target="_blank">http://www.postgresql.org/docs/9.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN</a><br>
<br>
(sorry, watch the word wrap)<br>
<br>
-Andy<br>
<br>
<br>
On 11/2/2015 12:53 PM, Nicolas Ribot wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">
Hi,<br>
<br>
AFAIU, the restriction on partitioned tables is even bigger.
From PG doc<br>
(<a moz-do-not-send="true"
href="http://www.postgresql.org/docs/current/interactive/ddl-partitioning.html#DDL-PARTITIONING-CONSTRAINT-EXCLUSION"
rel="noreferrer" target="_blank">http://www.postgresql.org/docs/current/interactive/ddl-partitioning.html#DDL-PARTITIONING-CONSTRAINT-EXCLUSION</a>)<br>
:<br>
<br>
"The following caveats apply to constraint exclusion:<br>
<br>
*<br>
<br>
Constraint exclusion only works when the query's WHERE
clause<br>
contains constants (or externally supplied parameters).
For example,<br>
a comparison against a non-immutable function such<br>
as|CURRENT_TIMESTAMP| cannot be optimized, since the
planner cannot<br>
know which partition the function value might fall into
at run time.<br>
<br>
"<br>
Using a subquery to provide values won't trigger the
constraint exclusion.<br>
One solution could be an anonymous block or a function to
explicitly<br>
write query with constant values got from a previous query.<br>
<br>
Rubio, take also care that a lot of partitioned tables is
not<br>
recommended by Pg doc:<br>
"All constraints on all partitions of the master table are
examined<br>
during constraint exclusion, so large numbers of partitions
are likely<br>
to increase query planning time considerably. Partitioning
using these<br>
techniques will work well with up to perhaps a hundred
partitions; don't<br>
try to use many thousands of partitions."<br>
<br>
I would go for a smaller set of bigger tables (10's millions
rows or so).<br>
<br>
Nicolas<br>
<br>
On 2 November 2015 at 18:28, Bborie Park <<a
moz-do-not-send="true" href="mailto:dustymugs@gmail.com"
target="_blank"><a class="moz-txt-link-abbreviated" href="mailto:dustymugs@gmail.com">dustymugs@gmail.com</a></a><br>
<mailto:<a moz-do-not-send="true"
href="mailto:dustymugs@gmail.com" target="_blank">dustymugs@gmail.com</a>>>
wrote:<br>
<br>
That check constraint is not going to help you as it is
too<br>
complicated for the partitioning task. I don't believe
the<br>
partitioning constraint can be functional but needs to
be simpler<br>
and built upon a basic data type...<br>
<br>
Something like...<br>
<br>
CREATE TABLE max_extent AS (<br>
id integer PRIMARY KEY,<br>
geom geometry(POLYGON)<br>
);<br>
<br>
And then the parent/partition tables had a reference to
pc_extent...<br>
<br>
CREATE TABLE ahn3_pointcloud.ahn3_all<br>
(<br>
id integer NOT NULL,<br>
pa pcpatch(7),<br>
max_extent_id integer<br>
);<br>
<br>
Then your queries become...<br>
<br>
WITH max_extents AS (<br>
SELECT<br>
id<br>
FROM max_extent<br>
WHERE PC_Intersects(geom,<br>
ST_MakeEnvelope(120740,486076,121074,486292, 28992))<br>
)<br>
SELECT<br>
*<br>
FROM ahn3_all<br>
JOIN max_extents<br>
ON ahn3_all.max_extent_id = <a
moz-do-not-send="true" href="http://max_extents.id"
rel="noreferrer" target="_blank">max_extents.id</a> <<a
moz-do-not-send="true" href="http://max_extents.id"
rel="noreferrer" target="_blank"><a class="moz-txt-link-freetext" href="http://max_extents.id">http://max_extents.id</a></a>><br>
<br>
-bborie<br>
<br>
On Mon, Nov 2, 2015 at 8:45 AM, Rubio Vaughan<br>
<<a moz-do-not-send="true"
href="mailto:rubio.vaughan@geodan.nl" target="_blank">rubio.vaughan@geodan.nl</a>
<mailto:<a moz-do-not-send="true"
href="mailto:rubio.vaughan@geodan.nl" target="_blank">rubio.vaughan@geodan.nl</a>>>
wrote:<br>
<br>
Dear all,<br>
<br>
We are trying to load a large LIDAR pointcloud
dataset into<br>
multiple tables in PostGIS, using the point cloud
extension.<br>
Eventually the whole data set will consist of 1400+
tables,<br>
containing about 1 million records each. Using a
union view on<br>
all these tables would result in terrible
performance, which is<br>
why we are trying to optimize query performance
using<br>
partitioned tables. According to the documentation,
the use of<br>
partitioned tables with CHECK constraints should
cause the query<br>
planner to only scan those tables for which the
CHECK constraint<br>
matches. Excerpt from the documentation:<br>
--------------------------------------------------------------------------------<br>
*18.7.4. Other Planner Options*<br>
constraint_exclusion(enum): Controls the query
planner's use of<br>
table constraints to optimize queries. The allowed
values of<br>
constraint_exclusion are on (examine constraints for
all<br>
tables), off (never examine constraints), and
partition (examine<br>
constraints only for inheritance child tables and
UNION ALL<br>
subqueries). partition is the default setting. It is
often used<br>
with inheritance and partitioned tables to improve
performance.<br>
When this parameter allows it for a particular
table, the<br>
planner compares query conditions with the table's
CHECK<br>
constraints, and omits scanning tables for which the
conditions<br>
contradict the constraints.<br>
--------------------------------------------------------------------------------<br>
<br>
However, as you can see from the example query
below, the<br>
indexes for all child tables are still scanned. I
would expect<br>
the query planner to only scan table c_25gn1, which
contains the<br>
queried region. Does anyone here have experience
with<br>
partitioned tables? I would be delighted to get some
pointers<br>
for figuring out this problem.<br>
<br>
Thanks in advance for any help!<br>
<br>
Best regards,<br>
Rubio Vaughan<br>
<br>
</blockquote>
<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></blockquote>
</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>