[postgis-users] PostGIS limited Query Performance

[Mustafa Elbehery]

Hi All, Below is the tables structures with the query executed, followed by
the query plan captured using *EXPLAIN *

CREATE TABLE sfuber
(
  id serial NOT NULL,
  tripid character varying,
  dtime timestamp without time zone,
  latitude numeric,
  longitude numeric,
  point geometry,
  CONSTRAINT sfuber_pkey PRIMARY KEY (id),
  CONSTRAINT enforce_dims_geom CHECK (st_ndims(point) = 2),
  CONSTRAINT enforce_geotype_geom CHECK (geometrytype(point) =
'POINT'::text OR point IS NULL),
  CONSTRAINT enforce_srid_pickup CHECK (st_srid(point) = 4326)
)

CREATE INDEX point
  ON sfuber
  USING gist
  (point);


*<< *----------------------------------------------------------------------------------------------------------------------------------
*>>*

CREATE TABLE planning_neighborhoods
(
  gid serial NOT NULL,
  neighborho character varying(25),
  geom geometry(MultiPolygon,4326),
  CONSTRAINT planning_neighborhoods_pkey PRIMARY KEY (gid)
)

CREATE INDEX planning_neighborhoods_geom_idx
  ON planning_neighborhoods
  USING gist
  (geom);


*<< *
----------------------------------------------------------------------------------------------------------------------------------
*>>*


*SELECT ST_AsText(point) FROM sfuber, planning_neighborhoods WHERE
ST_within(sfuber.point, planning_neighborhoods.geom);*
EXPLAIN SELECT ST_AsText(point) FROM sfuber, planning_neighborhoods WHERE
ST_within(sfuber.point, planning_neighborhoods.geom) limit 100000000;
                                      QUERY PLAN

--------------------------------------------------------------------------------------
 Limit  (cost=390.32..1723419.79 rows=1405938 width=32)
   ->  Nested Loop  (cost=390.32..1723419.79 rows=1405938 width=32)
         ->  Seq Scan on planning_neighborhoods  (cost=0.00..7.37 rows=37
width=32)
         ->  Bitmap Heap Scan on sfuber  (cost=390.32..46445.72 rows=3800
width=32)
               Recheck Cond: (point && planning_neighborhoods.geom)
               Filter: _st_contains(planning_neighborhoods.geom, point)
               ->  Bitmap Index Scan on point  (cost=0.00..389.37
rows=11399 width=0)
                     Index Cond: (point && planning_neighborhoods.geom)
(8 rows)


*The query return till limit 10 million record .. When I increase to 100
Millions, it never finish execution ..*

*<< *
----------------------------------------------------------------------------------------------------------------------------------
*>>*


I am using *PostgreSQL 9.3.10  *with * Extension POSTGIS="2.1.2" *on
AWS *EC2 m3.xlarge
with 4 cores && 15 GRAM .. *

Let me know please if you need more information.


On Tue, Feb 16, 2016 at 11:53 PM Nick Ves <vesnikos at gmail.com> wrote:

> 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.
>
> On Tue, Feb 16, 2016 at 10:46 PM, Mustafa Elbehery <
> elbeherymustafa at gmail.com> wrote:
>
>> Hi Folks,
>>
>> I am trying to create a benchmark to compare PostGIS against Apache Spark
>> performance, in SpatialQuery capabilties for sure.
>>
>> 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 *rich geometric predicates & functions. *
>>
>> However, I have tried to load 6GB only of data into a table, and trying a
>> join query with another table. Specifically *ST_Within. *
>>
>> Surprisingly, PostGIS was unable to finish the query, for *limit >
>> 1000000 . *
>>
>> In other words, as long as I limit my query result from *10 till
>> 10000000, *the query return result. However, more than this, the query
>> never return, I have waited for *50 *minutes on AWS EC2 Instance, with 4
>> cores & 16 GB, and *nothing !!*
>>
>> I have tried to use Vaccum_Verbose & analyze, the same issue remained.
>>
>> Would like to hear any suggestions to solve the problem
>>
>> Thanks in advance.
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/postgis-users
>>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160216/be7a1e24/attachment.html>