X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id CB55F3A3FB0 for ; Sat, 6 Nov 2004 06:18:53 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 86309-09 for ; Sat, 6 Nov 2004 06:18:51 +0000 (GMT) Received: from web52102.mail.yahoo.com (web52102.mail.yahoo.com [206.190.39.71]) by svr1.postgresql.org (Postfix) with SMTP id DCEFC3A4002 for ; Sat, 6 Nov 2004 06:18:51 +0000 (GMT) Message-ID: <20041106061850.5463.qmail@web52102.mail.yahoo.com> Received: from [66.92.62.216] by web52102.mail.yahoo.com via HTTP; Fri, 05 Nov 2004 22:18:50 PST Date: Fri, 5 Nov 2004 22:18:50 -0800 (PST) From: patrick ~ Subject: Re: vacuum analyze slows sql query To: Tom Lane Cc: pgsql-performance@postgresql.org In-Reply-To: <25527.1099714011@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/122 X-Sequence-Number: 9076 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 ) 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 wrote: > patrick ~ 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