Re: index performance question

Laurette,

It's not that easy. As soon as you do any operation on
a column you the optimizer can no longer use a normal
index. That's exactly why funcional indexes were
introduced.

It's easy to create
CREATE INDEX min_evtime
ON positions_plus (MIN(evtime));

BTW functional indexes were a new feature in Oracle 8i.
So its a relatively new feature in flagship comercial
database. And you would get the same poor performance
without the functional index in Oracle.

Regards,
Nikolaus

On Wed, 18 September 2002, Laurette Cisneros wrote:

>
> Yes, indeed.
>
> The workaround was quite simple (for min and max).
>
> But, having read the postings, I must cast my vote for
> fixing at least the
> standard aggregates so that they work faster (by being
> smarter). I realize
> that there is a trade off for allowing the building of
> custom aggregates
> but it is certainly not a good selling point (selling
> is relative of course
> since pgsql is free) if basic funcionality has bad
> performance.
>
> I deal with those that think we should be using, gulp,
> Oracle, a db I have
> up to now in my db career been able to avoid. Their
> argument, how could
> pgsql have the capabilities that are needed it doesn't
> even have replication
> or the ability to query more than one database.
>
> I try to convey the abilities that this server has but
> it's hard to back
> that up when the basic functions operate in a not so
> good manner.
>
> Just my 2 cents. And, don't get me wrong, I love
> postgresql.
>
> L.
>
> On Wed, 18 Sep 2002, Bruno Wolff III wrote:
>
> > On Wed, Sep 18, 2002 at 13:09:07 -0700,
> > Laurette Cisneros <laurette(at)nextbus(dot)com>
wrote:
> > >
> > > Aren't aggregates smart enough to use an
index on
> the column? This takes 8
> > > minutes to run! I can't see that I should
have to
> build a functional index
> > > (is it possible for an aggregate?) to get
this to
> run faster?
> >
> > I don't believe a functional index would work
for an
> aggregate.
> >
> > The way to do this is to use an order by clause
and a
> limit 1 clause.
> >
> > This has been discussed a lot on the lists and
you
> should be able to find
> > more details in the archives.
> >
>
> --
> Laurette Cisneros
> The Database Group
> (510) 420-3137
> NextBus Information Systems, Inc.
> www.nextbus.com
> ----------------------------------
> A wiki we will go...
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send
> an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org
so
> that your
> message can get through to the mailing list cleanly