Re: Creating index does not make any change in query plan.

On Mon, Feb 17, 2003 at 01:45:21PM -0800, John Edstrom wrote:
> >On Mon, Feb 17, 2003 at 01:06:26PM +0530, Deepa wrote:
> >Make sure you've run analyze recently and your tables are big enough to
> >make
> >an index scan worthwhile.
> >
>
> I don't think that this will solve the problem. I've uncovered a
> similar problem recently. Vacuuming invalidates indexes, at
> least as far as I can tell. Here is an example:

Umm, did you read my statement? A table with one row is not worth using an
index. What you're seeing is the ANALYZE (not the VACUUM) updating the
statistics to say "an index scan is brain dead here". What I'm more curious
about is why the REINDEX caused it to forget the statistics, thus making it
use the brain-dead index scan again.

> ----------------------------------------------------------------------
> web=# create table t1 ( i int primary key );
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> 't1_pkey' for table 't1'
> CREATE
> web=# explain select * from t1 where i = 10;
> NOTICE: QUERY PLAN:
>
> Index Scan using t1_pkey on t1 (cost=0.00..4.82 rows=1 width=4)
>
> EXPLAIN
> web=# vacuum analyze t1;
> VACUUM
> web=# explain select * from t1 where i = 10;
> NOTICE: QUERY PLAN:
>
> Seq Scan on t1 (cost=0.00..0.00 rows=1 width=4)
>
> EXPLAIN
> web=# reindex table t1;
> REINDEX
> web=# explain select * from t1 where i = 10;
> NOTICE: QUERY PLAN:
>
> Index Scan using t1_pkey on t1 (cost=0.00..4.82 rows=1 width=4)
>
> EXPLAIN
> --------------------------------------------------------------------

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Support bacteria! They're the only culture some people have.