Re: [SQL] 7.3 analyze & vacuum analyze problem

On Fri, 2 May 2003, Achilleus Mantzios wrote:

> On Wed, 30 Apr 2003, Tom Lane wrote:
>
> >
> > It would be interesting to see the pg_class and pg_stats rows for this
> > table after VACUUM ANALYZE and after ANALYZE --- but I suspect the main
> > difference will be the reltuples values.
>
> I surely must generate a reproducable scenario,
> describing the exact steps made, so i'll focus
> on that.

I use a freebsd-current (hereafter called FBSD) as a test environment,
with a freshly reloaded db and NO VACUUM or ANALYZE ever run, and i
EXPLAIN ANALYZE some queries against a linux 2.4.18SMP (hereafter called
LNX) which is the production environment, and on which a recent VACUUM
FULL ANALYZE is run.

Some queries run *very* fast on FBSD and very slow on LNX,
where others run very slow on FBSD and very fast on LNX.
(Here the oper system is not an issue, i just use these
2 acronyms as aliases for the 2 situations/environments.

So i have:

================= FBSD ===================
========= QueryA (A VERY FAST PLAN) =====
dynacom=# EXPLAIN ANALYZE select count(*) from status where
assettable='vessels' and appname='ISM PMS' and apptblname='items' and
status='warn' and isvalid and assetidval=57;

QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=6.02..6.02 rows=1 width=0) (actual time=14.16..14.16
rows=1 loops=1)
-> Index Scan using status_all on status (cost=0.00..6.02 rows=1
width=0) (actual time=13.09..13.95 rows=75 loops=1)
Index Cond: ((assettable = 'vessels'::character varying) AND
(assetidval = 57) AND (appname = 'ISM PMS'::character varying) AND
(apptblname = 'items'::character
varying) AND (status = 'warn'::character varying))
Filter: isvalid
Total runtime: 14.40 msec
(5 rows)

dynacom=#
===============QueryB A VERY SLOW PLAN =====
dynacom=# EXPLAIN ANALYZE select it.id from items it,machdefs md where
it.defid = md.defid and first(md.parents)=16492 and it.vslwhid = 53 and
it.machtypecount = 1 order
by md.description,md.partno;
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=457.76..457.77 rows=1 width=68) (actual time=150.31..150.31
rows=0 loops=1)
Sort Key: md.description, md.partno
-> Nested Loop (cost=0.00..457.75 rows=1 width=68) (actual
time=150.16..150.16 rows=0 loops=1)
-> Index Scan using items_machtypecount on items it
(cost=0.00..451.73 rows=1 width=8) (actual time=0.99..89.30 rows=2245
loops=1)
Index Cond: (machtypecount = 1)
Filter: (vslwhid = 53)
-> Index Scan using machdefs_pkey on machdefs md
(cost=0.00..6.01 rows=1 width=60) (actual time=0.02..0.02 rows=0
loops=2245)
Index Cond: ("outer".defid = md.defid)
Filter: (first(parents) = 16492)
Total runtime: 150.58 msec
(10 rows)

dynacom=#
=================END FBSD=================

=================LNX =====================
========= QueryA (A VERY SLOW PLAN) =====
dynacom=# EXPLAIN ANALYZE select count(*) from status where
assettable='vessels' and appname='ISM PMS' and apptblname='items' and
status='warn' and isvalid and assetidval=57;

QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1346.56..1346.56 rows=1 width=0) (actual
time=244.05..244.05 rows=1 loops=1)
-> Seq Scan on status (cost=0.00..1345.81 rows=300 width=0) (actual
time=0.63..243.93 rows=75 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 = 57))
Total runtime: 244.12 msec
(4 rows)

