From: Greg Stark <gsstark@mit.edu>
To: Christoph Haller <ch@rodos.fzk.de>
Cc: paul@tmsl.demon.co.uk
Cc: pgsql-sql@postgresql.org
Subject: Re: Index not used - now me
Date: 09 Feb 2004 10:43:21 -0500
Message-ID: <878yjcntra.fsf@stark.xeocode.com> (raw)
In-Reply-To: <200402091502.QAA19246@rodos>
References: <200402091502.QAA19246@rodos>
Christoph Haller <ch@rodos.fzk.de> writes:
> 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?
To estimate the relative costs of a sequential scan and an index scan Postgres
has to take into account the likelihood the blocks needed will be the disk
cache. In your example your database is otherwise idle and the entire table is
small enough that the entire index is probably in cache.
This means that the random access pattern of the index isn't really hurting
the index scan at all. Whereas in a busy database with less available RAM the
random access pattern makes a big difference.
You could try raising effective_cache_size to give postgres a better chance at
guessing that all the blocks will be in cache. But that may no longer be true
when the query is run on a busy database.
You could also try lowering random_page_cost. Some people find as low as 1.2
or so to be useful, but that would almost certainly be lying to postgres about
the costs of random access and would cause it to use index scans aggressively
even when they're not faster.
--
greg
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: gsstark@mit.edu, ch@rodos.fzk.de, paul@tmsl.demon.co.uk
Subject: Re: Index not used - now me
In-Reply-To: <878yjcntra.fsf@stark.xeocode.com>
* 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