Re: Perplexing Query Performance

mdklatt(at)ou(dot)edu (Michael Klatt) wrote in message news:<2cb75565(dot)0204051614(dot)4ad76291(at)posting(dot)google(dot)com>...
> I'm trying to track down a serious performance bottleneck and have
> noticed the following problem. I have a rainfall database with
> approximately 1.2 million records. Different resolutions (daily,
> monthly, etc) are implemented as views, where the main rainfall table
> is aggregated as appropriate. I've been building up a query bit by
> bit to see where it bogs down, and I've found the straw that breaks
> the camel's back.
>

Another update, another problem.

Now the query is bogging down in a new place, and even VACUUM ANALYZE
hasn't fixed it as with my previous problem.

Consider this query:

select *
from daily_rainfall as p, sites as s
where p.site = s.ident and s.latitude >= -90 and s.latitude <= 90 and
s.longitude >= -180 and terrain in ('A', 'I', 'L');

Almost any combination of 'A', 'I', and/or 'L' consistently executes
in the same time, but if it's ONLY 'A' PostgreSQL seems to go off into
Never Never Land. There is no problem if 'I' or 'L' is used alone,
just 'A'.

Does anyone have any ideas?