0

I am trying to retrieve all points p that are within polygons c. While building the query, I came across this strange behavior.

Question: Why does query 2 takes so long compared to query 1? Is it also doing a full tablescan of table points even though the WHERE clause is using c.osm_id and not table p? If so how can we prevent this? My final query probably requires both tables in the FROM clause.

Query 1

SELECT *
FROM counties As c
WHERE c.osm_id IN (-1842139,-1933719,-2362362,-1933718,-2362263,-2306361,-1933745,-1942017,-1931997,-1865772,-1933055,-1933746,-2315704)

Query 2

SELECT *
FROM counties As c, points As p
WHERE c.osm_id IN (-1842139,-1933719,-2362362,-1933718,-2362263,-2306361,-1933745,-1942017,-1931997,-1865772,-1933055,-1933746,-2315704)

This is my final query, it seems to take forever (207 sec) to execute... I'm wondering if the slowness of this query is related to the slow speed of the above query 2.

Table p countains 1 million points and table c contains 300 polygons.

SELECT *
FROM counties As c, points As p
WHERE c.osm_id IN (-1842139,-1933719,-2362362,-1933718,-2362263,-2306361,-1933745,-1942017,-1931997,-1865772,-1933055,-1933746,-2315704)
AND ST_Within(
        ST_Transform(ST_SetSRID(ST_Point(p.lng, p.lat), 4326), 2163)
    ,c.geom)

Update

Query using indexed geometry column p.geom and JOIN still takes 144 seconds. Am I expecting an unreasonable increase in speed?

SELECT *
FROM counties As c
JOIN points As p
ON ST_Within(p.geom,c.geom)
WHERE c.osm_id IN (-1842139,-1933719,-2362362,-1933718,-2362263,-2306361,-1933745,-1942017,-1931997,-1865772,-1933055,-1933746,-2315704)

I figured the long query time is due to transferring the geometry data for 17k rows from the Pgsql database to my computer running pgAdmin3. Transferring the ids alone took 480ms.

  • Ok, deleted my answer since you added more to your question. Q2 is slow because it results in a full cross join, which means many many rows. Your final query is not able to use any index or anything from your points (it doen't have an actual geometry?), so joining a million of those records with your counties doesn't seem that slow! – René Wolferink Apr 18 '13 at 13:46
  • If you want to know what it's doing add EXPLAIN before the select to get the query plan (but yes it's doing a seq scan). To confirm that it would be aviable to use indexes please try to select the points from p that are within a hardcoded geometry (or geometry pulled from a subquery). – Jakub Kania Apr 18 '13 at 13:59
  • I've updated table p with an GIST indexed geometry column and the final query now takes 157 secs instead of 207 sec. Updated the question with a query that uses the indexed column and a JOIN but its still pretty slow at 144 secs. – Nyxynyx Apr 18 '13 at 14:11
  • Slow query time is actually due to transferring the geometry data for 17k rows over the network to my computer. Transferring ids alone was really quick. Thanks everyone! – Nyxynyx Apr 18 '13 at 14:22
2

Your problem is caused by the cross joining, you will have to use a correlated subquery to speed it up (assuming that an index can be used).

SELECT * 
FROM points AS p
JOIN 
(
 SELECT c.id, <other c columns>, 
    (SELECT array_agg(p.id) 
     FROM points AS p 
     WHERE ST_Within(ST_Transform(ST_SetSRID(ST_Point(p.lng, p.lat), 4326), 2163)   ,c.geom)) as p_id
 FROM counties c
 WHERE c.osm_id IN   (-1842139,-1933719,-2362362,-1933718,-2362263,-2306361,-1933745,-1942017,-1931997,-1865772,-1933055,-1933746,-2315704)
OFFSET 0) as q
ON p.id<@q.p_id
  • Thanks, I've removed the need for the subquery (I think!) by updating table p with a indexed geometry column. – Nyxynyx Apr 18 '13 at 14:13
2

I think the second request's answer will give you the whole table "points" whatever the parameters in this where function... This might be very important compared to the first request which only returns entries in the "countries" table whose osm_id is mentionned.

Maybe you should add a clause like ' AND ST_WITHIN(p.the_geom, c.the_geom)"

The request may still be quite long but the answered list shorter ;)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.