dynacom=#
=========== QueryB (A VERY FAST PLAN)=======
dynacom=# EXPLAIN ANALYZE select it.id from items it,machdefs md where
it.defid = md.defid and first(md.parents)=16492 and it.vslwhid = 53 and
it.machtypecount = 1 order by md.description,md.partno;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=631.23..631.26 rows=11 width=42) (actual time=0.08..0.08
rows=0 loops=1)
Sort Key: md.description, md.partno
-> Nested Loop (cost=0.00..631.05 rows=11 width=42) (actual
time=0.03..0.03 rows=0 loops=1)
-> Index Scan using machdefs_dad on machdefs md
(cost=0.00..228.38 rows=67 width=34) (actual time=0.02..0.02 rows=0
loops=1)
Index Cond: (first(parents) = 16492)
-> Index Scan using items_defid_vslid_mtcnt on items it
(cost=0.00..5.99 rows=1 width=8) (never executed)
Index Cond: ((it.defid = "outer".defid) AND (it.vslwhid =
53) AND (it.machtypecount = 1))
Total runtime: 0.15 msec
(8 rows)

dynacom=#

======= END LNX =====================================

* first is a function:
integer first(integer[]),
that returns the first element of a [1xN] array.

Now i run a VACUUM FULL ANALYZE; on the FBSD system
and after taht,i get *identical* plans as on the LNX system.
So, the VACUUM FULL ANALYZE command helps QueryB, but screws
QueryA.

Here i paste pg_stats,pg_class data for the 3 tables (status,
machdefs, items) on the FBSD system

====BEFORE the VACUUM FULL ANALYZE=====
dynacom=# SELECT * from pg_class where relname='status';
-[ RECORD 1 ]--+--------
relname | status
relnamespace | 2200
reltype | 3470164
relowner | 1
relam | 0
relfilenode | 3470163
relpages | 562
reltuples | 33565
reltoastrelid | 0
reltoastidxid | 0
relhasindex | t
relisshared | f
relkind | r
relnatts | 12
relchecks | 0
reltriggers | 0
relukeys | 0
relfkeys | 0
relrefs | 0
relhasoids | t
relhaspkey | f
relhasrules | f
relhassubclass | f
relacl |

dynacom=#
dynacom=# SELECT * from pg_class where relname='machdefs';
-[ RECORD 1 ]--+---------
relname | machdefs
relnamespace | 2200
reltype | 3470079
relowner | 1
relam | 0
relfilenode | 3470078
relpages | 175
reltuples | 13516
reltoastrelid | 3470081
reltoastidxid | 0
relhasindex | t
relisshared | f
relkind | r
relnatts | 20
relchecks | 0
reltriggers | 7
relukeys | 0
relfkeys | 0
relrefs | 0
relhasoids | t
relhaspkey | t
relhasrules | f
relhassubclass | f
relacl |

dynacom=# SELECT * from pg_class where relname='items';
-[ RECORD 1 ]--+--------
relname | items
relnamespace | 2200
reltype | 3470149
relowner | 1
relam | 0
relfilenode | 3470148
relpages | 233
reltuples | 29433
reltoastrelid | 3470153
reltoastidxid | 0
relhasindex | t
relisshared | f
relkind | r
relnatts | 25
relchecks | 0
reltriggers | 10
relukeys | 0
relfkeys | 0
relrefs | 0
relhasoids | t
relhaspkey | t
relhasrules | f
relhassubclass | f
relacl |

dynacom=#

Before the VACUUM [FULL] ANALYZE No statistics are produced

====AFTER the VACUUM FULL ANALYZE=====

===========================================================
dynacom=# SELECT * from pg_class where relname='status';
-[ RECORD 1 ]--+--------
relname | status
relnamespace | 2200
reltype | 3191663
relowner | 1
relam | 0
relfilenode | 3191662
relpages | 562
reltuples | 33565
reltoastrelid | 0
reltoastidxid | 0
relhasindex | t
relisshared | f
relkind | r
relnatts | 12
relchecks | 0
reltriggers | 0
relukeys | 0
relfkeys | 0
relrefs | 0
relhasoids | t
relhaspkey | f
relhasrules | f
relhassubclass | f
relacl |

dynacom=#

