another index question - Mailing list pgsql-general

From Sean Harding
Subject another index question
Date
Msg-id 20010401123932.J22353@dogcow.org
Whole thread Raw
Responses Re: another index question
List pgsql-general
I have another query that I expected to use an index but is doing a
sequential scan:

SELECT frm,rcpt,subject FROM mesg_headers WHERE mesgnum IN (SELECT mesgnum
 FROM mesg_headers ORDER BY mesgnum DESC LIMIT 1);

Here's the explain:

NOTICE:  QUERY PLAN:

Seq Scan on mesg_headers  (cost=0.00..46866049756.39 rows=374843 width=36)
  SubPlan
    ->  Materialize  (cost=125028.26..125028.26 rows=1 width=4)
          ->  Limit  (cost=125028.26..125028.26 rows=1 width=4)
                ->  Sort  (cost=125028.26..125028.26 rows=374843 width=4)
                      ->  Seq Scan on mesg_headers  (cost=0.00..81505.43 rows=374843 width=4)

EXPLAIN

There's an index on the mesgnum, and both queries independently use the
index:

email=# EXPLAIN SELECT frm,rcpt,subject FROM mesg_headers WHERE mesgnum IN (5,20);
NOTICE:  QUERY PLAN:

Index Scan using mesg_headers_pkey, mesg_headers_pkey on mesg_headers
(cost=0.00..9.98 rows=1 width=36)

EXPLAIN

email=# EXPLAIN SELECT mesgnum FROM mesg_headers ORDER BY mesgnum DESC LIMIT 1;
NOTICE:  QUERY PLAN:

Limit  (cost=0.00..1.47 rows=1 width=4)
  ->  Index Scan Backward using mesg_headers_pkey on mesg_headers
  (cost=0.00..550028.43 rows=374843 width=4)

EXPLAIN

So, if both of the queries use the index, why don't they use the index when
combined?

Thanks.

sean

--
Sean Harding sharding@dogcow.org        |"It's not a habit, it's cool
http://www.dogcow.org/sean/             | I feel alive."
                                        | --k's Choice

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Dissapearing indexes, what's that all about?
Next
From: "ADBAAMD"
Date:
Subject: Re: Ok, why isn't it using *this* index?