Re: Indexes works only on miss

On Fri, Jan 06, 2006 at 05:42:41PM +0100, Sebastjan Trepca wrote:
> I really don't understand this behaviour. I have a table with column "owner"
> on which I created an index with btree method. The table contains around 3k
> rows.
>
> Now I run it using EXPLAIN command.

Please post the EXPLAIN ANALYZE output -- that'll show how accurate
the planner's estimates are.

Has the table been vacuumed and analyzed lately?

>This query has some results:
>
> explain SELECT "Name" FROM test WHERE "Owner"='root'
>
> "Seq Scan on test (cost=0.00..119.11 rows=263 width=11)"
> " Filter: (("Owner")::text = 'root'::text)"

The planner estimates that this query will return 263 rows; apparently
that's enough of the table that the planner thinks a sequence scan
would be faster than an index scan. An index scan has to hit the
index *and* the table, so it's a fallacy to assume that an index
scan will always be faster. You can play with enable_seqscan to
see if an index scan would indeed be faster. For example:

SET enable_seqscan TO off;
EXPLAIN ANALYZE SELECT ...
SET enable_seqscan TO on;
EXPLAIN ANALYZE SELECT ...

Be aware of disk caching when comparing execution times for different
queries: one query might be slower than another not because of a
less efficient plan, but rather because it had to fetch data from
disk and the "faster" query then took advantage of that cached data.
Run each query several times to allow for this.

> Query without results:
>
> explain SELECT "Name" FROM test WHERE "Owner"='blah'
>
> "Index Scan using idx_test_owner on test (cost=0.00..96.56 rows=28
> width=11)"
> " Index Cond: (("Owner")::text = 'blah'::text)"

The planner estimates that this query will return 28 rows, which
makes it more likely that an index scan would be faster because
that's a much smaller percentage of the table.

> Why is this happening? Is it because of the memory? I'm running on default
> db settings, version 8.0 and SUSE 10.

You can use various tuning guides to help adjust your settings.
Here are a couple of links:

http://www.powerpostgresql.com/PerfList
http://www.revsys.com/writings/postgresql-performance.html

Configuration settings can lead the planner to favor index scans,
but as I mentioned earlier, and index scan isn't always faster than
a sequential scan.

--
Michael Fuhr