oms=# select version();
                           version                           
-------------------------------------------------------------
 PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

oms=# create temp table __item_id as select item_id from items where branch='10' and date_modified >= '09/01/2002';
SELECT

oms=# select count(*) from __item_id;
 count 
-------
     4
(1 row)

oms=# select count(*) from itemindex;
 count 
-------
 66072
(1 row)



/*
  HERE IS WHERE THE TROUBLE STARTS...
  Why does the index scan claim to have found 57387 rows?  It *should* find 13, and this step should
  naturally then be quite fast, but it appears that this step is what takes all the time.
*/
oms=# explain analyze select i.* from itemindex i, __item_id l where i.item_id = l.item_id order by i.item_id,i.seq;
NOTICE:  QUERY PLAN:

Sort  (cost=4117.20..4117.20 rows=2107 width=54) (actual time=1060.14..1060.16 rows=13 loops=1)
  ->  Merge Join  (cost=69.83..4000.90 rows=2107 width=54) (actual time=0.73..1059.79 rows=13 loops=1)
        ->  Index Scan using idx_itemindex_itemid on itemindex i  (cost=0.00..3737.06 rows=66072 width=50) (actual time=0.37..915.49 rows=57387 loops=1)
        ->  Sort  (cost=69.83..69.83 rows=1000 width=4) (actual time=0.12..0.14 rows=9 loops=1)
              ->  Seq Scan on __item_id l  (cost=0.00..20.00 rows=1000 width=4) (actual time=0.01..0.03 rows=4 loops=1)
Total runtime: 1060.46 msec

EXPLAIN

oms=# select i.* from itemindex i, __item_id l where i.item_id = l.item_id order by i.item_id,i.seq;
<DATA SNIPPED>
(13 rows)

oms=# analyze __item_id;
ANALYZE



/*
  HUH?
  Analyzing a 4 row table makes a real difference??  The query does a seq-scan on the temp table
  whether its been analyzed or not.  This is a temp table; trying to write portable
  SQL dictates that I can't use an analyze here.
*/
oms=# explain analyze select i.* from itemindex i, __item_id l where i.item_id = l.item_id order by i.item_id,i.seq;
NOTICE:  QUERY PLAN:

Sort  (cost=40.30..40.30 rows=8 width=54) (actual time=1.02..1.03 rows=13 loops=1)
  ->  Nested Loop  (cost=0.00..40.17 rows=8 width=54) (actual time=0.11..0.82 rows=13 loops=1)
        ->  Seq Scan on __item_id l  (cost=0.00..1.04 rows=4 width=4) (actual time=0.02..0.04 rows=4 loops=1)
        ->  Index Scan using idx_itemindex_itemid on itemindex i  (cost=0.00..9.76 rows=2 width=50) (actual time=0.05..0.15 rows=3 loops=4)
Total runtime: 1.25 msec

EXPLAIN