pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [SQL] 7.3 analyze & vacuum analyze problem
9+ messages / 5 participants
[nested] [flat]
* Re: [SQL] 7.3 analyze & vacuum analyze problem
@ 2003-04-30 16:03 Josh Berkus <josh@agliodbs.com>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 9+ messages in thread
From: Josh Berkus @ 2003-04-30 16:03 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; pgsql-sql@postgresql.org; +Cc: pgsql-performance
Achilleus,
> i think i have an issue regarding the statistics that
> a) (plain) ANALYZE status and
> b) VACUUM ANALYZE status
> produce.
It's perfectly normal for a query to run faster after a VACUUM ANALYZE than
after an ANALYZE ... after all, you just vacuumed it, didn't you?
If you're demonstrating some other kind of behavioural difference, then please
post the results of EXPLAIN ANALYZE for the two examples.
Oh, and we should probably shift this discussion to the PGSQL-PERFORMANCE
list.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] 7.3 analyze & vacuum analyze problem
@ 2003-04-30 18:48 Josh Berkus <josh@agliodbs.com>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 2 replies; 9+ messages in thread
From: Josh Berkus @ 2003-04-30 18:48 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-performance
Achilleus,
> I am afraid it is not so simple.
> What i (unsuccessfully) implied is that
> dynacom=# VACUUM ANALYZE status ;
> VACUUM
> dynacom=# ANALYZE status ;
> ANALYZE
> dynacom=#
You're right, that is mysterious. If you don't get a response from one of
the major developers on this forum, I suggest that you post those EXPLAIN
results to PGSQL-BUGS.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 9+ messages in thread
* 7.3 analyze & vacuum analyze problem
@ 2003-04-30 20:57 Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 9+ messages in thread
From: Achilleus Mantzios @ 2003-04-30 20:57 UTC (permalink / raw)
To: pgsql-sql@postgresql.org
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
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)
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)
Attachments:
[text/plain] 2see-VACUUM_ANALYZE (7.9K, ../../Pine.LNX.4.44.0304301820340.8921-300000@matrix.gatewaynet.com/2-2see-VACUUM_ANALYZE)
download | inline:
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)
[text/plain] 2see-ANALYZE (7.7K, ../../Pine.LNX.4.44.0304301820340.8921-300000@matrix.gatewaynet.com/3-2see-ANALYZE)
download | inline:
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)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] 7.3 analyze & vacuum analyze problem
@ 2003-04-30 21:13 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 9+ messages in thread
From: Achilleus Mantzios @ 2003-04-30 21:13 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
On Wed, 30 Apr 2003, Josh Berkus wrote:
> Achilleus,
>
> > i think i have an issue regarding the statistics that
> > a) (plain) ANALYZE status and
> > b) VACUUM ANALYZE status
> > produce.
>
> It's perfectly normal for a query to run faster after a VACUUM ANALYZE than
> after an ANALYZE ... after all, you just vacuumed it, didn't you?
I am afraid it is not so simple.
What i (unsuccessfully) implied is that
dynacom=# VACUUM ANALYZE status ;
VACUUM
dynacom=# ANALYZE status ;
ANALYZE
dynacom=#
is enuf to damage the performance.
>
> If you're demonstrating some other kind of behavioural difference, then please
> post the results of EXPLAIN ANALYZE for the two examples.
>
dynacom=# ANALYZE status ;
ANALYZE
dynacom=# EXPLAIN ANALYZE select count(*) from status where
assettable='vessels' and appname='ISM PMS' and apptblname='items' and
status='warn' and isvalid and assetidval=49;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4309.53..4309.53 rows=1 width=0) (actual
time=242.60..242.60 rows=1 loops=1)
-> Seq Scan on status (cost=0.00..4306.08 rows=1378 width=0) (actual
time=15.75..242.51 rows=50 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 = 49))
Total runtime: 242.74 msec
(4 rows)
dynacom=#
dynacom=# VACUUM ANALYZE status ;
VACUUM
dynacom=# EXPLAIN ANALYZE select count(*) from status where
assettable='vessels' and appname='ISM PMS' and apptblname='items' and
status='warn' and isvalid and assetidval=49;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=2274.90..2274.90 rows=1 width=0) (actual time=8.89..8.89
rows=1 loops=1) -> Index Scan using status_all on status
(cost=0.00..2274.34 rows=223 width=0) (actual time=8.31..8.83 rows=50
loops=1)
Index Cond: ((assettable = 'vessels'::character varying) AND
(assetidval = 49) AND (appname = 'ISM PMS'::character varying) AND
(apptblname = 'items'::character varying) AND (status = 'warn'::character
varying))
Filter: isvalid
Total runtime: 8.98 msec
(5 rows)
dynacom=#
> Oh, and we should probably shift this discussion to the PGSQL-PERFORMANCE
> list.
>
OK.
>
--
==================================================================
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] 7.3 analyze & vacuum analyze problem
@ 2003-04-30 22:16 Ron Mayer <ron@intervideo.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 9+ messages in thread
From: Ron Mayer @ 2003-04-30 22:16 UTC (permalink / raw)
To: josh@agliodbs.com; Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-performance
Josh wrote...
> Achilleus,
>
> > I am afraid it is not so simple.
> > What i (unsuccessfully) implied is that
> > dynacom=# VACUUM ANALYZE status ;
> > VACUUM
> > dynacom=# ANALYZE status ;
> > ANALYZE
> > dynacom=#
> >
> > [is enuf to damage the performance.]
>
> You're right, that is mysterious. If you don't get a response from one of
> the major developers on this forum, I suggest that you post those EXPLAIN
> results to PGSQL-BUGS.
I had the same problem a while back.
http://archives.postgresql.org/pgsql-bugs/2002-08/msg00015.php
http://archives.postgresql.org/pgsql-bugs/2002-08/msg00018.php
http://archives.postgresql.org/pgsql-bugs/2002-08/msg00018.php
Short summary: Later in the thread Tom explained my problem as free
space not being evenly distributed across the table so ANALYZE's
sampling gave skewed results. In my case, "pgstatuple" was a
good tool for diagnosing the problem, "vacuum full" fixed my table
and a much larger fsm_* would have probably prevented it.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] 7.3 analyze & vacuum analyze problem
@ 2003-05-01 00:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ron Mayer <ron@intervideo.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2003-05-01 00:10 UTC (permalink / raw)
To: Ron Mayer <ron@intervideo.com>; +Cc: josh@agliodbs.com, "Achilleus Mantzios" <achill@matrix.gatewaynet.com>; pgsql-performance
"Ron Mayer" <ron@intervideo.com> writes:
> Short summary: Later in the thread Tom explained my problem as free
> space not being evenly distributed across the table so ANALYZE's
> sampling gave skewed results. In my case, "pgstatuple" was a
> good tool for diagnosing the problem, "vacuum full" fixed my table
> and a much larger fsm_* would have probably prevented it.
Not sure if that is Achilleus' problem or not. IIRC, there should be
no difference at all in what VACUUM ANALYZE and ANALYZE put into
pg_statistic (modulo random sampling variations of course). The only
difference is that VACUUM ANALYZE puts an exact tuple count into
pg_class.reltuples (since the VACUUM part groveled over every tuple,
this info is available) whereas ANALYZE does not scan the entire table
and so has to put an estimate into pg_class.reltuples.
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.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] 7.3 analyze & vacuum analyze problem
@ 2003-05-01 01:51 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 9+ messages in thread
From: Christopher Kings-Lynne @ 2003-05-01 01:51 UTC (permalink / raw)
To: josh@agliodbs.com; Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-performance
> > I am afraid it is not so simple.
> > What i (unsuccessfully) implied is that
> > dynacom=# VACUUM ANALYZE status ;
> > VACUUM
> > dynacom=# ANALYZE status ;
> > ANALYZE
> > dynacom=#
>
> You're right, that is mysterious. If you don't get a response from one
of
> the major developers on this forum, I suggest that you post those EXPLAIN
> results to PGSQL-BUGS.
Is it mysterious? The ANALYZE histogram algorithm does do random sampling
doesn't it?
Chris
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] 7.3 analyze & vacuum analyze problem
@ 2003-05-02 16:29 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Achilleus Mantzios @ 2003-05-02 16:29 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Ron Mayer <ron@intervideo.com>; josh@agliodbs.com; pgsql-performance; Achilleus Mantzios <achill@matrix.gatewaynet.com>
On Wed, 30 Apr 2003, Tom Lane wrote:
> "Ron Mayer" <ron@intervideo.com> writes:
> > Short summary: Later in the thread Tom explained my problem as free
> > space not being evenly distributed across the table so ANALYZE's
> > sampling gave skewed results. In my case, "pgstatuple" was a
> > good tool for diagnosing the problem, "vacuum full" fixed my table
> > and a much larger fsm_* would have probably prevented it.
>
> Not sure if that is Achilleus' problem or not. IIRC, there should be
> no difference at all in what VACUUM ANALYZE and ANALYZE put into
> pg_statistic (modulo random sampling variations of course). The only
> difference is that VACUUM ANALYZE puts an exact tuple count into
> pg_class.reltuples (since the VACUUM part groveled over every tuple,
> this info is available) whereas ANALYZE does not scan the entire table
> and so has to put an estimate into pg_class.reltuples.
>
> 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.
Unfortunately i did a VACUUM FULL, and later a dump/reload
which eliminated (vanished) the problem regarding the difference between
plain ANALYZE and VACUUM ANALYZE.
However, now the condition is much more wierd, in the sense
that after the reload, some planner costs seem too low (~ 6)
the expected number of rows is very often 1,
and the correct index is used, resulting in a
ultra speed situation (that i never had expected!).
After vacuum full analyze, or vacuum analyze
things get slow again.
I surely must generate a reproducable scenario,
describing the exact steps made, so i'll focus
on that.
In the meantime if Tom or some other hacker
has any ideas that would be great.
>
> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] 7.3 analyze & vacuum analyze problem
@ 2003-05-02 19:03 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 9+ messages in thread
From: Achilleus Mantzios @ 2003-05-02 19:03 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Ron Mayer <ron@intervideo.com>; josh@agliodbs.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-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@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2003-05-02 19:03 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-30 20:57 7.3 analyze & vacuum analyze problem Achilleus Mantzios <achill@matrix.gatewaynet.com>
2003-04-30 16:03 ` Josh Berkus <josh@agliodbs.com>
2003-04-30 21:13 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2003-04-30 18:48 ` Josh Berkus <josh@agliodbs.com>
2003-04-30 22:16 ` Ron Mayer <ron@intervideo.com>
2003-05-01 00:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-05-02 16:29 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2003-05-02 19:03 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2003-05-01 01:51 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
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