pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (17+ messages)  latest in thread

Message-ID: <16373.1099512777@sss.pgh.pa.us>
Permalink:  ../16373.1099512777@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/16373.1099512777@sss.pgh.pa.us

 · 

reply

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