Re: another index question

Sean Harding <sharding(at)dogcow(dot)org> writes:
>> This looks like a bug. What version are you running, exactly?

> Sorry. Should have mentioned that. It's 7.1RC1 on linux 2.4.2.

Hm. Curious. I can't reproduce the problem:

regression=# create table mesg_headers (mesgnum int, frm text,
regression(# rcpt text, subject text);
CREATE
regression=# create index mesg_msgnum on mesg_headers(mesgnum);
CREATE
regression=# explain
regression-# SELECT frm,rcpt,subject FROM mesg_headers WHERE mesgnum IN (SELECT
mesgnum
regression(# FROM mesg_headers ORDER BY mesgnum DESC LIMIT 1);
NOTICE: QUERY PLAN:

Seq Scan on mesg_headers (cost=0.00..81.50 rows=1000 width=36)
SubPlan
-> Materialize (cost=0.06..0.06 rows=1 width=4)
-> Limit (cost=0.00..0.06 rows=1 width=4)
-> Index Scan Backward using mesg_msgnum on mesg_headers (cost
=0.00..59.00 rows=1000 width=4)

EXPLAIN
regression=#

Could you send me the full table schema ("pg_dump -s -t mesg_headers dbname"
would do nicely) as well as the statistics from

select attname,attdispersion,s.*
from pg_statistic s, pg_attribute a, pg_class c
where starelid = c.oid and attrelid = c.oid and staattnum = attnum
and relname = 'mesg_headers';

regards, tom lane