Re: [PERFORM] overestimate on empty table

From: Tom Lane
Subject: Re: [PERFORM] overestimate on empty table
Date: ,
Msg-id: 9772.1510348781@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: [PERFORM] overestimate on empty table  (Justin Pryzby)
Responses: Re: [PERFORM] overestimate on empty table  (Justin Pryzby)
List: pgsql-performance

Tree view

[PERFORM] overestimate on empty table  (Justin Pryzby, )
 Re: [PERFORM] overestimate on empty table  (Tom Lane, )
  Re: [PERFORM] overestimate on empty table  (Justin Pryzby, )
   Re: [PERFORM] overestimate on empty table  (Tom Lane, )

Justin Pryzby <> writes:
> As the queued_alters table is typically empty (and autoanalyzed with
> relpages=0), I see "why":

> ./src/backend/optimizer/util/plancat.c
> |                        if (curpages < 10 &&
> |                                rel->rd_rel->relpages == 0 &&
> |                                !rel->rd_rel->relhassubclass &&
> |                                rel->rd_rel->relkind != RELKIND_INDEX)
> |                                curpages = 10;

So I'm sure you read the comment above that, too.

I'm loath to abandon the principle that the planner should not believe
that tables are empty/tiny without some forcing function.  There are
going to be way more people screaming about the plans they get from
too-small rowcount estimates than the reverse.  However, maybe we could
do better about detecting whether a vacuum or analyze has really happened.
(Autovacuum won't normally touch a table until a fair number of rows have
been put in it, so if a table is tiny but has been vacuumed, we can
presume that that was a manual action.)

One idea is to say that relpages = reltuples = 0 is only the state that
prevails for a freshly-created table, and that VACUUM or ANALYZE should
always set relpages to at least 1 even if the physical size is zero.
Dunno if that would confuse people.  Or we could bite the bullet and
add a "relanalyzed" bool flag to pg_class.  It's not like that's going
to be a noticeable percentage increase in the row width ...

> But is there a better way (I don't consider adding a row of junk to be a significant improvement).

Not ATM.
        regards, tom lane


--
Sent via pgsql-performance mailing list ()
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


pgsql-performance by date:

From: Tom Lane
Date:
Subject: Re: [PERFORM] overestimate on empty table
From: Justin Pryzby
Date:
Subject: Re: [PERFORM] overestimate on empty table