I thought there was also a problem with count(*) in that it would have to scan the tuple any way to check for visibility. Not 100% sure on that.
I believe the best way to do select max() is to:
SELECT * FROM tbl ORDER BY mx_field DESC LIMIT 1
This does of course have the problem of being none standards compliant.
I'm not sure how optimised this is so tell me if I'm talking gibberish :)
- Stuart
> -----Original Message-----
> From: Josh Berkus [mailto:josh@agliodbs.com]
> Sent: 31 July 2002 00:00
> To: Ron Johnson; PgSQL Novice ML
> Subject: Re: [NOVICE] Very slow query
>
>
> Ron,
>
> > A couple of months ago, I asked a similar question, when I saw
> > that that a COUNT(*) was scanning, even though it had a perfect
> > supporting index. This is the paraphrased answer:
> > The aggregate functions module is a complicated black box that
> > the developers are scared to look at and break. Besides, I
> > hardly ever use COUNT(*), so you don't need it either.
>
> That's the price we pay for having the ability to create
> custom aggregates.
> Since it's possible for me in Postgres to make an aggregate called
> comma_cat(varchar) that concatinates a varchar column into a
> comma-delimited
> list -- for example -- the parser cannot optimize for what
> goes on inside the
> aggregate.
>
> The hackers list has discussed the possibility of writing
> parser optimization
> just for the built-in aggregates for which aggregation is
> reasonable (COUNT,
> MIN, MAX). However, nobody who cares enough about the issue
> has stepped up
> to the plate to offer their code. And it would require
> *extensive* testing.
>
> Its a priority thing. Sometimes I'm annoyed that MAX() is
> slow in postgres.
> More times I'm annoyed that MSSQL does not support custom aggregates.
>
> And, this doesn't affect my advice to Chad. Indexing
> phonenum_substr *will*
> speed up the query by speeding up the JOIN portion.
>
> --
> -Josh Berkus
> Aglio Database Solutions
> San Francisco
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>
