Re: EXPLAIN ANALYZE not considering primary and unique indices!

In response to Sanjay <skpatel20(at)gmail(dot)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