Re: [HACKERS] Bug#48582: psql spends hours computing results it already knows (fwd)

"Ross J. Reedstrom" <reedstrm(at)wallace(dot)ece(dot)rice(dot)edu> writes:
> Hmm, that happens to not be the case. The rows=XXXX number is drawn
> from the statistics for the table, which are only updated on VACUUM
> ANALYZE of that table. Easily tested: just INSERT a couple rows and do
> the EXPLAIN again. The rows=XXX won't change.

The short answer to this is that maintaining a perfectly accurate tuple
count on-the-fly would almost certainly cost more, totalled over all
operations that modify a table, than we could ever hope to make back
by short-circuiting "select count(*)" operations. (Consider
concurrent transactions running in multiple backends, some of which
may abort instead of committing, and others of which may already have
committed but your transaction is not supposed to be able to see their
effects...)

The optimizer is perfectly happy with approximate tuple counts, so it
makes do with stats recorded at the last VACUUM.

This has been discussed quite recently on pg-hackers; see the archives
for more info.

regards, tom lane