pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: patrick ~ <sidsrr@yahoo.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: vacuum analyze slows sql query
Date: Fri, 5 Nov 2004 22:18:50 -0800 (PST)
Message-ID: <20041106061850.5463.qmail@web52102.mail.yahoo.com> (raw)
In-Reply-To: <25527.1099714011@sss.pgh.pa.us>

Hi Tom, -performance@,

I apologize if I didn't follow through with the PREPARE and
EXECUTE.  I assume that is what you are refering to.  After
reading the PostgreSQL docs on PREPARE statement I realized
two things: a) PREPARE is only session long and b) that I
can not (at least I haven't figured out how) PREPARE a
statement which would mimic my original select statement
which I could EXECUTE over all rows of pkk_offer table.

Best I could do is either:

 PREPARE pkk_01 ( interger ) select $1, pkk_offer_has_pending_purch( $1 ) from
pkk_offer ;

or

 PREPARE pkk_00 ( integer ) <the def of pkk_offer_has_pending_purc( integer )>

In the former case the EXPLAIN ANALYZE doesn't give enough
data (it is the same as w/o the PREPARE statement).  In the
latter case, I can only execute it with one offer_id at at
time.  Is this sufficient?

If so, here are the results before and after VACUUM ANALYZE:

pkk=# explain analyze execute pkk_00( 795 ) ;
                          QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=8.57..8.58 rows=1 width=0) (actual time=0.095..0.096 rows=1
loops=1)
   InitPlan
     ->  Limit  (cost=0.00..8.57 rows=1 width=4) (actual time=0.083..0.084
rows=1 loops=1)
           ->  Index Scan using pur_offer_id_idx on pkk_purchase p0 
(cost=0.00..17.13 rows=2 width=4) (actual time=0.079..0.079 rows=1 loops=1)
                 Index Cond: (offer_id = $1)
                 Filter: ((((expire_time)::timestamp with time zone > now()) OR
(expire_time IS NULL) OR (pending = true)) AND ((cancel_date IS NULL) OR
(pending = true)))
 Total runtime: 0.238 ms
(7 rows)

pkk=# VACUUM ANALYZE ;
VACUUM
Time: 97105.589 ms

pkk=# explain analyze execute pkk_00( 795 ) ;
                          QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=8.57..8.58 rows=1 width=0) (actual time=0.329..0.330 rows=1
loops=1)
   InitPlan
     ->  Limit  (cost=0.00..8.57 rows=1 width=4) (actual time=0.311..0.312
rows=1 loops=1)
           ->  Index Scan using pur_offer_id_idx on pkk_purchase p0 
(cost=0.00..17.13 rows=2 width=4) (actual time=0.307..0.307 rows=1 loops=1)
                 Index Cond: (offer_id = $1)
                 Filter: ((((expire_time)::timestamp with time zone > now()) OR
(expire_time IS NULL) OR (pending = true)) AND ((cancel_date IS NULL) OR
(pending = true)))
 Total runtime: 0.969 ms
(7 rows)

Time: 16.252 ms



In both before and after "Index Scan" is used on pur_offer_id_idx.
So, unless I'm missing something obvious here I am at a loss.

I went as far as doing the EXPLAIN ANALYZE EXECUTE pkk_00( offer_id )
for each offer_id in pkk_offer table one at a time (not manually but
by scripting it).  All instances use "Index Scan".

I only noticed a couple that had quite large "actual times" like
this following:


pkk=# explain analyze execute pkk_00( 2312 ) ;
                                                                               
 QUERY PLAN                                                                    
            
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=8.57..8.58 rows=1 width=0) (actual time=21.279..21.282 rows=1
loops=1)
   InitPlan
     ->  Limit  (cost=0.00..8.57 rows=1 width=4) (actual time=21.256..21.258
rows=1 loops=1)
           ->  Index Scan using pur_offer_id_idx on pkk_purchase p0 
(cost=0.00..17.13 rows=2 width=4) (actual time=21.249..21.249 rows=1 loops=1)
                 Index Cond: (offer_id = $1)
                 Filter: ((((expire_time)::timestamp with time zone > now()) OR
(expire_time IS NULL) OR (pending = true)) AND ((cancel_date IS NULL) OR
(pending = true)))
 Total runtime: 21.435 ms
(7 rows)

Time: 22.541 ms


Which makes sense when you look at the number of entries this
offer_id has in pkk_purchase table vs offer_id = 795:

pkk=# select offer_id, count(*) from pkk_purchase where offer_id in ( 795, 2312
) group by offer_id ;
 offer_id | count 
----------+-------
      795 |     4
     2312 |  1015
(2 rows)

Time: 21.118 ms


--patrick




--- Tom Lane <tgl@sss.pgh.pa.us> wrote:

> patrick ~ <sidsrr@yahoo.com> writes:
> > 1. Is this really the only solution left for me?
> 
> You still haven't followed the suggestions that were given to you
> (ie, find out what is happening with the plan for the query inside
> the problematic function).
> 
> 			regards, tom lane


		
__________________________________ 
Do you Yahoo!? 
Check out the new Yahoo! Front Page. 
www.yahoo.com 
 




view thread (17+ messages)  latest in thread

Message-ID: <20041106061850.5463.qmail@web52102.mail.yahoo.com>
Permalink:  ../20041106061850.5463.qmail@web52102.mail.yahoo.com/
Also on:    postgresql.org/message-id/20041106061850.5463.qmail@web52102.mail.yahoo.com

 · 

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: sidsrr@yahoo.com, tgl@sss.pgh.pa.us
  Subject: Re: vacuum analyze slows sql query
  In-Reply-To: <20041106061850.5463.qmail@web52102.mail.yahoo.com>

* 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