Thread: index performance question

index performance question

From
Laurette Cisneros
Date:
Not sure the right forum to post this...

I have a table that has 20 million rows and growing.  One of the columns is a
timestamptz column.

If I do:

explain select * from bigtable where tscol > '2002-09-17';

I see:
NOTICE:  QUERY PLAN:

Index Scan using pos_timeidx on positions_plus  (cost=0.00..46519.71
rows=1425978 width=108)

EXPLAIN

But, when I do:
explain select min(evtime) from positions_plus;

I see:
NOTICE:  QUERY PLAN:

Aggregate  (cost=548394.90..548394.90 rows=1 width=8)
  ->  Seq Scan on positions_plus  (cost=0.00..499146.92 rows=19699192
width=8)

EXPLAIN

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?

Thanks,

--
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
----------------------------------
A wiki we will go...



Re: index performance question

From
Bruno Wolff III
Date:
On Wed, Sep 18, 2002 at 13:09:07 -0700,
  Laurette Cisneros <laurette@nextbus.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.

Re: index performance question

From
Laurette Cisneros
Date:
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@nextbus.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...


Re: index performance question

From
Andrew Sullivan
Date:
On Wed, Sep 18, 2002 at 01:59:13PM -0700, Laurette Cisneros wrote:

> 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

I think that trade-off is probably bigger than you realise, given
that the customisability is a big feature.  It's true that min() and
max() are pretty standard ways of doing things, so PostgreSQL is
pretty strange in this case.  But hey, it's not like Postgres is the
_only_ strnage one here.

> pgsql have the capabilities that are needed it doesn't even have replication
> or the ability to query more than one database.

There _is_ replication for Postgres, and the "more than one database"
stuff will be handled nicely by schema support, I think.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110


Re: index performance question

From
"Nikolaus Dilger"
Date:
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@nextbus.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@postgresql.org
so
> that your
> message can get through to the mailing list cleanly