Обсуждение: explain plan

Running Red Hat 7.0 kernel 2.2.16
PostgreSQL 7.0.3

skyy=# create unique index article_pkey on article (id_article);
CREATE
skyy=# explain select id_article from article where id_article = 21;
NOTICE:  QUERY PLAN:

Seq Scan on article  (cost=0.00..1.61 rows=1 width=8)

EXPLAIN
skyy=# vacuum analyze article;
VACUUM
skyy=# explain select id_article from article where id_article = 21;
NOTICE:  QUERY PLAN:

Seq Scan on article  (cost=0.00..1.61 rows=1 width=8)

EXPLAIN
skyy=#

This table has 20,000 records. What am I doing wrong? Why doesn't it use
the Index I created? Is there something I need to enable, why wouldn't
it choose an index over a seq scan with more than 20,000 rows to scan?


Thanks In Advance,

Rudy Laczkovich

rudy <rudy@heymax.com> writes:
> skyy=# explain select id_article from article where id_article = 21;
> NOTICE:  QUERY PLAN:

> Seq Scan on article  (cost=0.00..1.61 rows=1 width=8)

> This table has 20,000 records.

It does?  Given that cost estimate for a sequential scan, I don't
believe the table could have more than one page or more than
fifty records.  What do you get from
    select relpages, reltuples from pg_class where relname = 'article';

> Why doesn't it use the Index I created?

Because it thinks the seqscan is cheaper.

            regards, tom lane

On Tue, 30 Jan 2001, rudy wrote:

> skyy=# vacuum analyze article;
> VACUUM
> skyy=# explain select id_article from article where id_article = 21;
> NOTICE:  QUERY PLAN:
>
> Seq Scan on article  (cost=0.00..1.61 rows=1 width=8)
>
> EXPLAIN
> skyy=#
>
> This table has 20,000 records. What am I doing wrong? Why doesn't it use
> the Index I created? Is there something I need to enable, why wouldn't
> it choose an index over a seq scan with more than 20,000 rows to scan?

I am new to PostgreSQL, but have been doing databases for a while so I am
going to give feedback based on previous experiences with other
optimizers.

Depending on how big each row is the optimizer may decide that the
overhead of going to the index may not be worth it compared to what it
would "cost" just reading the whole file.

You also need to take into account the cardinality of the field in
question. (familiar with the term?)

For example if when you did vacuum analyze the database notices that the
field in question has a high number of different values and it believes
that your request would return a large number of them, then going to the
index may indeed be slower.

How many rows does the query return?

> Seq Scan on article  (cost=0.00..1.61 rows=1 width=8)


:-)
Replying to myself..

Notice the "cost" reported by the database. "0.00..1.61". That is fairly
low. Just to give you an example. I have a table with 259 rows. and I got:
-------
test=# explain select * from phones;
NOTICE:  QUERY PLAN:

Seq Scan on phones  (cost=0.00..20.00 rows=1000 width=36)

EXPLAIN
test=# vacuum analyze
test-# ;
VACUUM
test=# explain select * from phones;
NOTICE:  QUERY PLAN:

Seq Scan on phones  (cost=0.00..5.59 rows=259 width=36)
--------

0 to 5.59 for just 259 rows, yet yours is only 1.61. Possible the entire
table was in memory so there was no need to go to index.