Re: horrendous query challenge :-)

Fran Fabrizio <ffabrizio(at)mmrd(dot)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