Re: Query runs in 335ms; function in 100,239ms : date problem?

On 05/09/11, Rory Campbell-Lange (rory@campbell-lange.net) wrote:
> On 05/09/11, Tomas Vondra (tv@fuzzy.cz) wrote:
> > On 5 Zá??í 2011, 23:07, Rory Campbell-Lange wrote:
> ...
> > > The query itself runs in about a 1/3rd of a second. When running the
> > > query as a 'RETURN QUERY' function on Postgres 8.4, the function runs in
> > > over 100 seconds, about 300 times slower.

...

> > Try to run it as a prepared query - I guess you'll get about the same run
> > time as with the function (i.e. over 100 seconds).
>
> The prepared query runs in almost exactly the same time as the function,
> but thanks for the suggestion. A very useful aspect of it is that I was
> able to get the EXPLAIN output which I guess gives a fairly good picture
> of the plan used for the function.
>
> The explain output is here:
> http://campbell-lange.net/media/files/explain.txt.html
>
> I'm inexperienced in reading EXPLAIN output, but it looks like the
> Nested Loop Semi Join at line 72 is running very slowly.

I added in more filtering conditions to the clause at line 72 and the
prepared statement dropped in runtime to 24043.902 ms. Unfortunately the
function ran slower -- 47957.796 -- but even that is a 50% improvement.

Thanks very much for your help.

Regards
Rory