[postgis-users] PostGIS limited Query Performance

[Sandro Santilli]

On Tue, Feb 16, 2016 at 11:57:25PM +0000, Mustafa Elbehery wrote:

> 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)


How many rows do you have in "planning_neighborhoods" table ?
How many rows do you have in "sfuber" table ?

--strk; 

  ()   Free GIS & Flash consultant/developer
  /\   http://strk.keybit.net/services.html