Thread: regression in analyze
Hi all, Attached test shows a regression in analyze command. Expected rows in an empty table is 2140 even after an ANALYZE is executed -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157
Attachment
Hi, > Attached test shows a regression in analyze command. > Expected rows in an empty table is 2140 even after an ANALYZE is executed Doesn't seem to be a regression to me, as I've just checked that 8.0 did behave the same. However the question also was raised a few days ago on the italian mailing list and I couldn't find a reasonable explanation for it. Cheers -- Matteo Beccati OpenX - http://www.openx.org
On Thu, Nov 6, 2008 at 9:24 AM, Matteo Beccati <php@beccati.com> wrote: > Hi, > >> Attached test shows a regression in analyze command. >> Expected rows in an empty table is 2140 even after an ANALYZE is executed > > Doesn't seem to be a regression to me, as I've just checked that 8.0 did > behave the same. However the question also was raised a few days ago on > the italian mailing list and I couldn't find a reasonable explanation > for it. > mmm.... yeah! i'm seeing the same at 8.3 too :( -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157
Hi, > * We approximate "never vacuumed" by "has relpages = 0", which > * means this will also fire on genuinely empty relations. Not > * great, but fortunately that's a seldom-seen case in the real > * world, and it shouldn't degrade the quality of the plan too > * much anyway to err in this direction. > */ > if (curpages < 10 && rel->rd_rel->relpages == 0) > curpages = 10; > > > commenting that two lines make the estimates correct. now that we have > plan invalidation that hack is still needed? > i know that as the comment suggest this has no serious impact but > certainly this is user visible. I guess the reason is that a 0 estimate for a non empty table which was analyzed before the data was inserted and not yet analyzed again could cause much more troubles... anyway, I was just curious to get an "official" anwser ;) Cheers -- Matteo Beccati OpenX - http://www.openx.org
On 11/6/08, Jaime Casanova <jcasanov@systemguards.com.ec> wrote: > On Thu, Nov 6, 2008 at 9:24 AM, Matteo Beccati <php@beccati.com> wrote: >> Hi, >> >>> Attached test shows a regression in analyze command. >>> Expected rows in an empty table is 2140 even after an ANALYZE is >>> executed >> >> Doesn't seem to be a regression to me, as I've just checked that 8.0 did >> behave the same. However the question also was raised a few days ago on >> the italian mailing list and I couldn't find a reasonable explanation >> for it. >> > this is related to this hack: src/backend/optimizer/util/plancat.c:342 /* * HACK: if the relation has never yet been vacuumed, use a * minimum estimate of 10 pages. This emulates a desirable aspect * of pre-8.0 behavior, which is that we wouldn't assume a newly * created relation is really small, which saves us from making * really bad plans during initial data loading. (The plans are * not wrong when they are made, but if they are cached and used * again afterthe table has grown a lot, they are bad.) It would * be better to force replanning if the table size haschanged a * lot since the plan was made ... but we don't currently have any * infrastructure forredoing cached plans at all, so we have to * kluge things here instead. * * We approximate"never vacuumed" by "has relpages = 0", which * means this will also fire on genuinely empty relations. Not * great, but fortunately that's a seldom-seen case in the real * world, and it shouldn'tdegrade the quality of the plan too * much anyway to err in this direction. */ if(curpages < 10 && rel->rd_rel->relpages == 0) curpages = 10; commenting that two lines make the estimates correct. now that we have plan invalidation that hack is still needed? i know that as the comment suggest this has no serious impact but certainly this is user visible. -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157