<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7652.24">
<TITLE>RE: [postgis-users] Large geometry issue</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=2>Martin,<BR>
<BR>
Sorry for the lack of coherence -- 2530 vertices in this test polygon ... it should be in the original posting I sent, an attached zip file with two tables; this is the single entry in the oli_req table. The other table has some 13000 rows of polygons which are either completely within or overlapping with the large poly.<BR>
<BR>
If you can't get at that let me know and I'll send you a copy ...<BR>
<BR>
Greg W.<BR>
<BR>
<BR>
-----Original Message-----<BR>
From: postgis-users-bounces@postgis.refractions.net on behalf of Martin Davis<BR>
Sent: Thu 7/5/2007 5:09 PM<BR>
To: PostGIS Users Discussion<BR>
Subject: Re: [postgis-users] Large geometry issue<BR>
<BR>
Greg,<BR>
<BR>
Can you elaborate on your statement "2530 in a sinlge polygon"? Do you<BR>
mean holes or subPolygons in a MultiPolygon?<BR>
<BR>
Either way, currently JTS is not optimized for working with very large<BR>
numbers of holes and sub-polygons. As Paul says, we are currently<BR>
working on improving this. Hopefully we can at least match the<BR>
performance of the Other DMBS(es) for this situation!<BR>
<BR>
Would it be possible for me to obtain this geometry for testing?<BR>
<BR>
Martin<BR>
<BR>
<BR>
<BR>
Gregory Williamson wrote:<BR>
><BR>
> Dear peoples,<BR>
><BR>
> I have a problem with a query that uses an absurdly large geometry<BR>
> (2530 in a single polygon). This is srid -1 (part of a large test of<BR>
> postgres vs some other database product). Everything has been vacuumed<BR>
> and analyzed.<BR>
><BR>
> The initial search to find candidates in a target table is quite fast:<BR>
> catest=# select count(*) from wtm_sub_cell w, order_line_item x WHERE<BR>
> x.bbox && w.geometry AND x.id_as_int = 114672;<BR>
> count<BR>
> -------<BR>
> 13168<BR>
> (1 row)<BR>
><BR>
> Time: 9.472 ms<BR>
><BR>
> Trying to get the list narrowed to geometries that are completely<BR>
> contained by the requested shape is slow:<BR>
> catest=# select count(*) from wtm_sub_cell w, order_line_item x WHERE<BR>
> x.bbox && w.geometry AND distance(x.geometry,w.geometry) = 0 and<BR>
> x.id_as_int = 114672;<BR>
> count<BR>
> -------<BR>
> 1112<BR>
> (1 row)<BR>
><BR>
> Time: 69277.780 ms<BR>
><BR>
> So I have two questions:<BR>
> a) anything better to use than "distance(x,y) = 0) ? I tried<BR>
> st_within -- it is about the same speed but returns no polys, which is<BR>
> strange to me, but I also haven't looked at these in detail yet. For<BR>
> example:<BR>
> catest=# select count(*) from wtm_sub_cell w, order_line_item x WHERE<BR>
> x.bbox && w.geometry AND st_within(x.geometry,w.geometry) and<BR>
> x.id_as_int = 114672;<BR>
> count<BR>
> -------<BR>
> 0<BR>
> (1 row)<BR>
><BR>
> Time: 1173.185 ms<BR>
> (same results with st_within(w.geometry,x.geometry):<BR>
> catest=# select count(*) from wtm_sub_cell w, order_line_item x WHERE<BR>
> x.bbox && w.geometry AND st_within(w.geometry,x.geometry) and<BR>
> x.id_as_int = 114672;<BR>
> count<BR>
> -------<BR>
> 0<BR>
> (1 row)<BR>
><BR>
><BR>
> b) anything I can do to speed things up ? I have tried boosting work<BR>
> mem to 16 megs (from 1) and it made no apparent difference.<BR>
><BR>
><BR>
><BR>
> I have a self contained test case that shows the same behavior -- the<BR>
> one large poly and all the candidates in another table. Apologies for<BR>
> the size; hopefully it's not been mangled in the transfers.<BR>
><BR>
> Explain analyze of the sample (the sequential is sensible since there<BR>
> is only one row in the table):<BR>
> catest=# explain analyze select count(*) from wsc_candidates w,<BR>
> oli_req x WHERE w.geometry && x.bbox AND<BR>
> distance(w.geometry,x.oli_req_geom) > 0 AND x.oli_req_id = 114672;<BR>
> <BR>
> QUERY PLAN <BR>
> -------------------------------------------------------------------------------------------------------------------------------------------------------<BR>
> Aggregate (cost=20.28..20.29 rows=1 width=0) (actual<BR>
> time=77232.858..77232.859 rows=1 loops=1)<BR>
> -> Nested Loop (cost=0.00..9.30 rows=4389 width=0) (actual<BR>
> time=6.389..77221.506 rows=12056 loops=1)<BR>
> Join Filter: (distance(w.geometry, x.oli_req_geom) ><BR>
> 0::double precision)<BR>
> -> Seq Scan on oli_req x (cost=0.00..1.01 rows=1<BR>
> width=40602) (actual time=0.007..0.009 rows=1 loops=1)<BR>
> Filter: (oli_req_id = 114672)<BR>
> -> Index Scan using wsc_c_spatial_ndx on wsc_candidates w <BR>
> (cost=0.00..8.27 rows=1 width=109) (actual time=0.022..25.991<BR>
> rows=13168 loops=1)<BR>
> Index Cond: (w.geometry && x.bbox)<BR>
> Filter: (w.geometry && x.bbox)<BR>
> Total runtime: 77232.901 ms<BR>
> (9 rows)<BR>
><BR>
> Time: 77233.773 ms<BR>
><BR>
><BR>
> And for the real thing:<BR>
> catest=# explain analyze select count(*) from wtm_sub_cell w,<BR>
> order_line_item x WHERE w.geometry && x.bbox AND<BR>
> distance(w.geometry,x.geometry) = 0 AND x.id_as_int = 114672;<BR>
> QUERY<BR>
> PLAN <BR>
> --------------------------------------------------------------------------------------------------------------------------------------------<BR>
> Aggregate (cost=141.83..141.84 rows=1 width=0) (actual<BR>
> time=77457.587..77457.588 rows=1 loops=1)<BR>
> -> Nested Loop (cost=5.99..141.83 rows=1 width=0) (actual<BR>
> time=15.682..77456.541 rows=1112 loops=1)<BR>
> Join Filter: (distance(w.geometry, x.geometry) = 0::double<BR>
> precision)<BR>
> -> Index Scan using oli_id_ndx on order_line_item x <BR>
> (cost=0.00..8.30 rows=1 width=383) (actual time=0.012..0.018 rows=1<BR>
> loops=1)<BR>
> Index Cond: (id_as_int = 114672)<BR>
> -> Bitmap Heap Scan on wtm_sub_cell w (cost=5.99..132.97<BR>
> rows=32 width=109) (actual time=2.988..21.796 rows=13168 loops=1)<BR>
> Filter: (w.geometry && x.bbox)<BR>
> -> Bitmap Index Scan on wsc_geom_idx1 <BR>
> (cost=0.00..5.98 rows=32 width=0) (actual time=2.828..2.828 rows=13168<BR>
> loops=1)<BR>
> Index Cond: (w.geometry && x.bbox)<BR>
> Total runtime: 77457.633 ms<BR>
> (10 rows)<BR>
><BR>
> Time: 77458.458 ms<BR>
><BR>
><BR>
> The tables involved by size:<BR>
> catest=# select count(*) from wsc_candidates;<BR>
> count<BR>
> -------<BR>
> 13168<BR>
> (1 row)<BR>
><BR>
> Time: 2.586 ms<BR>
> catest=# select count(*) from oli_req;<BR>
> count<BR>
> -------<BR>
> 1<BR>
> (1 row)<BR>
><BR>
> Time: 0.193 ms<BR>
> catest=# select count(*) from wtm_sub_cell;<BR>
> count<BR>
> ---------<BR>
> 6399928<BR>
> (1 row)<BR>
><BR>
> Time: 1776.508 ms<BR>
> catest=# select count(*) from order_line_item;<BR>
> count<BR>
> --------<BR>
> 395921<BR>
> (1 row)<BR>
><BR>
> Time: 176.083 ms<BR>
><BR>
><BR>
> Many thanks for your time and bandwidth!<BR>
><BR>
> Greg Williamson<BR>
> Senior DBA<BR>
> GlobeXplorer LLC, a DigitalGlobe company<BR>
><BR>
> Confidentiality Notice: This e-mail message, including any<BR>
> attachments, is for the sole use of the intended recipient(s) and may<BR>
> contain confidential and privileged information and must be protected<BR>
> in accordance with those provisions. Any unauthorized review, use,<BR>
> disclosure or distribution is prohibited. If you are not the intended<BR>
> recipient, please contact the sender by reply e-mail and destroy all<BR>
> copies of the original message.<BR>
><BR>
> (My corporate masters made me say this.)<BR>
><BR>
> ------------------------------------------------------------------------<BR>
><BR>
> _______________________________________________<BR>
> postgis-users mailing list<BR>
> postgis-users@postgis.refractions.net<BR>
> <A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
> <BR>
<BR>
--<BR>
Martin Davis<BR>
Senior Technical Architect<BR>
Refractions Research, Inc.<BR>
(250) 383-3022<BR>
<BR>
_______________________________________________<BR>
postgis-users mailing list<BR>
postgis-users@postgis.refractions.net<BR>
<A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
<BR>
</FONT>
</P>
</BODY>
</HTML>