Thread: index performance question
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...
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.
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...
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
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