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.
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 |