X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id E3329476083; Wed, 30 Apr 2003 12:11:45 -0400 (EDT) Received: from matrix.gatewaynet.com (unknown [217.19.69.50]) by postgresql.org (Postfix) with ESMTP id 1F2EE475F2C; Wed, 30 Apr 2003 12:11:44 -0400 (EDT) Received: from matrix.gatewaynet.com (localhost [127.0.0.1]) by matrix.gatewaynet.com (8.12.5/8.12.5) with ESMTP id h3ULDe1s009392; Wed, 30 Apr 2003 19:13:40 -0200 Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.12.5/8.12.5/Submit) with ESMTP id h3ULDe50009388; Wed, 30 Apr 2003 19:13:40 -0200 Date: Wed, 30 Apr 2003 19:13:40 -0200 (GMT+2) From: Achilleus Mantzios To: Josh Berkus Cc: pgsql-sql@postgresql.org, Subject: Re: [SQL] 7.3 analyze & vacuum analyze problem In-Reply-To: <200304300902.37985.josh@agliodbs.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Spam-Status: No, hits=-38.6 required=5.0 tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, QUOTE_TWICE_1,REPLY_WITH_QUOTES,SIGNATURE_LONG_DENSE, USER_AGENT_PINE autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/264 X-Sequence-Number: 1770 On Wed, 30 Apr 2003, Josh Berkus wrote: > Achilleus, > > > i think i have an issue regarding the statistics that > > a) (plain) ANALYZE status and > > b) VACUUM ANALYZE status > > produce. > > It's perfectly normal for a query to run faster after a VACUUM ANALYZE than > after an ANALYZE ... after all, you just vacuumed it, didn't you? I am afraid it is not so simple. What i (unsuccessfully) implied is that dynacom=# VACUUM ANALYZE status ; VACUUM dynacom=# ANALYZE status ; ANALYZE dynacom=# is enuf to damage the performance. > > If you're demonstrating some other kind of behavioural difference, then please > post the results of EXPLAIN ANALYZE for the two examples. > dynacom=# ANALYZE status ; ANALYZE dynacom=# EXPLAIN ANALYZE select count(*) from status where assettable='vessels' and appname='ISM PMS' and apptblname='items' and status='warn' and isvalid and assetidval=49; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=4309.53..4309.53 rows=1 width=0) (actual time=242.60..242.60 rows=1 loops=1) -> Seq Scan on status (cost=0.00..4306.08 rows=1378 width=0) (actual time=15.75..242.51 rows=50 loops=1) Filter: ((assettable = 'vessels'::character varying) AND (appname = 'ISM PMS'::character varying) AND (apptblname = 'items'::character varying) AND (status = 'warn'::character varying) AND isvalid AND (assetidval = 49)) Total runtime: 242.74 msec (4 rows) dynacom=# dynacom=# VACUUM ANALYZE status ; VACUUM dynacom=# EXPLAIN ANALYZE select count(*) from status where assettable='vessels' and appname='ISM PMS' and apptblname='items' and status='warn' and isvalid and assetidval=49; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=2274.90..2274.90 rows=1 width=0) (actual time=8.89..8.89 rows=1 loops=1) -> Index Scan using status_all on status (cost=0.00..2274.34 rows=223 width=0) (actual time=8.31..8.83 rows=50 loops=1) Index Cond: ((assettable = 'vessels'::character varying) AND (assetidval = 49) AND (appname = 'ISM PMS'::character varying) AND (apptblname = 'items'::character varying) AND (status = 'warn'::character varying)) Filter: isvalid Total runtime: 8.98 msec (5 rows) dynacom=# > Oh, and we should probably shift this discussion to the PGSQL-PERFORMANCE > list. > OK. > -- ================================================================== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel: +30-210-8981112 fax: +30-210-8981877 email: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr