Re: vacuum analyze slows sql query - Mailing list pgsql-performance

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