[postgis-users] Help with Bad Query Plan

[Mark Cave-Ayland]

Oliver Snowden wrote:

> Dear all, I am having some problems with a bad query plan.  Essentially,
> I have two tables.  A table of many different types of geography and
> another table (reports) linking to those geographies.  We want to select
> a small subset of reports (e.g. 30 reports) based on date and intersect
> those report geographies (potentially historical geographies) with a
> (current) selected geography.
> 
> I have (conceptually) worked out what I believe to be most efficient by
> putting the query into canonical form, moving the restricts down and
> putting the most restrictive item first; although I haven't actually
> done any specific calculations - index/disk access speeds etc.
> 
> My view is that the query should be executed as shown below.
> 
> Any comments or suggestions will be greatly appreciated.  We have tried
> various things inc. subqueries, using &&, and indexing almost everything
> etc.  Still no luck.
> 
> Many thanks, Oliver.


Hi Oliver,

Please can we see the EXPLAIN ANALYZE output for queries STEP 3 and STEP 4?


Many thanks,

Mark.

-- 
Mark Cave-Ayland
Sirius Corporation - The Open Source Experts
http://www.siriusit.co.uk
T: +44 870 608 0063