Re: Why won't the query planner use my index?

On Wed, 2002-03-27 at 23:46, Robert Wille wrote:
> create table a (
> id int default nextval('seq'),
> parent int,
> name varchar(32),
> state bigint default 0,
> scope varchar(80),
> primary key (id)
> );
>
> I then populate it with 1M rows, and then create the following index:
>
> create unique index parentindex on a (parent, name);
>
> and then vacuum analyze.
>
> The output from the following explain statements seem unusual:
>
> ------------------------
> test=# explain select * from a where parent=5;
> NOTICE: QUERY PLAN:
> Seq Scan on a (cost=0.00..23000.20 rows=46108 width=40)

PostgreSQL is guessing that this qualification will return ~46,000 rows,
so that using a sequential scan would be faster than using the index.
You can test if this is correct by using "SET enable_seqscan = off" to
force an index scan, and then timing to see how long that query plan
takes to execute (in 7.2, you could use EXPLAIN ANALYZE). Also, 7.2 is
smarter in this regard.

> ------------------------
> test=# explain select max(id) from a;
> NOTICE: QUERY PLAN:
> Aggregate (cost=23000.20..23000.20 rows=1 width=4)
> -> Seq Scan on a (cost=0.00..20378.76 rows=1048576 width=4)
> ------------------------
>
> This one is quite baffling. All the DB needs to do is look at the end of the primary key index.

Postgres will never use an index for this kind of query. If you need
this kind of functionality, perhaps you can manipulate the sequence
directly, using nextval() or currval().

> ------------------------
> test=# explain select * from a where id < 10000;
> NOTICE: QUERY PLAN:
> Seq Scan on a (cost=0.00..23000.20 rows=9998 width=40)
> ------------------------
>
> This select statement would select only 1% of the rows, yet the planner thinks that a sequential scan is faster.

Use PostgreSQL 7.2, it should be smart enough to figure that out.

Cheers,

Neil

--
Neil Conway <neilconway(at)rogers(dot)com>
PGP Key ID: DB3C29FC