I assume your question is how to speed up the query? Can you show us the results of EXPLAIN ANALYZE SELECT ....? That way we maybe could know what is going on there.
–
ThiloMay 5 '12 at 11:05

No, My Question is why Ist this Query is taking even more than 5 times the time taken by 3rd Query above !!
–
Abhishek SagarMay 5 '12 at 11:15

ok, after about much awaiting, for IInd Query i recieves the following error message : "out of memory for query result" and query execution terminated. Can somebosy throw light on this ?
–
Abhishek SagarMay 5 '12 at 11:25

4 Answers
4

st_distance as well as st_area are not able to use indices. This is because both functions can not be reduced to questions like "Is a within b?" or "Do a and b overlap?". Even more concrete: GIST-indices can only operate on the bounding boxes of two objects.

For more information on this you just could look in the postgis manual, which states an example with st_distance and how the query could be improved to perform better.

However, this does not solve your k-nearest-neighbour-problem. For that, right now I do not have a good idea how to improve the performance of the query. The only chance I see would be assuming that the k nearest neighbors are always in a distance of below x meters. Then you could use a similar approach as done in the postgis manual.

Your second query could be speeded up a bit. Currently, you compute the area for each object in table 1 as often as table has rows - the strategy is first to join the data and then select based on that function. You could reduce the count of area computations significantly be precomputing the area:

WITH polygonareas AS (
SELECT gid, the_geom, st_area(the_geom) AS area
FROM polygons
)
SELECT g1.gid, g2.gid
FROM polygonareas as g1 , polygonareas as g2
WHERE g1.area > g2.area;

Your third query can be significantly optimized using bounding boxes: When the bounding boxes of two objects do not overlap, there is no way the objects do. This allows the usage of a given index and thus a huge performance gain.

...will return the 10 objects whose geom is nearest -90,40 in a scalable way. A few more details (options and caveats) are in that announcement post and use of the <-> and the <#> operators is also now documented in the official PostGIS 2.0 reference. (The main difference between the two is that <-> compares the shape centroids and <#> compares their boundaries — no difference for points, other shapes choose what is appropriate for your queries.)

A major caveat of these two operators, as it says on the linked postgis reference pages, is that the spatial index will only kick in if one of the geometries is a constant, as in your st_makepoint in the example. This means you can't use these operators with efficient index usage to answer the OP question which involves finding all geometries A near some other set of geometries B.
–
John BarçaJan 28 '14 at 8:09

Ah, good point. Thanks for raising it. So is @Stefan's answer the "correct" one then, just needing a bit more detail and updated link(s)?
–
natevwJan 28 '14 at 21:42

Is returning the k nearest neighbours in the p x g set. The query may be using indexes, but it still has to order the entire p x g set to find the k rows with the smallest distance. What you instead want is the following:

SELECT g1.gid,
(SELECT g2.gid FROM polygons g2
--prevents you from finding every nearest neighbour twice
WHERE g1.gid < g2.gid
--ORDER BY gid is erroneous if you want to limit by the distance
ORDER BY ST_Distance(g1.the_geom,g2.the_geom)
LIMIT k)
FROM points as g1;