dynacom=# SELECT * from pg_class where relname='machdefs';
-[ RECORD 1 ]--+---------
relname | machdefs
relnamespace | 2200
reltype | 3191578
relowner | 1
relam | 0
relfilenode | 3191577
relpages | 175
reltuples | 13516
reltoastrelid | 3191580
reltoastidxid | 0
relhasindex | t
relisshared | f
relkind | r
relnatts | 20
relchecks | 0
reltriggers | 7
relukeys | 0
relfkeys | 0
relrefs | 0
relhasoids | t
relhaspkey | t
relhasrules | f
relhassubclass | f
relacl |

dynacom=#

dynacom=# SELECT * from pg_class where relname='items';
-[ RECORD 1 ]--+--------
relname | items
relnamespace | 2200
reltype | 3191648
relowner | 1
relam | 0
relfilenode | 3191647
relpages | 232
reltuples | 29433
reltoastrelid | 3191652
reltoastidxid | 0
relhasindex | t
relisshared | f
relkind | r
relnatts | 25
relchecks | 0
reltriggers | 10
relukeys | 0
relfkeys | 0
relrefs | 0
relhasoids | t
relhaspkey | t
relhasrules | f
relhassubclass | f
relacl |

dynacom=# SELECT
tablename,attname,null_frac,avg_width,n_distinct,most_common_vals,most_common_freqs,histogram_bounds,correlation
from pg_stats where tablename='status';

tablename | attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation
-----------+-------------+-----------+-----------+------------+--------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
status | id | 0 | 4 | -1 | | | {8,3677,6977,10159,13753,17012,20228,23620,26864,30311,33859} | 0.795126
status | checkdate | 0 | 8 | -1 | | | {"2002-10-19 10:54:53.764+03","2003-03-01 05:00:22.691+02","2003-03-03 05:00:23.876+02","2003-03-04 05:00:28.912+02","2003-03-29 05:00:28.099+02","2003-03-30 05:00:24.009+03","2003-04-02 12:14:34.221+03","2003-04-26 05:02:53.133+03","2003-04-29 05:01:43.716+03","2003-04-30 05:01:05.727+03","2003-04-30 05:01:46.749+03"} | 0.844914
status | assettable | 0 | 11 | 1 | {vessels} | {1} | | 1
status | assetidval | 0 | 4 | 21 | {53,57,48,65,33,61,49} | {0.11,0.108667,0.0916667,0.079,0.073,0.0693333,0.0626667} | {20,24,26,29,32,35,36,43,44,47,79} | 0.15861
status | appname | 0 | 11 | 6 | {"ISM PMS",Class.Certificates,Class.Surveys,Repairs,Class.CMS,Class.Recommendations} | {0.975333,0.01,0.00633333,0.004,0.003,0.00133333} | | 0.963033
status | apptblname | 0 | 9 | 5 | {items,certificates,surveys,repdat,recommendations} | {0.978333,0.01,0.00633333,0.004,0.00133333} | | 0.96127
status | apptblidval | 0 | 4 | -0.165914 | {18799,2750,9025,12364,12491,20331,20546,20558,21665,22913} | {0.00166667,0.00133333,0.00133333,0.00133333,0.00133333,0.00133333,0.00133333,0.00133333,0.00133333,0.00133333} | {1,4996,8117,12367,14441,16488,19586,21155,22762,24026,32802} | 0.104023
status | colname | 0 | 14 | 6 | {lastrepdate,lastinspdate,rh,N/A,status,classsurvey} | {0.685,0.241333,0.049,0.0176667,0.004,0.003} | | 0.487112
status | colval | 0 | 8 | -0.56769 | {0,1,2991,27,146,1102,412,784,136,1126} | {0.0206667,0.004,0.002,0.00166667,0.00166667,0.00166667,0.00133333,0.00133333,0.001,0.001} | {21,14442.908,14506.476,18028.868,18038.256,18045.821,18053.101,18062.404,18076.057,150212.049,96805423.065} | 0.197915
status | status | 0 | 8 | 2 | {warn,alarm} | {0.524333,0.475667} | | 0.514211
status | isvalid | 0 | 1 | 2 | {f,t} | {0.789333,0.210667} | | 0.967602
status | username | 0 | 12 | 7 | {periodic,amantzio,ckaklaman,secretuser,mitsios,birtsia,lignos} | {0.856333,0.053,0.0433333,0.029,0.013,0.00266667,0.00266667} | | 0.769222
(12 rows)

