Re: Index not being used in MAX function (7.2.3)

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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>