[postgis-users] Points not in Polygons and 'jointype 5 not supported'

[Lauri Kajan]

Hi all,

I'm trying to figure out an optimal query to find points in a table that
are not in any polygons.
My points table contains 3,7 million points and 6000 polygons.

My goal is to find points from certain area that are not contained by
polygons. Here is my current query:
*  select*
    *id,*
    *geom*
*  from*
*   points*
*  where not exists(*
*    select * from polygons where st_contains(polygons.geom, points.geom)*
*  )*
*  and points.geom && 'BOX(425930 7197112, 429605.074 7200582.906)'::BOX2D;*

I'm getting following notice from PostGIS:
*NOTICE:  gserialized_gist_joinsel: jointype 5 not supported*
What I have understood is that query works ok but the query plan might not
be optimal.

Is there something to do to improve this?


Here is the explain analyze results:
"Nested Loop Anti Join  (cost=18.83..3356.77 rows=809 width=36) (actual
time=3.986..438.047 rows=272 loops=1)"
"  ->  Bitmap Heap Scan on points  (cost=18.68..1540.86 rows=809 width=36)
(actual time=0.724..2.955 rows=1452 loops=1)"
"        Recheck Cond: (geom &&
'010300000001000000050000000000000028FF1941000000006E745B410000000028FF19416DE7FBB9D1775B41F0A7C64B94381A416DE7FBB9D1775B41F0A7C64B94381A41000000006E745B410000000028FF1941000000006E745B41'::geometry)"
"        ->  Bitmap Index Scan on points_geom  (cost=0.00..18.48 rows=809
width=0) (actual time=0.699..0.699 rows=1452 loops=1)"
"              Index Cond: (geom &&
'010300000001000000050000000000000028FF1941000000006E745B410000000028FF19416DE7FBB9D1775B41F0A7C64B94381A416DE7FBB9D1775B41F0A7C64B94381A41000000006E745B410000000028FF1941000000006E745B41'::geometry)"
"  ->  Index Scan using polygons_geom on polygons  (cost=0.15..2.23 rows=1
width=4361) (actual time=0.296..0.296 rows=1 loops=1452)"
"        Index Cond: (geom && points.geom)"
"        Filter: _st_contains(geom, points.geom)"
"        Rows Removed by Filter: 0"
"Total runtime: 438.491 ms"


I appreciate all your help!

-Lauri
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140812/f9a84c6a/attachment.html>