<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 href="mailto:andy@squeakycode.net">andy@squeakycode.net</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 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 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 href="mailto:dustymugs@gmail.com" target="_blank">dustymugs@gmail.com</a><br>
<mailto:<a 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 href="http://max_extents.id" rel="noreferrer" target="_blank">max_extents.id</a> <<a href="http://max_extents.id" rel="noreferrer" target="_blank">http://max_extents.id</a>><br>
<br>
-bborie<br>
<br>
On Mon, Nov 2, 2015 at 8:45 AM, Rubio Vaughan<br>
<<a href="mailto:rubio.vaughan@geodan.nl" target="_blank">rubio.vaughan@geodan.nl</a> <mailto:<a 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 href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div>