Thread

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

   Tom Lane <tgl@sss.pgh.pa.us> — 1999-10-28T23:05:56Z

   "Ross J. Reedstrom" <reedstrm@wallace.ece.rice.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
   
   

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

   Brian E Gallew <geek+@cmu.edu> — 1999-10-29T02:44:12Z

   Then <tgl@sss.pgh.pa.us> spoke up and said:
   > 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...)
   
   So, does the planner allow counting from a unique index (if one
   exists)?  In general, an index scan on a unique index should be faster
   than a table scan.  Of course, I'm sure someone already thought of this...
   
   -- 
   =====================================================================
   | JAVA must have been developed in the wilds of West Virginia.      |
   | After all, why else would it support only single inheritance??    |
   =====================================================================
   | Finger geek@cmu.edu for my public key.                            |
   =====================================================================