Re: Indexes works only on miss

Thank you for exhaustive explanation, this is the output with analyze :

"Seq Scan on test (cost=0.00..120.67 rows=627 width=11) (actual time=
0.018..5.467 rows=621 loops=1)"
" Filter: (("Owner")::text = 'root'::text)"
"Total runtime: 7.288 ms"

"Index Scan using idx_test_owner on test (cost=0.00..96.75 rows=28
width=11) (actual time=0.025..0.025 rows=0 loops=1)"
" Index Cond: (("Owner")::text = 'blah'::text)"
"Total runtime: 0.064 ms"

I tried a search term in query with less rows and it used index scan too. I
gues I didn't understand how indexes work.

Thanks for help, Sebastjan

On 1/6/06, Michael Fuhr <mike(at)fuhr(dot)org> wrote:
>
> 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
>