7.3 analyze & vacuum analyze problem - Mailing list pgsql-sql

Hi,
i think i have an issue regarding the statistics that 
a) (plain) ANALYZE status and 
b) VACUUM ANALYZE status
produce.

I have a table status:
dynacom=# \d status                                  Table "public.status"  Column    |           Type           |
              Modifiers
 
-------------+--------------------------+---------------------------------------------------id          | integer
          | not null default 
 
nextval('"status_id_seq"'::text)checkdate   | timestamp with time zone |assettable  | character varying(50)
|assetidval | integer                  |appname     | character varying(100)   |apptblname  | character varying(50)
|apptblidval| integer                  |colname     | character varying(50)    |colval      | double precision
|status     | character varying(5)     |isvalid     | boolean                  |username    | character varying(50)
|
Indexes: status_id_key unique btree (id),        status_all btree (assettable, assetidval, appname, apptblname, 
status, isvalid),        status_all_wo_astidval btree (assettable, appname, apptblname, 
status, isvalid),        status_appname btree (appname),        status_apptblidval btree (apptblidval),
status_apptblnamebtree (apptblname),        status_assetidval btree (assetidval),        status_assettable btree
(assettable),       status_checkdate btree (checkdate),        status_colname btree (colname),        status_isvalid
btree(isvalid),        status_status btree (status)
 
dynacom=#
dynacom=# SELECT count(*) from status ;count
-------33565
(1 row)
dynacom=#

I very often perform queries of the form:
select count(*) from status where assettable='vessels' and 
appname='ISM PMS' and apptblname='items' and status='warn' 
and isvalid and assetidval=<SOME ID>;

Altho i dont understand exactly why the stats created by
VACUUM ANALYZE are more accurate (meaning producing faster plans)
than the ones created by
plain ANALYZE, (altho for some attributes they are false for sure)
the performance is much much better when
VACUUM ANALYZE is run than plain ANALYZE.

In the former case, some times the status_all index is used,
and sometimes (when the selectivity is small)
a sequential scan is performed.

In the latter case, no index is ever used even  
for crazy statements (assetidval is always >0) like:

select count(*) from status where assettable='vessels' and
appname='ISM PMS' and apptblname='items' and status='warn'
and isvalid and assetidval=-10000000;

I attach the statistics of either case.

My app just performs the above query for most of the assetidval values
(And for all most popular assetidval values)
So the elapsed time of the app i think is a good
measure of the overall performance of these queries.

In the "VACUUM ANALYZE" case it takes 1.2 - 1.5 secs, while
in the "ANALYZE" case it takes >=3+

-- 
==================================================================
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