Re: [SQL] 7.3 analyze & vacuum analyze problem
| От | Achilleus Mantzios |
|---|---|
| Тема | Re: [SQL] 7.3 analyze & vacuum analyze problem |
| Дата | |
| Msg-id | Pine.LNX.4.44.0305021550260.19233-100000@matrix.gatewaynet.com обсуждение исходный текст |
| Ответ на | Re: [SQL] 7.3 analyze & vacuum analyze problem (Achilleus Mantzios <achill@matrix.gatewaynet.com>) |
| Список | pgsql-performance |
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-0405: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-2605: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,"Safetydevices",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","Camshaftdrive","Cylinder Lubricator Pump body","Ejector pump","Fuel injection pump No5","Inlet valve","Main
bearingNo6","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","Z27533",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,"ReportReceipt",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 |
| |
{"AuxBoiler Feed Inner Pump (No.1)","Ballast Inner Pump (No.1)","Emergency Fire Pump","M/E Cylinder Relief valve
No2","M/EPiston No4","No.1 Cooling S.W.Pump for G/E","No.2 Cargo Oil Pump","No.2 Main Generator Diesel Engine","No.4
Connectingrod, 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-Generalservice",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@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
В списке pgsql-performance по дате отправления: