From: Tom Lane <tgl@sss.pgh.pa.us>
To: patrick ~ <sidsrr@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: vacuum analyze slows sql query
Date: Wed, 03 Nov 2004 15:12:57 -0500
Message-ID: <16373.1099512777@sss.pgh.pa.us> (raw)
In-Reply-To: <20041103185647.87443.qmail@web52105.mail.yahoo.com>
References: <20041103185647.87443.qmail@web52105.mail.yahoo.com>
patrick ~ <sidsrr@yahoo.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, sidsrr@yahoo.com
Subject: Re: vacuum analyze slows sql query
In-Reply-To: <16373.1099512777@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox