Re: does "select count(*) from mytable" always do a seq scan?

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 <harrelson(at)gmail(dot)com> wrote:
> On Fri, 07 Jan 2005 16:17:16 +0100, Tino Wildenhain <tino(at)wildenhain(dot)de> 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
>