On Wed, 2005-03-09 at 16:43 +0100, Tom Ivar Helbekkmo wrote:
> Paul J Stevens <[EMAIL PROTECTED]> writes:
>
> > ERROR: cannot use aggregate function in index expression
> >
> > A recent discussion shows fixing this is on the TODO list for postgresql.
>
> That sounded as if they're planning to allow indexing on aggregate
> functions, which is, of course, meaningless. What is being talked
> about is doing something to speed up COUNT(*).
An aggregate index wouldn't be meaningless with GROUP BY- a better way
to look at it [syntax] would be indexes on views.
Some things from the PG TODO list that are interesting though:
* Use indexes for MIN() and MAX()
MIN/MAX queries can already be rewritten as SELECT col FROM tab ORDER BY
col {DESC} LIMIT 1. Completing this item involves doing this
transformation automatically.
* 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.
* Allow data to be pulled directly from indexes
Currently indexes do not have enough tuple 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 to 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.
--
Internet Connection High Quality Web Hosting
http://www.internetconnection.net/
