current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 16 down vote favorite
5

Very simple example - one table, one index, one query:

CREATE TABLE book
(
  id bigserial NOT NULL,
  "year" integer,
  -- other columns...
);

CREATE INDEX book_year_idx ON book (year)

EXPLAIN
 SELECT *
   FROM book b
  WHERE b.year > 2009

gives me:

Seq Scan on book b  (cost=0.00..25663.80 rows=105425 width=622)
  Filter: (year > 2009)

Why it does NOT perform index scan instead? What am I missing?

share|improve this question
add comment

2 Answers

up vote 32 down vote accepted

If the SELECT returns more than approximately 5-10% of all rows in the table, a sequential scan is much faster than an index scan.

This is because an index scan requires several IO operations for each row (look up the row in the index, then retrieve the row from the heap). Whereas a sequential scan only requires a single IO for each row - or even less because a block (page) on the disk contains more than one row, so more than one row can be fetched with a single IO operation.

Btw: this is true for other DBMS as well - some optimizations as "index only scans" taken aside (but for a SELECT * it's highly unlikely such a DBMS would go for an "index only scan")

share|improve this answer
    
The 5-10% depends on a couple of configuration settings and the storage of the data as well. It's not a hard number. –  Frank Heikens Mar 5 '11 at 13:05
1  
@Frank: that's why I said "approximately" :) But thanks for pointing it out –  a_horse_with_no_name Mar 5 '11 at 13:06
    
Interesting, that explains many things for me :) Indeed, when I select by year > 2010 it does index scan. Thank you! –  wajda Mar 5 '11 at 15:24
1  
Also, a sequential scan can request several pages from the heap at a time, and ask the kernel to be fetching the next chunk while it works on the current one- an index scan fetches one page at once. (A bitmap scan does a compromise between the two, you usually see that appearing in a plan for queries that aren't selective enough for an index scan, but still not so unselective as to merit a full table scan) –  araqnid Mar 5 '11 at 18:44
add comment
up vote 2 down vote

Did you ANALYZE the table/database? And what about the statistics? When there are many records where year > 2009, a sequential scan might be faster than an index scan.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.