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

Is your index a hash or btree?

Jon

On Tue, 10 Jun 2003, 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
>