pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michael Richards <miker@scifair.acadiau.ca>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Stats on new tables
Date: Sun, 24 Oct 1999 16:09:30 -0400
Message-ID: <4801.940795770@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.BSF.4.10.9910241553001.46045-100000@scifair.acadiau.ca>

Michael Richards <miker@scifair.acadiau.ca> writes:
> I think I've found something of interest.
> When I create a new table, the plan for it believes that the table
> contains 2 rows, not 0.

No, actually the initial default assumption under 6.5 & up is that
the table contains 1000 rows.  (It *used* to be that relntuples started
off zero, but that led the optimizer to pick plans that were only
suitable for very small tables, which led to horrible performance if
you loaded up a table without doing a vacuum.)

The particular example you're looking at involves a default assumption
about the selectivity of an '=' WHERE condition as well as a default
assumption about the total table size.  The 'rows' field of a plan
node shows the estimated number of *output* tuples, not the number of
tuples that need to be scanned.


> Also, since the engine knows when we run an
> insert, and also knows how many rows a delete nukes, wouldn't it be better
> to update the stats after every select and delete?

A lot easier said than done (consider concurrent transactions some
of which may abort).  We've discussed making it happen, but personally
I don't believe that the bookkeeping costs that would be incurred could
possibly be justified.  Certainly the optimizer's results wouldn't be
materially better with exact tuple counts than with approximate ones.

			regards, tom lane



view thread (3+ messages)  latest in thread

Message-ID: <4801.940795770@sss.pgh.pa.us>
Permalink:  ../4801.940795770@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/4801.940795770@sss.pgh.pa.us

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, miker@scifair.acadiau.ca
  Subject: Re: [SQL] Stats on new tables
  In-Reply-To: <4801.940795770@sss.pgh.pa.us>
  References: <Pine.BSF.4.10.9910241553001.46045-100000@scifair.acadiau.ca> <4801.940795770@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 DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox