Skip site navigation (1) Skip section navigation (2)

Re: [SQL] 7.3 analyze & vacuum analyze problem

From: Achilleus Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: [SQL] 7.3 analyze & vacuum analyze problem
Date: 2003-04-30 21:13:40
Message-ID: Pine.LNX.4.44.0304301907560.8921-100000@matrix.gatewaynet.com (view raw or flat)
Thread:
Lists: pgsql-performancepgsql-sql
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(at)matrix(dot)gatewaynet(dot)com
        mantzios(at)softlab(dot)ece(dot)ntua(dot)gr


In response to

Responses

pgsql-performance by date

Next:From: Ron MayerDate: 2003-04-30 22:16:58
Subject: Re: [SQL] 7.3 analyze & vacuum analyze problem
Previous:From: Achilleus MantziosDate: 2003-04-30 20:57:31
Subject: 7.3 analyze & vacuum analyze problem

pgsql-sql by date

Next:From: Ron MayerDate: 2003-04-30 22:16:58
Subject: Re: [SQL] 7.3 analyze & vacuum analyze problem
Previous:From: Achilleus MantziosDate: 2003-04-30 20:57:31
Subject: 7.3 analyze & vacuum analyze problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group