Re: indexes not being used!

We just finished doing this and the queries were slower with the indexes
being used. Apparently the optimizer knows what it is doing.

The root of the problem goes back to a very slow link on a web page. Of
course the programmer has stated that the problem is on the postgres side
but I have yet to find one.

Thanks
Jodi

----- Original Message -----
From: "Joe Conway" <mail(at)joeconway(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Jodi Kanter" <jkanter(at)virginia(dot)edu>; "Postgres Admin List"
<pgsql-admin(at)postgresql(dot)org>
Sent: Friday, March 21, 2003 1:12 PM
Subject: Re: [ADMIN] indexes not being used!

> Tom Lane wrote:
> > Jodi Kanter <jkanter(at)virginia(dot)edu> writes:
> >
> >>I apologize if this is the wrong list. I have posted explain analyzes
below.
> >
> >
> > Given the small size of the tables, I think the planner is doing the
> > right thing not to use indexes. Seqscans are probably less I/O until
> > the tables get a lot bigger.
>
> If you want to convince yourself of this, try EXPLAIN ANALYZE on your
> query with enable_seqscan set to off (discard the first result however,
> because in either case some caching will happen). For example:
>
> regression=# select * from foo;
> f0 | f1 | f2
> ----+------+-------
> 1 | cat1 | 1.21
> 2 | cat1 | 1.24
> 3 | cat1 | 1.18
> 4 | cat1 | 1.26
> 5 | cat1 | 1.15
> 6 | cat2 | 1.15
> 7 | cat2 | 1.26
> 8 | cat2 | 1.32
> 9 | cat2 | 1.3
> 10 | cat3 | 3.333
> (10 rows)
>
> regression=# VACUUM ANALYZE;
> VACUUM
> regression=# create index foo_idx on foo(f0);
> CREATE INDEX
>
> regression=# explain analyze select * from foo where f0 = 1;
> QUERY PLAN
> --------------------------------------------------------------------------
------------------
> Seq Scan on foo (cost=0.00..1.12 rows=2 width=20) (actual
> time=0.03..0.05 rows=1 loops=1)
> Filter: (f0 = 1)
> Total runtime: 0.22 msec
> (3 rows)
> regression=# set enable_seqscan to off;
> SET
> regression=# explain analyze select * from foo where f0 = 1;
> QUERY PLAN
> --------------------------------------------------------------------------
----------------------------------
> Index Scan using foo_idx on foo (cost=0.00..3.01 rows=2 width=20)
> (actual time=0.06..0.07 rows=1 loops=1)
> Index Cond: (f0 = 1)
> Total runtime: 0.20 msec
> (3 rows)
>
> regression=# set enable_seqscan to on;
> SET
> regression=# explain analyze select * from foo where f0 = 1;
> QUERY PLAN
> --------------------------------------------------------------------------
------------------
> Seq Scan on foo (cost=0.00..1.12 rows=2 width=20) (actual
> time=0.03..0.05 rows=1 loops=1)
> Filter: (f0 = 1)
> Total runtime: 0.14 msec
> (3 rows)
>
> HTH,
>
> Joe
>
>