Checking if point is contained by any geometry

Question

I have a table of geometries (mixed MultiPolygons and Polygons), I want to check if a point is contained by any of the geometries in the table. I don't care about how many, nor I want to retrieve any information, all I care about is a boolean. Is there a way to speed up this query, given the circumstances?

SELECT N>0 as on_land FROM(
SELECT COUNT(*) N FROM lands l WHERE 
ST_Contains(l.geom,ST_SetSRid(ST_Makepoint(47.3,19.4), 4326))) B

(the union of the 'lands' table is all landmass on earth)

Answer 1

I think the count(*) is not necessary if you are interested in the existence of a container polygon. My solution would be:

select 1 from lands 
  where st_contains(geom, st_setsrid(st_makepoint(47.3,19.4), 4326)) 
  LIMIT 1;

This returns nothing if no polygon contains point and "1" otherwise. I tested the queries on a table having ~40000 polygons. Compare the query plans, yours:

"Subquery Scan on b  (cost=149.07..149.09 rows=1 width=8)"
"  ->  Aggregate  (cost=149.07..149.08 rows=1 width=0)"
"        ->  Bitmap Heap Scan on planet_osm_polygon l  (cost=4.57..149.04 rows=13 width=0)"
"              Recheck Cond: (way ~ '0101000020E6100000CB08B99C5CC651C0271CD5447A284540'::geometry)"
"              Filter: _st_contains(way, '0101000020E6100000CB08B99C5CC651C0271CD5447A284540'::geometry)"
"              ->  Bitmap Index Scan on planet_osm_polygon_index  (cost=0.00..4.57 rows=39 width=0)"
"                    Index Cond: (way ~ '0101000020E6100000CB08B99C5CC651C0271CD5447A284540'::geometry)"

and mine:

"Limit  (cost=0.28..13.39 rows=1 width=0)"
"  ->  Index Scan using planet_osm_polygon_index on planet_osm_polygon  (cost=0.28..170.71 rows=13 width=0)"
"        Index Cond: (way ~ '0101000020E6100000CB08B99C5CC651C0271CD5447A284540'::geometry)"
"        Filter: _st_contains(way, '0101000020E6100000CB08B99C5CC651C0271CD5447A284540'::geometry)"