From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-sql@postgresql.org, <pgsql-performance@postgresql.org>
Subject: Re: [SQL] 7.3 analyze & vacuum analyze problem
Date: Wed, 30 Apr 2003 19:13:40 -0200 (GMT+2)
Message-ID: <Pine.LNX.4.44.0304301907560.8921-100000@matrix.gatewaynet.com> (raw)
In-Reply-To: <200304300902.37985.josh@agliodbs.com>
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
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-sql@postgresql.org
Cc: achill@matrix.gatewaynet.com, josh@agliodbs.com, pgsql-performance@postgresql.org
Subject: Re: [SQL] 7.3 analyze & vacuum analyze problem
In-Reply-To: <Pine.LNX.4.44.0304301907560.8921-100000@matrix.gatewaynet.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