public inbox for pgsql-performance@postgresql.org  

help / color / mirror / Atom feed

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
Date: Fri, 10 Nov 2017 16:19:41 -0500
Message-ID: <9772.1510348781@sss.pgh.pa.us> (raw)
In-Reply-To: <20171110204043.GS8563@telsasoft.com>
References: <20171110204043.GS8563@telsasoft.com>
List-Unsubscribe:  <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>

Justin Pryzby <pryzby@telsasoft.com> 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 (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, pryzby@telsasoft.com
  Subject: Re: overestimate on empty table
  In-Reply-To: <9772.1510348781@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox