From: Christoph Haller <ch@rodos.fzk.de>
To: pgsql-sql@postgresql.org
Subject: Index not used - now me
Date: Mon, 09 Feb 2004 13:50:11 MET
Message-ID: <200402091250.NAA18174@rodos> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de
Subject: Re: Index not used - now me
In-Reply-To: <200402091250.NAA18174@rodos>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox