Re: Index not being used in MAX function (7.2.3) - Mailing list pgsql-general

Yep. It's a "feature" :-)
I had hard time understanding why too...
Apparently, there is nothing special about max() aggregate from the
query planner's standpoint, compared to any other aggregate function. It
doesn't know its specifics, and thus is not able to figure out that all
it really needs is to grab the first entry from the index...

To mak it quicker do this instead:

select id from todocinetv order by id desc limit 1;

I hope, it helps...

Dima

Paulo Jan wrote:

> Hi all:
>
>     I have here a table belonging to a message board (Phorum 3.3), and
> there's an index in it that is not being used for reasons that I don't
> understand. The table is:
>
>
>                         Table "todocinetv"
>    Column    |            Type             |      Modifiers
> -------------+-----------------------------+----------------------
>  id          | integer                     | not null default '0'
>  datestamp   | timestamp without time zone | not null
>  thread      | integer                     | not null default '0'
>  parent      | integer                     | not null default '0'
>  author      | character(37)               | not null default ''
>  subject     | character(255)              | not null default ''
>  email       | character(200)              | not null default ''
>  attachment  | character(64)               | default ''
>  host        | character(50)               | not null default ''
>  email_reply | character(1)                | not null default 'N'
>  approved    | character(1)                | not null default 'N'
>  msgid       | character(100)              | not null default ''
>  modifystamp | integer                     | not null default '0'
>  userid      | integer                     | not null default '0'
> Indexes: todocinetv_approved,
>          todocinetv_author,
>          todocinetv_datestamp,
>          todocinetv_modifystamp,
>          todocinetv_msgid,
>          todocinetv_parent,
>          todocinetv_subject,
>          todocinetv_thread,
>          todocinetv_userid,
>          todocinetvpri_key
>
>
>     And the index "todocinetvpri_key" is created on the primary key
> (id). Yet when I do:
>
> explain select max(id) from todocinetv;
> NOTICE:  QUERY PLAN:
>
> Aggregate  (cost=30939.22..30939.22 rows=1 width=4)
>   ->  Seq Scan on todocinetv  (cost=0.00..30882.98 rows=22498 width=4)
>
>
>     It doesn't use the index, and surely, it takes forever. I have
> tried with VACUUM ANALYZE, and also dropping the index, creating it
> again and VACUUMing it, and it never uses it. The only explanation I
> can come up with is that the MAX() function doesn't use indices; I
> have tried with tables in other databases (running Postgres 7.2.1),
> and it doesn't use the indices in any of them.
>     Is this the right behaviour? Or there is something else going on?
> The table mentioned above is in a Postgres 7.2.3 server, while the
> other ones that I used for testing were, as I said, in 7.2.1.
>
>
>
>                     Paulo Jan.
>                     DDnet.
>
>
>
> ---------------------------(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