From: Christoph Haller <ch@rodos.fzk.de>
Message-Id: <200402091250.NAA18174@rodos>
Subject: Index not used - now me 
To: pgsql-sql@postgresql.org
Date: Mon, 09 Feb 2004 13:50:11 MET

I know there have been dozens of threads on this subject and 
I have searched the archives well (I hope at least), but still ... 

I have 
select version();
                           version
--------------------------------------------------------------
 PostgreSQL 7.3.4 on hppa-hp-hpux10.20, compiled by GCC 2.8.1

show enable_seqscan ;
 enable_seqscan
----------------
 off

\d ParDef_DimRange
    Table "public.pardef_dimrange"
    Column     |   Type   | Modifiers
---------------+----------+-----------
 primary_key   | integer  | not null
 dim_pointer   | smallint | not null
 dimensions_nr | smallint | not null
 first         | smallint | not null
 last          | smallint | not null
 max_range     | smallint | not null
Indexes: pd_dptr_index btree (dim_pointer),
         pd_pkey_index btree (primary_key)

explain analyze SELECT Dim_Pointer,Dimensions_Nr,First,Last       FROM ParDef_DimRange
 WHERE Dim_Pointer = 162::smallint    ORDER BY Dim_Pointer,Dimensions_Nr;
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=7.02..7.03 rows=2 width=8) (actual time=0.72..0.75 rows=2 loops=1)
   Sort Key: dim_pointer, dimensions_nr
   ->  Index Scan using pd_dptr_index on pardef_dimrange  (cost=0.00..7.01 rows=2 width=8) (actual time=0.20..0.28 rows=2 loops=1)
         Index Cond: (dim_pointer = 162::smallint)
 Total runtime: 1.24 msec

excellent, but 

explain analyze SELECT Dim_Pointer,Dimensions_Nr,First,Last       FROM ParDef_DimRange
 WHERE Dim_Pointer = 162    ORDER BY Dim_Pointer,Dimensions_Nr;
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=100000062.22..100000062.23 rows=2 width=8) (actual time=32.44..32.46 rows=2 loops=1)
   Sort Key: dim_pointer, dimensions_nr
   ->  Seq Scan on pardef_dimrange  (cost=100000000.00..100000062.21 rows=2 width=8) (actual time=11.06..31.93 rows=2 loops=1)
         Filter: (dim_pointer = 162)
 Total runtime: 32.79 msec

That's not nice. Will this go away on 7.4? 

\d Transfer_ModRange
   Table "public.transfer_modrange"
     Column     |   Type   | Modifiers
----------------+----------+-----------
 module_pointer | smallint | not null
 from_module    | smallint | not null
 to_module      | smallint | not null
 primary_key    | integer  | not null
Indexes: tmr_primkey_index btree (primary_key)

explain analyze SELECT Module_Pointer FROM Transfer_ModRange
 WHERE Primary_Key  = 13 AND From_Module <= 2 AND To_Module   >= 2 ;
                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using tmr_primkey_index on transfer_modrange  (cost=0.00..115.09 rows=14 width=2) (actual time=2.11..2.11 rows=0 loops=1)
   Index Cond: (primary_key = 13)
   Filter: ((from_module <= 2) AND (to_module >= 2))
 Total runtime: 2.46 msec

Now 
set enable_seqscan to on ;
explain analyze SELECT Module_Pointer FROM Transfer_ModRange
 WHERE Primary_Key  = 13 AND From_Module <= 2 AND To_Module   >= 2 ;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Seq Scan on transfer_modrange  (cost=0.00..104.93 rows=14 width=2) (actual time=45.91..45.91 rows=0 loops=1)
   Filter: ((primary_key = 13) AND (from_module <= 2) AND (to_module >= 2))
 Total runtime: 46.19 msec

That's odd. May I please have an explanation for this. 
Probably I should mention both tables have far less than 10.000 tuples. 
VACUUM and ANALYZE was done just before. 

TIA 

Regards, Christoph