<div dir="ltr">Hi All, Below is the tables structures with the query executed, followed by the query plan captured using <b>EXPLAIN </b><div><b><br></b></div><div><div style="font-weight:bold">CREATE TABLE sfuber</div><div style="font-weight:bold">(</div><div style="font-weight:bold"> id serial NOT NULL,</div><div style="font-weight:bold"> tripid character varying,</div><div style="font-weight:bold"> dtime timestamp without time zone,</div><div style="font-weight:bold"> latitude numeric,</div><div style="font-weight:bold"> longitude numeric,</div><div style="font-weight:bold"> point geometry,</div><div style="font-weight:bold"> CONSTRAINT sfuber_pkey PRIMARY KEY (id),</div><div style="font-weight:bold"> CONSTRAINT enforce_dims_geom CHECK (st_ndims(point) = 2),</div><div style="font-weight:bold"> CONSTRAINT enforce_geotype_geom CHECK (geometrytype(point) = 'POINT'::text OR point IS NULL),</div><div style="font-weight:bold"> CONSTRAINT enforce_srid_pickup CHECK (st_srid(point) = 4326)</div><div style="font-weight:bold">)</div><div style="font-weight:bold"><br></div><div style="font-weight:bold"><div>CREATE INDEX point</div><div> ON sfuber</div><div> USING gist</div><div> (point);</div></div><div style="font-weight:bold"><br></div><div style="font-weight:bold"><br></div><div><b><< </b>---------------------------------------------------------------------------------------------------------------------------------- <b>>></b></div></div><div><b><br></b></div><div><div style="font-weight:bold">CREATE TABLE planning_neighborhoods</div><div style="font-weight:bold">(</div><div style="font-weight:bold"> gid serial NOT NULL,</div><div style="font-weight:bold"> neighborho character varying(25),</div><div style="font-weight:bold"> geom geometry(MultiPolygon,4326),</div><div style="font-weight:bold"> CONSTRAINT planning_neighborhoods_pkey PRIMARY KEY (gid)</div><div style="font-weight:bold">)</div><div style="font-weight:bold"><br></div><div style="font-weight:bold"><div>CREATE INDEX planning_neighborhoods_geom_idx</div><div> ON planning_neighborhoods</div><div> USING gist</div><div> (geom);</div></div><div style="font-weight:bold"><br></div><div style="font-weight:bold"><br></div><div style="font-weight:bold"><b><< </b><span style="font-weight:normal">---------------------------------------------------------------------------------------------------------------------------------- </span><b>>></b><br></div><div style="font-weight:bold"><b><br></b></div><div style="font-weight:bold"><b><div>SELECT ST_AsText(point) FROM sfuber, planning_neighborhoods WHERE ST_within(sfuber.point, planning_neighborhoods.geom);</div><div><br></div></b></div><div><div style="font-weight:bold">EXPLAIN SELECT ST_AsText(point) FROM sfuber, planning_neighborhoods WHERE ST_within(sfuber.point, planning_neighborhoods.geom) limit 100000000;</div><div style="font-weight:bold"> QUERY PLAN </div><div style="font-weight:bold">--------------------------------------------------------------------------------------</div><div style="font-weight:bold"> Limit (cost=390.32..1723419.79 rows=1405938 width=32)</div><div style="font-weight:bold"> -> Nested Loop (cost=390.32..1723419.79 rows=1405938 width=32)</div><div style="font-weight:bold"> -> Seq Scan on planning_neighborhoods (cost=0.00..7.37 rows=37 width=32)</div><div style="font-weight:bold"> -> Bitmap Heap Scan on sfuber (cost=390.32..46445.72 rows=3800 width=32)</div><div style="font-weight:bold"> Recheck Cond: (point && planning_neighborhoods.geom)</div><div style="font-weight:bold"> Filter: _st_contains(planning_neighborhoods.geom, point)</div><div style="font-weight:bold"> -> Bitmap Index Scan on point (cost=0.00..389.37 rows=11399 width=0)</div><div style="font-weight:bold"> Index Cond: (point && planning_neighborhoods.geom)</div><div style="font-weight:bold">(8 rows)</div><div style="font-weight:bold"><br></div><div style="font-weight:bold"><br></div><div><b><i>The query return till limit 10 million record .. When I increase to 100 Millions, it never finish execution ..</i></b></div></div><div><br></div><div><div style="font-weight:bold"><b><< </b><span style="font-weight:normal">---------------------------------------------------------------------------------------------------------------------------------- </span><b>>></b><br></div><div style="font-weight:bold"><b><br></b></div><div style="font-weight:bold"><b><br></b></div><div>I am using <b>PostgreSQL 9.3.10 </b>with <b> Extension POSTGIS="2.1.2" </b>on AWS <b>EC2 <span style="color:rgb(68,68,68);font-family:'Helvetica Neue',Roboto,Arial,sans-serif;font-size:13px">m3.xlarge with 4 cores && 15 GRAM .. </span></b></div><div><b><span style="color:rgb(68,68,68);font-family:'Helvetica Neue',Roboto,Arial,sans-serif;font-size:13px"><br></span></b></div><div><font color="#444444" face="Helvetica Neue, Roboto, Arial, sans-serif">Let me know please if you need more information.</font></div><div style="font-weight:bold"><b><br></b></div></div></div><br><div class="gmail_quote"><div dir="ltr">On Tue, Feb 16, 2016 at 11:53 PM Nick Ves <<a href="mailto:vesnikos@gmail.com" target="_blank">vesnikos@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div class="gmail_default" style="font-family:tahoma,sans-serif">Before anyone be able to give you a solid advice, I would suggest to post your query plan as well. I am almost certain that you're accessing your data using wrong queries. </div></div><div class="gmail_extra"><br><div class="gmail_quote"></div></div><div class="gmail_extra"><div class="gmail_quote">On Tue, Feb 16, 2016 at 10:46 PM, Mustafa Elbehery <span dir="ltr"><<a href="mailto:elbeherymustafa@gmail.com" target="_blank">elbeherymustafa@gmail.com</a>></span> wrote:<br></div></div><div class="gmail_extra"><div class="gmail_quote"><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Hi Folks, <div><br></div><div>I am trying to create a benchmark to compare PostGIS against Apache Spark performance, in SpatialQuery capabilties for sure. </div><div><br></div><div>I have expected that spark will outperfom, since the data resides in memory, unlike PostGIS. However, I am more interested in using PostGIS, for the sake of the <b>rich geometric predicates & functions. </b></div><div><b><br></b></div><div>However, I have tried to load 6GB only of data into a table, and trying a join query with another table. Specifically <b>ST_Within. </b></div><div><br></div><div>Surprisingly, PostGIS was unable to finish the query, for <b>limit > 1000000 . </b></div><div><b><br></b></div><div>In other words, as long as I limit my query result from <b>10 till 10000000, </b>the query return result. However, more than this, the query never return, I have waited for <b>50 </b>minutes on AWS EC2 Instance, with 4 cores & 16 GB, and <b>nothing !!</b></div><div><b><br></b></div><div>I have tried to use Vaccum_Verbose & analyze, the same issue remained. </div><div><br></div><div>Would like to hear any suggestions to solve the problem </div><div><br></div><div>Thanks in advance.</div></div>
<br></blockquote></div></div><div class="gmail_extra"><div class="gmail_quote"><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">_______________________________________________<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><br></blockquote></div><br></div>
_______________________________________________<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></div>