From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Esger Abbink" <pgadmin(at)bumblebeast(dot)com> |
Cc: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: problem concerning vacuum/statistics & query performance |
Date: | 2002-01-18 16:09:37 |
Message-ID: | 6813.1011370177@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
"Esger Abbink" <pgadmin(at)bumblebeast(dot)com> writes:
> if i manually VACUUM ANALYZE the db when it is pretty loaded (~300k
> rows) the planner starts using a different plan which runs acceptable
> performance wise. however after the daily cleanup it reverts back to
> the old (bad) plan.
> so it seems the nightly VACUUM does some analyzing as well?
Plain VACUUM (no ANALYZE) won't touch pg_statistic, but it does update
the pages and tuples estimates in pg_class.
> in which case the planner is using totally off-base statistics for
> most of the day, which in itself isnt bad but i'd rather have it use
> large table stats on small tables than vice versa.
You could do this in your nightly script:
-- delete lotsa stuff from mytable;
vacuum mytable;
update pg_class set reltuples = M, relpages = N where relname = 'mytable';
where M and N correspond to the peak values instead of the minima.
Kinda grotty but it'll get the job done.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Esger Abbink | 2002-01-18 16:12:51 | duplicate post |
Previous Message | Esger Abbink | 2002-01-18 15:45:06 | problem concerning vacuum/statistics & query performance |