Re: vacuum analyze slows sql query

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> patrick ~ <sidsrr(at)yahoo(dot)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