dynacom=# SELECT
tablename,attname,null_frac,avg_width,n_distinct,most_common_vals,most_common_freqs,histogram_bounds,correlation
from pg_stats where tablename='machdefs';

tablename | attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation
-----------+-------------+-----------+-----------+------------+---------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
machdefs | defid | 0 | 4 | -1 | | | {2482,4607,6556,7957,9339,10662,12006,13822,15082,16533,18224} | 0.315706
machdefs | parents | 0.124667 | 29 | -0.345266 | {"{8673}","{4456}","{9338}","{11565}","{6865}","{11183}","{10810}","{9852}","{7016}","{7636}"} | {0.0166667,0.016,0.016,0.0156667,0.013,0.0126667,0.0106667,0.01,0.01,0.00966667} | |
machdefs | description | 0.281333 | 20 | -0.101338 | {Inspection,Rings,Overhaul,Greasing/Lubrication,Bearings,Oil,"Safety devices",Motor,Cleaning,Crankcase} | {0.0296667,0.01,0.008,0.00733333,0.00633333,0.00633333,0.006,0.00533333,0.005,0.00433333} | {"1T11 Vortex Pump","Camshaft drive","Cylinder Lubricator Pump body","Ejector pump","Fuel injection pump No5","Inlet valve","Main bearing No6","Piston & Connecting rod No6","Safety cut out device No7","Stuffing box","dP/I Transmitter flow meter kit"} | 0.04711
machdefs | partno | 0.840667 | 10 | 327 | | | {0137,151623-54101,302,51.04101-0479,90401-48-296,"G 21401","Z 11918","Z 23165","Z 27242","Z 27533",ZK34402} | 0.394772
machdefs | machtypeid | 0 | 4 | 739 | {358,632,207,364,16,633,1006,31,533,723} | {0.0853333,0.0326667,0.0226667,0.0223333,0.0203333,0.0203333,0.0203333,0.0196667,0.0196667,0.0196667} | {19,64,129,330,456,631,809,932,1048,1242,1575} | 0.128535
machdefs | rhbec | 0.782667 | 4 | 20 | {6000} | {0.073} | {375,750,1500,1500,3000,3750,3750,7500,9000,12000,37500} | 0.300707
machdefs | rhdue | 0.782667 | 4 | 20 | {8000} | {0.073} | {500,1000,2000,2000,4000,5000,5000,10000,12000,16000,50000} | 0.300707
machdefs | periodbec | 0.458667 | 4 | 11 | {22} | {0.262333} | {5,67,67,67,135,135,270,270,675,1350} | 0.415895
machdefs | perioddue | 0.458667 | 4 | 10 | {30,90,180,360,1800,7,900,720,120,60} | {0.262333,0.0833333,0.053,0.0456667,0.0233333,0.021,0.021,0.0156667,0.0153333,0.000666667} | | 0.419195
machdefs | action | 0.474333 | 13 | 56 | {Inspection,Overhaul,Cleaning,Clearances,"Megger Report"} | {0.151333,0.0966667,0.0746667,0.0273333,0.0236667} | {"Actuation test",Check,"Check Position",Greasing/Lubrication,Landing,"Pressure Test",Renewal,Renewal,"Report Receipt",Test,"Water Washing"} | 0.180053
machdefs | application | 0.973333 | 18 | 2 | {"Megger Report","CrankShaft Deflection Report"} | {0.0236667,0.003} | | 0.999508
(11 rows)

dynacom=# SELECT
tablename,attname,null_frac,avg_width,n_distinct,most_common_vals,most_common_freqs,histogram_bounds,correlation
from pg_stats where tablename='items';

