Postgresql Spatial Query too slow

Question

I have two tables that I would like to intersect. The first table represents about 50 million points and the second is a polygon layer of all the countries in the world. I want to get all the points that intersect with that polygon.

SELECT d.id, d.geom 
FROM export d, world_boundaries b 
WHERE (b.cntry_name = 'UK') 
  AND d.date_inserted >= '2012-06-01' 
  AND d.geom && b.wkb_geometry 
  AND intersects(d.geom, b.wkb_geometry);

This query is very simple but takes more than 4 hours to run. I have GIST indexes built on the geometry column for each table and have VACUUM ANALYZE'd them both. Still no performance increase. I am running CENTOS 6 with Postgres 8.4 and PostGIS 1.5. Can anyone shed some light on how to speed things up? I get results very quickly when LIMITing the query to 1000 to 10000 records. When I try to grab the full result set it drags. Thoughts?

UPDATE: I see now that I have to refine my query as a first step in this process. I get the envelope like this

select astext(st_envelope(wkb_geometry)) as e 
from world_borders 
where cntry_name = 'UK'

Now, what is the most efficient way to include/execute this as part of the entire query?

Answer 1

Try running it with EXPLAIN (and LIMIT) to see if the indexes are being used at all.

Since the real intersection checking is the slowest operation there, maybe running it against a ST_Collect of the subquery (everything but the ST_Intersects check) would help. That way there'd be only one call and if the multigeometry construction is fast enough, the net result may be better.

edit1: Well, turns out it is not so optimal, since unless you force the coordinates to 3d (to hold also the id), an extra lookup is needed to get the geometry id:

SELECT d.id, d.geom
FROM
(
    SELECT *
    FROM
    ( 
        SELECT ST_Collect(d.geom)
        FROM export d, world_boundaries b 
        WHERE (b.cntry_name = 'UK') 
        AND d.date_inserted >= '2012-06-01' 
        AND d.geom && b.wkb_geometry
    ) as c, world_boundaries b 
    WHERE (b.cntry_name = 'UK')
    AND ST_Intersection(c.geom, b.wkb_geometry);
) as e, export d
WHERE (ST_Dump(e.geom)).geom = d.geom