From: Christoph Haller <ch@rodos.fzk.de>
To: paul@tmsl.demon.co.uk
Cc: pgsql-sql@postgresql.org
Subject: Re: Index not used - now me
Date: Mon, 09 Feb 2004 16:02:40 MET
Message-ID: <200402091502.QAA19246@rodos> (raw)
In-Reply-To: <20040209142819.A12693@bacon>
>
>
> On 09/02/2004 12:50 Christoph Haller wrote:
>
> >
> > \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.
>
> That's because it's acually more efficent to do a seqscan on your small
> table. When you have only a small table (like many of us do when testing),
> the whole table will probably fit on one 8K page so the lowest cost
> operation (= quickest) is to get that page. It was disabling seqscan that
> was forcing an index scan to appear to be the least costly operation. BTW,
> you can't actually prevent PG doing a seqscan if there's no alternative
> plan. All set enable_seqscan = false does is make a seqscan appear very
> expensive so that the planner is less likely to pick it.
>
> HTH
>
> --
> Paul Thomas
>
Thanks for your reply so far, but there is one thing I still don't understand.
You wrote
It was disabling seqscan that
was forcing an index scan to appear to be the least costly operation.
Why appear? If the Index Scan has a Total runtime: 2.46 msec and the Seq Scan
a Total runtime: 46.19 msec, then the Index Scan is much faster.
Or am I completely off the track reading the explain analyze output?
Again, thanks for your time.
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, paul@tmsl.demon.co.uk
Subject: Re: Index not used - now me
In-Reply-To: <200402091502.QAA19246@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