Re: 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

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(at)beccati(dot)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

On 11/6/08, Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec> wrote:
> On Thu, Nov 6, 2008 at 9:24 AM, Matteo Beccati <php(at)beccati(dot)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 after the table has grown a lot, they are bad.) It would
* be better to force replanning if the table size has changed a
* lot since the plan was made ... but we don't currently have any
* infrastructure for redoing 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'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.

--
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