Re: Improving count(*)

On Tue, Nov 22, 2005 at 06:11:01PM -0500, Bruce Momjian wrote:
> mark(at)mark(dot)mielke(dot)cc wrote:
> Jan has been talking about have a bitmap to track pages that need
> vacuuming, and I am wondering if the same system could be used to track
> the heap-dirty bits. Putting one bit on every 8k disk page means we have
> to load the 8k page to read the bit, while a centralized bitmap would
> load 64k page bits in a single 8k page. That one page would cover 500MB
> of a table. Seems vacuum could use the same bitmap values.
>
> Assuming we track 100 tables regularly, that would require 800k of shared
> memory. We would have to pagein/out the bitmaps as needed, but we could
> throw them away on a crash and rebuild as part of normal operation.
>
> FSM has not been a concurrency bottleneck, so I am thinking this would
> not be either.
>
> I suppose it would require a new filesystem file for each table.

ISTM that the requirements here are very similar to the requirements for
the FSM, at least from a high-level: Track all pages where condition X
is true. Is there value to using the same framework for both cases?
Maybe it makes more sense to store free space info for a relation using
a bitmap, rather than storing individual page numbers. Or conversely,
storing 'dirty page info' should maybe be done in a similar fasion to
FSM instead of a bitmap.

If we wanted to provide the ultimate in tunability we'd offer both
solutions; some tables will have a large number of pages with free space
on them (which would favor storing that info in a bitmap); likewise some
tables will have a small number of pages that are 'dirty', which would
favor storing a list of page numbers instead of a bitmap.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461