Thread

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

   Tom Lane <tgl@sss.pgh.pa.us> — 1999-10-29T03:57:59Z

   Brian E Gallew <geek+@cmu.edu> writes:
   > 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...
   
   Vadim will have to check me on this, but I believe that index entries
   don't contain transaction information --- that is, you can determine
   whether a tuple matches a specified search key by examining the index,
   but in order to discover whether the tuple is actually *valid*
   (according to your transaction's worldview) you must fetch the tuple
   itself from the main table.  So scanning an index cannot be cheaper than
   a sequential scan of the main table, except when the index allows you to
   avoid visiting most of the tuples in the main table.
   
   There has been some discussion of allowing scans of indexes without
   fetching the underlying tuples, but AFAICS that would mean replicating
   the tuple transaction status information into (each!) index, which'd
   be a big hit in both disk space and number of disk writes implied by
   committing a tuple.  I've got my doubts about it being a win...
   
   			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-29T04:51:51Z

   Then <tgl@sss.pgh.pa.us> spoke up and said:
   > Vadim will have to check me on this, but I believe that index entries
   > don't contain transaction information --- that is, you can determine
   > whether a tuple matches a specified search key by examining the index,
   > but in order to discover whether the tuple is actually *valid*
   > (according to your transaction's worldview) you must fetch the tuple
   > itself from the main table.  So scanning an index cannot be cheaper than
   > a sequential scan of the main table, except when the index allows you to
   > avoid visiting most of the tuples in the main table.
   
   Right.  As usual, I've overlooked something obvious.  So, this really
   wouldn't work unless we had an exclusive table lock ('cause then there
   wouldn't be any other transactions to worry about, except for our
   own).  Feh.
   
   -- 
   =====================================================================
   | 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.                            |
   =====================================================================