pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-sql@postgresql.org
Subject: 7.3 analyze & vacuum analyze problem
Date: Wed, 30 Apr 2003 18:57:31 -0200 (GMT+2)
Message-ID: <Pine.LNX.4.44.0304301820340.8921-300000@matrix.gatewaynet.com> (raw)
In-Reply-To: <3E92E3B1.7070405@eselx.ipl.pt>
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_apptblname btree (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
Attachments:
2see-VACUUM_ANALYZE (text/plain, 7.9K) download
attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation
-------------+-----------+-----------+------------+--------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
id | 0 | 4 | -1 | | | {24,7079,13389,17624,21084,24392,25876,28033,29895,32132,33866} | 0.801686
checkdate | 0 | 8 | -1 | | | {"2002-10-19 10:59:54.399+03","2003-03-03 05:00:25.359+02","2003-03-29 05:00:28.382+02","2003-04-01 05:01:00.065+03","2003-04-11 05:01:27.234+03","2003-04-29 05:00:41.318+03","2003-04-29 05:01:24.084+03","2003-04-30 05:00:40.719+03","2003-04-30 05:01:06.203+03","2003-04-30 05:01:25.435+03","2003-04-30 05:01:46.636+03"} | 0.923726
assettable | 0 | 11 | 1 | {vessels} | {1} | | 1
assetidval | 0 | 4 | 18 | {53,57,48,65} | {0.136667,0.107333,0.100333,0.0766667} | {20,26,29,32,33,35,44,47,49,61,74} | 0.117431
appname | 0 | 11 | 6 | {"ISM PMS",Class.Certificates,Class.Surveys,Repairs,Class.CMS,Class.Recommendations} | {0.989333,0.005,0.00266667,0.00166667,0.000666667,0.000666667} | | 0.990286
apptblname | 0 | 9 | 5 | {items,certificates,surveys,repdat,recommendations} | {0.99,0.005,0.00266667,0.00166667,0.000666667} | | 0.975883
apptblidval | 0 | 4 | 5002 | {7161,11099,7162,12392,2819,8346,12241,12465,14932,17007} | {0.002,0.002,0.00166667,0.00166667,0.00133333,0.00133333,0.00133333,0.00133333,0.00133333,0.00133333} | {7,5016,8296,12443,14443,17115,20166,21996,22866,24209,32796} | 0.0604367
colname | 0 | 14 | 6 | {lastrepdate,lastinspdate,rh,N/A,status,classsurvey} | {0.660667,0.298667,0.03,0.00833333,0.00166667,0.000666667} | | 0.38036
colval | 0 | 8 | -0.433074 | {0,1,1624,3742,146,594,864,915,125,224} | {0.009,0.00166667,0.00166667,0.00133333,0.001,0.001,0.001,0.001,0.000666667,0.000666667} | {21,14463.947,14498.65,18031.762,18043.58,18051.517,18059.712,18067.688,18078.67,21646.39,82811594.146} | 0.288656
status | 0 | 8 | 2 | {warn,alarm} | {0.55,0.45} | | 0.401053
isvalid | 0 | 1 | 2 | {f,t} | {0.650667,0.349333} | | 0.944827
username | 0 | 12 | 7 | {periodic,ckaklaman,amantzio,secretuser,mitsios,lignos,birtsia} | {0.886333,0.0546667,0.0336667,0.012,0.0116667,0.001,0.000666667} | | 0.805608
(12 rows)
2see-ANALYZE (text/plain, 7.7K) download
attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation
-------------+-----------+-----------+------------+-----------------------------------------------------------------+------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
id | 0 | 4 | -1 | | | {9,6352,12201,16772,20303,24130,25418,27688,29427,31938,33868} | 0.821664
checkdate | 0 | 8 | -1 | | | {"2002-10-19 11:01:56.325+03","2003-03-01 05:00:53.05+02","2003-03-20 05:00:30.769+02","2003-03-30 05:00:22.981+03","2003-04-07 05:00:52.29+03","2003-04-26 05:02:40.671+03","2003-04-29 05:01:17.314+03","2003-04-30 05:00:30.248+03","2003-04-30 05:01:01.945+03","2003-04-30 05:01:23.903+03","2003-04-30 05:01:46.737+03"} | 0.922868
assettable | 0 | 11 | 1 | {vessels} | {1} | | 1
assetidval | 0 | 4 | 19 | {53,57,48,65,49} | {0.116,0.101,0.0933333,0.0823333,0.0673333} | {20,24,29,32,33,35,43,44,47,61,74} | 0.101641
appname | 0 | 11 | 5 | {"ISM PMS",Class.Certificates,Class.Surveys,Repairs,Class.CMS} | {0.984667,0.00666667,0.00433333,0.00333333,0.001} | | 0.980634
apptblname | 0 | 9 | 4 | {items,certificates,surveys,repdat} | {0.985667,0.00666667,0.00433333,0.00333333} | | 0.968969
apptblidval | 0 | 4 | 4764 | {7428,20257,2334,4656,9040,9864,11070,12975,15342,15845} | {0.002,0.00166667,0.00133333,0.00133333,0.00133333,0.00133333,0.00133333,0.00133333,0.00133333,0.00133333} | {1,5807,8457,12503,14512,17007,19967,21821,22909,24202,35284} | 0.0596056
colname | 0 | 14 | 6 | {lastrepdate,lastinspdate,rh,N/A,status,classsurvey} | {0.637,0.316667,0.031,0.011,0.00333333,0.001} | | 0.365323
colval | 0 | 8 | -0.345557 | {0,1,594,1200,784,1624,21,136,526,1231} | {0.012,0.00366667,0.00166667,0.00166667,0.00133333,0.00133333,0.001,0.001,0.001,0.001} | {24,14464.549,14494.017,18030.778,18043.42,18051.79,18060.089,18068.117,18078.319,122745.988,74262056.367} | 0.239448
status | 0 | 8 | 2 | {warn,alarm} | {0.561667,0.438333} | | 0.436302
isvalid | 0 | 1 | 2 | {f,t} | {0.675,0.325} | | 0.95008
username | 0 | 12 | 7 | {periodic,ckaklaman,amantzio,secretuser,mitsios,lignos,birtsia} | {0.876333,0.0523333,0.041,0.0143333,0.012,0.00266667,0.00133333} | | 0.817096
(12 rows)
view thread (9+ messages) latest in thread
Message-ID: <Pine.LNX.4.44.0304301820340.8921-300000@matrix.gatewaynet.com> Permalink: ../Pine.LNX.4.44.0304301820340.8921-300000@matrix.gatewaynet.com/ Also on: postgresql.org/message-id/Pine.LNX.4.44.0304301820340.8921-300000@matrix.gatewaynet.com ·
reply
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Reply to all the recipients using the --to and --cc options: reply via email To: pgsql-sql@postgresql.org Cc: achill@matrix.gatewaynet.com Subject: Re: 7.3 analyze & vacuum analyze problem In-Reply-To: <Pine.LNX.4.44.0304301820340.8921-300000@matrix.gatewaynet.com> * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox