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?

Each of the recent releases has improved GiST and GIN indexes. You might want to consider an upgrade to a new major release. It might even be worth trying your problem on the 9.2 beta release, since it includes SP-GiST. postgresql.org/docs/9.2/static/spgist-intro.html — kgrittn
– kgrittn, Commented Jul 1, 2012 at 22:31

lynxlynxlynx · Accepted Answer · 2012-07-02 09:25:50Z

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

edited Jul 2, 2012 at 9:25

answered Jul 1, 2012 at 21:58

lynxlynxlynx

1,44321 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

kgrittn Over a year ago

Don't expect the plan generated with a LIMIT to necessarily resemble the plan without a LIMIT. It might be the same, but it could be completely different, since the planner will be looking for the cheapest plan to return the requested number of rows, rather the the cheapest plan for returning all the rows. Those often differ.

aeupinhere Over a year ago

Any chance of providing an example of the ST_Collect() subquery like with the SQL statement above?

lynxlynxlynx Over a year ago

@kgrittn Of course, but I highly doubt it would be cheaper to not use an index there, so it should be fine as a diagnostic for what I said.

Collectives™ on Stack Overflow

Postgresql Spatial Query too slow

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related