Re: horrendous query challenge :-)

Fran Fabrizio <ffabrizio@mmrd.com> writes:
> Performance is about 2-3x's better, but still taking around 40s.  Now
> that we've eliminated the view and it's function calls, and the IN, this
> seems pretty straightforward.  My next stop was to check on the one
> remaining function call, the findsite() in the where clause.  But, I
> have spent about a week's time in the past working on that one and it's
> fairly well optimized, down to about .003 seconds per call.

Nonetheless, it's being evaluated 202*153 or almost 31000 times per
query.  Your .003 is an overestimate since it includes all the overhead
of query parsing/planning, but it still appears that those function
calls are the bulk of the runtime.

What is findsite doing exactly?  If it's a table lookup, maybe you could
inline it into this query and get some optimization.

            regards, tom lane