On Sun, Apr 7, 2013 at 3:22 PM, Mark Davidson <mark@4each.co.uk> wrote:
Takes a little longer with the INNER join unfortunately. Takes about ~3.5 minutes, here is the query plan http://explain.depesz.com/s/EgBl.
With the JOIN there might not be a match if the data does not fall within one of the areas that is selected in the IN query.
So if we have data id (10) that might fall in areas ( 1, 5, 8, 167 ) but the user might be querying areas ( 200 ... 500 ) so no match in area would be found just to be absolutely clear.
I'm not clear on what you *want* to happen. Are you sure it works the way you want it to now? If you want every specified id to return at least one row even if there is no qualified area matching it, you have to move the LEFT JOIN one join to the left, and have to move the IN list criteria from the WHERE to the JOIN.