Re: vacuum analyze slows sql query

patrick ~ <sidsrr(at)yahoo(dot)com> writes:
> that if I 'createdb' and populate it with the "sanatized" data the
> query in question is quite fast; 618 rows returned in 864.522 ms.
> This was puzzling. Next I noticed that after a VACUUM the very same
> query would slow down to a crawl; 618 rows returned in 1080688.921 ms).

The outer query is too simple to have more than one possible plan,
so the issue is certainly a change in query plans inside the function.
You need to be investigating what's happening inside that function.
7.1 doesn't have adequate tools for this, but in 7.4 you can use
PREPARE and EXPLAIN ANALYZE EXECUTE to examine the query plans used
for parameterized statements, which is what you've got here.

My bet is that with ANALYZE stats present, the planner guesses wrong
about which index to use; but without looking at EXPLAIN ANALYZE output
there's no way to be sure.

BTW, why the bizarrely complicated substitute for a NOT NULL test?
ISTM you only need

create function
pkk_offer_has_pending_purch( integer )
returns bool
as '
select p0.purchase_id is not null
from pkk_purchase p0
where p0.offer_id = $1
and ( p0.pending = true
or ( ( p0.expire_time > now()
or p0.expire_time isnull )
and p0.cancel_date isnull ) )
limit 1
' language 'sql' ;

(Actually, seeing that pkk_purchase.purchase_id is defined as NOT NULL,
I wonder why the function exists at all ... but I suppose you've
"stripped" the function to the point of being nonsense.)

regards, tom lane