Index not used - now me - Mailing list pgsql-sql

From Christoph Haller
Subject Index not used - now me
Date
Msg-id 200402091250.NAA18174@rodos
Whole thread Raw
Responses Re: Index not used - now me
List pgsql-sql
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 nulldim_pointer   | smallint | not
nulldimensions_nr| smallint | not nullfirst         | smallint | not nulllast          | smallint | not nullmax_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_DimRangeWHERE 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  ->
IndexScan 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_DimRangeWHERE Dim_Pointer = 162    ORDER
BYDim_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.93rows=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 nullfrom_module    | smallint | not
nullto_module     | smallint | not nullprimary_key    | integer  | not null
 
Indexes: tmr_primkey_index btree (primary_key)

explain analyze SELECT Module_Pointer FROM Transfer_ModRangeWHERE Primary_Key  = 13 AND From_Module <= 2 AND To_Module
>= 2 ;                                                             QUERY PLAN
 

--------------------------------------------------------------------------------------------------------------------------------------Index
Scanusing 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_ModRangeWHERE Primary_Key  = 13 AND From_Module <= 2 AND To_Module
>= 2 ;                                                 QUERY PLAN
 
--------------------------------------------------------------------------------------------------------------Seq Scan
ontransfer_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 



pgsql-sql by date:

Previous
From: Mark Gibson
Date:
Subject: Implementation of a bag pattern using rules
Next
From: Robert Creager
Date:
Subject: Re: Implementation of a bag pattern using rules