tablename | attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation
-----------+-----------------+-----------+-----------+------------+-------------------------------------------------------------+------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
items | id | 0 | 4 | -1 | | | {2315,7279,12104,15875,19170,22170,25511,28420,32582,35753,38322} | 0.427626
items | vslwhid | 0 | 4 | 19 | {57,53,65,74} | {0.130333,0.125,0.116667,0.0746667} | {24,29,31,33,43,44,48,49,61,76,79} | 0.0679692
items | serialno | 0.952 | 10 | 149 | | | {014-3255,120092,1294207,20081,318216,56678,80-51,A1-0548,BV54654,KC60525,XL5334} | -0.0161482
items | rh | 0.863667 | 4 | 191 | {0} | {0.008} | {1,172,400,855,1292,2322,3191,4328,4906,6421,37679} | 0.0437569
items | lastinspdate | 0.885 | 4 | 120 | | | {1999-05-28,2002-04-23,2002-12-06,2003-01-15,2003-02-01,2003-02-22,2003-03-04,2003-03-15,2003-03-21,2003-03-28,2003-10-09} | 0.101498
items | classused | 0 | 4 | 2 | {0,1} | {0.985333,0.0146667} | | 0.979994
items | classaa | 0.985333 | 4 | 43 | | | {5,24,50,69,93,104,132,178,686,1072,1241} | -0.114588
items | classsurvey | 0.985333 | 31 | 44 | | | {"Aux Boiler Feed Inner Pump (No.1)","Ballast Inner Pump (No.1)","Emergency Fire Pump","M/E Cylinder Relief valve No2","M/E Piston No4","No.1 Cooling S.W.Pump for G/E","No.2 Cargo Oil Pump","No.2 Main Generator Diesel Engine","No.4 Connecting rod, top end and guides","No.6 Safety valve of M/E","Sea Water Service Pump"} | -0.0264975
items | classsurveydate | 0.987333 | 4 | 20 | | | {1998-05-31,1998-05-31,2000-01-31,2000-05-31,2001-03-31,2001-09-30,2002-02-28,2002-07-31,2002-12-31,2003-02-16,2003-04-23} | 0.305832
items | classduedate | 0.985333 | 4 | 22 | | | {2003-05-31,2003-07-31,2004-07-31,2005-01-31,2005-10-18,2006-07-31,2006-09-30,2007-07-31,2007-12-31,2008-02-28,2008-04-30} | 0.0222692
items | classcomment | 0.997333 | 26 | 1 | {"Main Propulsion System"} | {0.00266667} | | 1
items | defid | 0 | 4 | -0.243872 | {15856,15859,15851,13801,14179,14181,15860,15865,2771,2775} | {0.00333333,0.00233333,0.002,0.00166667,0.00166667,0.00166667,0.00166667,0.00166667,0.00133333,0.00133333} | {2319,3192,5182,7387,9296,11020,12862,14001,15190,16852,18221} | 0.321816
items | machtypecount | 0 | 4 | 8 | {1,2,3,4,6,5,7,8} | {0.62,0.22,0.139667,0.0113333,0.00466667,0.003,0.000666667,0.000666667} | | 0.489828
items | totalrh | 0 | 4 | 2 | {0} | {0.999667} | | 0.999829
items | comment | 0.928667 | 7 | 34 | | | {1,3,"90KVA-General service",No1,No1,No1,No2,No2,No2,No3,Stbd} | 0.384123
items | lastrepdate | 0.742667 | 4 | 10 | {2003-03-31} | {0.187333} | {2002-06-30,2003-02-28,2003-02-28,2003-02-28,2003-04-01,2003-04-04,2003-04-04,2003-04-04,2003-04-08} | 0.887771
(16 rows)

================================================================================
It seems that the presence of Statistics really hurt status table.
In the other cases (machdefs,items) VACUUM ANALYZE does
a pretty good job. (or at least compared to the "no stats at all" case).

Also Tom, i could give you access, if you want, to the test environment :)

> >
> >
> > regards, tom lane
> >
>
>

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