This is interesting... Perhaps a more knowledgable person for pgsql
could help us here...
I seem to remember something to do with the fact that You can't use
aggregate functions over an index... I'm not sure why though.
You can do:
create index foo on my_table (lower(my_column))
but not
create index foo on my_table(min(my_column)) - I guess it wouldn't be
much of an index - it would be a single value.
You could reproduce that functionality with a trigger that updated a
table that contained the value of (min(my_column)), and I guess you
could do the same fo count(*) too.
I guess what I"m really asking is why can't you run aggregates over an index?
Alex Turner
NetEconomist
On Fri, 7 Jan 2005 09:09:49 -0800, Culley Harrelson <[EMAIL PROTECTED]> wrote:
> On Fri, 07 Jan 2005 16:17:16 +0100, Tino Wildenhain <[EMAIL PROTECTED]> wrote:
> >
> > How do you think an index would help if you do an unconditional
> > count(*)?
>
> I really don't know <grin>. I don't know the inner workings of
> database internals but I would guess that there would be some
> optimized way of counting the nodes in an index tree that would be
> faster than sequentially going through a table.... I suppose there is
> no free lunch.
>
> One row, two rows, three rows, four rows, five rows.... <snore>
>
> culley
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
