Effectively forcing the planner to do the thing you want might help. In this case, sub-setting the polygon table prior to executing the spatial join with the points table. You might be able to outwit the planner using "WITH" syntax:
WITH polys AS (
SELECT * FROM area
WHERE area.id in IN(28,29,30,31,32,33,25,26,27,18,19,20,21,12,13,14,15,16,17,34,35,1,2,3,4,5,6,22,23,24,7,8,9,10,11)
)
SELECT *
FROM polys JOIN data ON ST_Intersects(data.point, polys.polygon)
WHERE datasetid IN(3)
AND (readingdatetime BETWEEN '1948-01-01' AND '2012-11-19');
The trouble with trying to play these games is that you are coding into your statement the assumption "my polygon list will always be more selective than my other query portions". Which might not be true for all parameterizations of your query, or for all applications of a particular query over a heterogeneously distributed dataset.
But it might work.
UPDATE: This goes even further down the dangerous road of assuming you know the selectivity of your clauses beforehand, this time we also take the attribute selection on the point table out and do it separately before the spatial join:
WITH polys AS (
SELECT * FROM area
WHERE area.id in IN(28,29,30,31,32,33,25,26,27,18,19,20,21,12,13,14,15,16,17,34,35,1,2,3,4,5,6,22,23,24,7,8,9,10,11)
),
WITH points AS (
SELECT * FROM data
WHERE datasetid IN(3)
AND (readingdatetime BETWEEN '1948-01-01' AND '2012-11-19')
)
SELECT *
FROM polys JOIN points ON ST_Intersects(points, polys.polygon);
Best Answer
ST_Distance()actually calculates the distance between all the pairs of points, so, as such, no index could be used. So your query will do a sequence scan and then choose those geometries that are less than the distance you specify away. You are looking for ST_DWithin, which does use an index.ST_Distance()is more useful for ordering results, often in conjunction with ORDER BY and/or LIMIT, that have been obtained with queries that do use an index.