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