Re: Much Ado About COUNT(*)

On Wed, 2005-01-12 at 15:09 -0500, Rod Taylor wrote:
> On Wed, 2005-01-12 at 12:52 -0700, Jonah H. Harris wrote:
> > Tom Lane wrote:
> >
> > >The fundamental problem is that you can't do it without adding at least
> > >16 bytes, probably 20, to the size of an index tuple header. That would
> > >double the physical size of an index on a simple column (eg an integer
> > >or timestamp). The extra I/O costs and extra maintenance costs are
> > >unattractive to say the least. And it takes away some of the
> > >justification for the whole thing, which is that reading an index is
> > >much cheaper than reading the main table. That's only true if the index
> > >is much smaller than the main table ...
>
> > I recognize the added cost of implementing index only scans. As storage
> > is relatively cheap these days, everyone I know is more concerned about
> > faster access to data. Similarly, it would still be faster to scan the
> > indexes than to perform a sequential scan over the entire relation for
> > this case. I also acknowledge that it would be a negative impact to
> > indexes where this type of acces isn't required, as you suggested and
> > which is more than likely not the case. I just wonder what more people
> > would be happier with and whether the added 16-20 bytes would be
> > extremely noticable considering most 1-3 year old hardware.
>
> I'm very much against this. After some quick math, my database would
> grow by about 40GB if this was done. Storage isn't that cheap when you
> include the hot-backup master, various slaves, RAM for caching of this
> additional index space, backup storage unit on the SAN, tape backups,
> additional spindles required to maintain same performance due to
> increased IO because I don't very many queries which would receive an
> advantage (big one for me -- we started buying spindles for performance
> a long time ago), etc.
>
> Make it a new index type if you like, but don't impose any new
> performance constraints on folks who have little to no advantage from
> the above proposal.

Jonah,

People's objections are:
- this shouldn't be the system default, so would need to be implemented
as a non-default option on a b-tree index
- its a lot of code and if you want it, you gotta do it

Remember you'll need to
- agree all changes via the list and accept that redesigns may be
required, even at a late stage of coding
- write visibility code into the index
- write an additional node type to handle the new capability
- microarchitecture performance testing so you know whether its really
worthwhile, covering a range of cases
- add code to the optimiser to so it can estimate the cost of using this
and to know when to do this
- add a column to the catalog to record whether an index has the
visibility option
- add code to the parser to invoke the option
- update pg_dump so that it correctly dumps tables with that option
- copy and adapt all of the existing tests for the new mechanism
- document it

If you really want to do all of that, I'm sure you'd get help, but
mostly it will be you that has to drive the change through.

There are some other benefits of that implementation:
You'd be able to vacuum the index (only), allowing index access to
remain reasonably constant, even as the table itself grew from dead
rows.

The index could then make sensible the reasonably common practice of
using a covered index - i.e. putting additional columns into the index
to satisfy the whole query just from the index.

--
Best Regards, Simon Riggs