Index not being used in MAX function (7.2.3)

From: Paulo Jan <admin(at)digital(dot)ddnet(dot)es>
To: pgsql-general(at)postgresql(dot)org
Subject: Index not being used in MAX function (7.2.3)
Date: 2003-06-10 16:39:49
Message-ID: 3EE609D5.7000506@digital.ddnet.es
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message mallah 2003-06-10 16:57:00 Dependency tracking while dropping a database.
Previous Message Bruce Momjian 2003-06-10 15:55:33 Re: host and hostssl equivalence in pg_hba.conf