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 по дате отправления: