Search Postgresql Archives

Re: Indices for select count(*)?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Martijn van Oosterhout wrote:
-- Start of PGP signed section.
> On Fri, Dec 23, 2005 at 11:04:50AM -0500, Tom Lane wrote:
> > It's not that easy --- in the MVCC world there simply isn't a unique
> > count that is the right answer for every observer.  But the idea of
> > packaging a count(*) mechanism as an index type seems like it might be
> > a good one.  I don't think the planner objection need be taken too
> > seriously: we already have a good big wart in there for recognizing
> > MIN/MAX indexability, and this sort of transformation would fit pretty
> > naturally with what's already done in planagg.c.
> 
> AFAICS two big problems with using an index type:
> 
> 1. The index isn't told when the tuple is deleted.
> 2. The server expects to be able to lookup an index.
> 
> Other than that...

I think our TODO has a good summary of the issues:

---------------------------------------------------------------------------

* Speed up COUNT(*)

  We could use a fixed row count and a +/- count to follow MVCC
  visibility rules, or a single cached value could be used and
  invalidated if anyone modifies the table.  Another idea is to
  get a count directly from a unique index, but for this to be
  faster than a sequential scan it must avoid access to the heap
  to obtain tuple visibility information.

* Add estimated_count(*) to return an estimate of COUNT(*)

  This would use the planner ANALYZE statistics to return an estimated
  count.

* Allow data to be pulled directly from indexes

  Currently indexes do not have enough tuple visibility information
  to allow data to be pulled from the index without also accessing
  the heap.  One way to allow this is to set a bit on index tuples
  to indicate if a tuple is currently visible to all transactions
  when the first valid heap lookup happens.  This bit would have to
  be cleared when a heap tuple is expired.

  Another idea is to maintain a bitmap of heap pages where all rows
  are visible to all backends, and allow index lookups to reference
  that bitmap to avoid heap lookups, perhaps the same bitmap we might
  add someday to determine which heap pages need vacuuming.  Frequently
  accessed bitmaps would have to be stored in shared memory.  One 8k
  page of bitmaps could track 512MB of heap pages.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@xxxxxxxxxxxxxxxx               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux