Re: Query 4-5 times slower after ANALYZE

In response to "Philippe Lang" <philippe(dot)lang(at)attiksystem(dot)ch>:

[snip]

> > Anyway, the real reason I posted -- I doubt if anyone will be able to
> > make sense of a query plan that complex without the actual query, so
> > you'll probably want to post it as well.
>
> :) What? I thought you would read that like Neo was reading the
> Matrix... :)

Yeah ... not quite like that, although it appears that there are some
on-list who do have that level of skill ...

[more snip ...]

> AND solde_po(CURRENT_DATE, c.id) > 0
>
> AND d.creation_date <= CURRENT_DATE
> ----------------
>
> The heavy part here is the "solde_po" call (at the end), which takes up
> most CPU time. That's why scanning the customers table takes up so much
> time. I imagine a small change in the way this table is scanned can have
> enormous effects in the overall execution time, like when an sequential
> scan is preferred over an index scan. Does that sound correct?

Quite likely.

> A small question here: solde_po is an SQL function (not PLPGSQL). Is it
> "inlined" in the parent query before the whole query execution plan is
> calculated? Or are they treated completely separately?

Is that function STABLE? If it's VOLATILE, can it be rewritten to be
STABLE? That might make a lot of difference if it's repeatedly called
with the same values.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/