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

From Greg Stark
Subject Re: Index not used - now me
Date
Msg-id 878yjcntra.fsf@stark.xeocode.com
Whole thread Raw
In response to Re: Index not used - now me  (Christoph Haller <ch@rodos.fzk.de>)
Responses Re: Index not used - now me
List pgsql-sql
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



pgsql-sql by date:

Previous
From: "Mark Roberts"
Date:
Subject: timestamptz - problems
Next
From: Tom Lane
Date:
Subject: Re: Implementation of a bag pattern using rules