Обсуждение: EXPLAIN ANALYZE not considering primary and unique indices!

Hi All,

Say I have a simple table WEBSITE(website_id int4 PRIMARY KEY, name
VARCHAR(30)). While I try this:

EXPLAIN ANALYZE SELECT * FROM WEBSITE WHERE website_id = 1

the output is:
--------------------------------------------------------------------------------------
Seq Scan on website  (cost=0.00..1.31 rows=1 width=162) (actual
time=0.047..0.051 rows=1 loops=1)
  Filter: (website_id = 1)
Total runtime: 0.102 ms
---------------------------------------------------------------------------------------
Wondering why it is not using the index, which would have been
automatically created for the primary key.

Needing help.

thanks
Sanjay

In response to Sanjay <skpatel20@gmail.com>:

> Hi All,
>
> Say I have a simple table WEBSITE(website_id int4 PRIMARY KEY, name
> VARCHAR(30)). While I try this:
>
> EXPLAIN ANALYZE SELECT * FROM WEBSITE WHERE website_id = 1
>
> the output is:
> --------------------------------------------------------------------------------------
> Seq Scan on website  (cost=0.00..1.31 rows=1 width=162) (actual
> time=0.047..0.051 rows=1 loops=1)
>   Filter: (website_id = 1)
> Total runtime: 0.102 ms
> ---------------------------------------------------------------------------------------
> Wondering why it is not using the index, which would have been
> automatically created for the primary key.

Because PG thinks the seq scan is faster than an index scan.

Depending on other factors, it may be right.  If there's only a few
rows in the table, then a seq scan is going to be faster than
scanning the index, only to grab most of the table in to memory
anyway.

If it's wrong, it's either because your analyze data isn't up to date,
or your tuning parameters don't match your hardware.

--
Bill Moran
http://www.potentialtech.com

On Monday 27 August 2007 05:21, Sanjay <skpatel20@gmail.com> wrote:
>Wondering why it is not using the index, which would have
> been
> automatically created for the primary key.

Because you not only have just one row in the whole table, 100% of them will
match the query. In short, one page fetch for a seq scan is faster than
first looking it up in an index and then fetching the same page.

set enable_seqscan=false;


--
"Government big enough to supply everything you need is big enough to take
everything you have ... the course of history shows that as a government
grows, liberty decreases." -- Thomas Jefferson

Bill Moran <wmoran@potentialtech.com> writes:
> In response to Sanjay <skpatel20@gmail.com>:
>> Seq Scan on website  (cost=0.00..1.31 rows=1 width=162) (actual time=0.047..0.051 rows=1 loops=1)
>> Filter: (website_id = 1)
>> Total runtime: 0.102 ms

>> Wondering why it is not using the index, which would have been
>> automatically created for the primary key.

> Because PG thinks the seq scan is faster than an index scan.

The cost estimate shows there is only one page in the table (assuming
seq_page_cost has its default value of 1.0).  You're basically never
going to get an indexscan plan for a one-page table: it takes one read
to fetch the page, and any reads done to fetch index pages are going
to be more expensive than just examining the tuples, unless you have a
*whole* lot of tuples in the one page.

Load the table up with a realistic amount of data, and ANALYZE it, and
then see what plan you get.

            regards, tom lane

Thanks a lot everybody! I got it clear. I was wrongly thinking that
PostgreSQL might not be creating the indices by default.

regards
Sanjay