Re: [SQL] 7.3 analyze & vacuum analyze problem

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