Re: Why the planner does not use index for a large amount of data?

On Wed, Dec 6, 2017 at 7:37 AM, hmidi slim <hmidi(dot)slim2(at)gmail(dot)com> wrote:

> Hi,
> When I used explain I found that the query planner use a seq scan to
> execute a query on a table containing about 2 millions rows.However I'm
> creating an index.Why does the planner uses seq scan in place of index
> scan?Does the execution of index scan is slower with table containing a
> huge amount of data?
>

​Please avoid posting to multiple lists at once.

An index doesn't contain visibility information so every record located in
the index must also be checked on the storage table to determine if it is
visible to the current transaction and thus is valid to be returned. If it
does need to be returned the table itself is also needed to get the rest of
the information. Thus the index scan itself involves additional
non-value-added (NVA) effort so far as the query is concerned. If a large
fraction (I've seen estimates of 10%) of the table is estimated to be
returned the additional cost involved with scanning the entire table will
less than the NVA cost of walking through the index and then fetching
records from the table anyway.

Also: "Fetching rows separately is much more expensive than reading them
sequentially."

https://www.postgresql.org/docs/10/static/using-explain.html#USING-EXPLAIN-BASICS

David J.