Suboptimal execution plan for simple query

Hi!

We've got a table with the following definition:

CREATE TABLE image_relation
(
  id integer,
  article_id integer NOT NULL,
  entity_id integer NOT NULL,
  image_id integer NOT NULL,
  subline text,
  "position" integer,
  article_headline text,
  entity_name text,
  entity_type_id integer,
  entity_source text,
  default_pic character varying(3) NOT NULL,
  last_updated timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT pkey_image_relation PRIMARY KEY (article_id, entity_id, image_id)
)
WITH (OIDS=FALSE);

There are simple btree indexes on article_id, default_pic, entity_id, id, image_id, last_updated and position. The
tablehas about 723,000 rows, stats say table size is 135MB, toast tables are 184MB and index size was at a whopping
727MB- so I thought I might do some additional maintenance. After reindexing, I got index size down to 131MB. This
howeverdid not affect the planner choices in any way, as they and the resulting execution times stayed the same before
andafter table maintenance (reindex and subsequent vacuum analyze). Our PostgreSQL version is 8.2.4 (I am going to move
onto the latest and greatest 8.3 in about two weeks). 

Now I've got this simple query

SELECT  image_id
FROM image_relation
WHERE entity_id = 69560::integer
ORDER BY last_updated DESC
LIMIT    1;

which currently runs for something around 600ms. Here's the explain analyze output:

"Limit  (cost=0.00..144.78 rows=1 width=12) (actual time=599.745..599.747 rows=1 loops=1)"
"  ->  Index Scan Backward using idx_image_relation_last_updated on image_relation  (cost=0.00..39525.70 rows=273
width=12)(actual time=599.741..599.741 rows=1 loops=1)" 
"        Filter: (entity_id = 69560)"
"Total runtime: 599.825 ms"

SELECT  image_id
FROM image_relation
WHERE entity_id = 69560::integer;

only returns three rows. So I wonder why the planner chooses to use the index on last_updated instead of the index on
entity_id;I found out that I can get it to reconsider and make a wiser choice by adding some seemingly superfluous
statementto the WHERE clause (notice the AND... bit): 

SELECT  image_id
FROM image_relation
WHERE entity_id = 69560
AND entity_id = entity_id
ORDER BY last_updated DESC
LIMIT    1

"Limit  (cost=881.82..881.82 rows=1 width=12) (actual time=0.097..0.099 rows=1 loops=1)"
"  ->  Sort  (cost=881.82..881.82 rows=1 width=12) (actual time=0.094..0.094 rows=1 loops=1)"
"        Sort Key: last_updated"
"        ->  Index Scan using idx_image_relation_entity_id on image_relation  (cost=0.00..881.81 rows=1 width=12)
(actualtime=0.063..0.075 rows=3 loops=1)" 
"              Index Cond: (entity_id = 69560)"
"              Filter: (entity_id = entity_id)"
"Total runtime: 0.128 ms"

That's much more like it. The table is being vacuumed on a regular basis by both a nightly cron and the autovacuum
daemon. 

Any ideas on what's going wrong here?

Kind regards

   Markus


Computec Media AG
Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818)
Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch
Vorsitzender des Aufsichtsrates: Jürg Marquard
Umsatzsteuer-Identifikationsnummer: DE 812 575 276