Why does PostgreSQL perform sequential scan on indexed column?

Why does PostgreSQL perform sequential scan on indexed column?

Asked on January 8, 2019 in PostgreSQL.
Add Comment


  • 3 Answer(s)

    User can SELECT returns more than almost 5-10% of overall rows in the table, a sequential scan is much faster than an index scan.

    So the  index scan requires many IO process 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.

     In this   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”)

    Answered on January 8, 2019.
    Add Comment

    First you Refer the tables in the database? And what about the statistics? When there are many records where year > 2009, a sequential scan might be faster than an index scan.

    Answered on January 8, 2019.
    Add Comment

    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”)

    Answered on March 5, 2019.
    Add Comment


  • Your Answer

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