From: Tom Lane <tgl@sss.pgh.pa.us>
To: Justin Pryzby <pryzby@telsasoft.com>
cc: pgsql-performance@postgresql.org
Subject: Re: overestimate on empty table
In-reply-to: <20171110204043.GS8563@telsasoft.com>
References: <20171110204043.GS8563@telsasoft.com>
Comments: In-reply-to Justin Pryzby <pryzby@telsasoft.com>
	message dated "Fri, 10 Nov 2017 14:40:43 -0600"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <9771.1510348781.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 10 Nov 2017 16:19:41 -0500
Message-ID: <9772.1510348781@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Justin Pryzby <pryzby@telsasoft.com> writes:
> As the queued_alters table is typically empty (and autoanalyzed with
> relpages=3D0), I see "why":

> ./src/backend/optimizer/util/plancat.c
> |                        if (curpages < 10 &&
> |                                rel->rd_rel->relpages =3D=3D 0 &&
> |                                !rel->rd_rel->relhassubclass &&
> |                                rel->rd_rel->relkind !=3D RELKIND_INDEX)
> |                                curpages =3D 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 =3D reltuples =3D 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


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