Why Index-Only-Scan's COST can be higher than Seq-Scan

Question

sql> create table index_test (col1 integer, col2 integer default 0);
sql> do $$
  begin
  for i in 1..100000 loop
    insert into index_test (col1) values(i);
  end loop;
end;
$$;

sql> create index p_index_test2 on index_test (col1, col2);

sql> explain analyze select col2 from index_test where col1 > 40000;
+-----------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                      |
|-----------------------------------------------------------------------------------------------------------------|
| Seq Scan on index_test  (cost=0.00..1693.00 rows=59718 width=4) (actual time=2.916..426.669 rows=60000 loops=1) |
|   Filter: (col1 > 40000)                                                                                        |
|   Rows Removed by Filter: 40000                                                                                 |
| Planning time: 0.104 ms                                                                                         |
| Execution time: 829.769 ms                                                                                      |
+-----------------------------------------------------------------------------------------------------------------+


sql> SET enable_seqscan = OFF;
sql> explain analyze select col2 from index_test where col1 > 40000;
+--------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                                 |
|--------------------------------------------------------------------------------------------------------------------------------------------|
| Index Only Scan using p_index_test2 on index_test  (cost=0.29..1705.36 rows=59718 width=4) (actual time=0.032..427.644 rows=60000 loops=1) |
|   Index Cond: (col1 > 40000)                                                                                                               |
|   Heap Fetches: 0                                                                                                                          |
| Planning time: 0.076 ms                                                                                                                    |
| Execution time: 839.303 ms                                                                                                                 |
+--------------------------------------------------------------------------------------------------------------------------------------------+

The above is comparing two query plan. one is using Index-Only-Scan the other is using Seq-Scan.

At the fist, I expected that query planner uses Index-Only-Scan for sure. However, the two plan show similar cost and actual time figure and it even shows Index-Only-Scan is more expensive.

Why?

I know that in case of Index-Scan, it needs additional action fetching data from data table so that the Index-Scan's cost could be more expensive than Seq-Scan but this is not in the case of Index-Only-Scan.

One thing my assumpting is because the cardinality of result is hight(40001~100000). I think high cardinality could make planner do many random-access and its cost is higher than sequential-access.

However, I think planner will use sequential-access(not random-access) because WHERE clause is range-condition and index is always ordered.

---

Edit

Postgresql 10.5

As I understand, If it simplifies, the two plan will work like the below.

---

with Index-Only-Scan

1. move to 40001
2. read data sequentially up to 100000

cast = (jump to 40001 in index table) + (read 60000 sequentially in index table)

---

with Seq-Scan

1. read data sequentially up to 100000

cast = (read 100000 sequentially in data table)

---

If the two plan have similar coat, it means (jump to 40001 in index table)'s cost is expensive as (read 40000 sequentially in data table)

Is my understanding wrong?

Answer 1

The difference in timing you show is below noise level and does not tell us more than that both query plans are about as fast - which is as expected.

You do seem to test on a terribly slow system, though. This simple query shouldn't take close to a second. Even on my age-old laptop, this takes below 20 ms. Either you are on ancient / minimal hardware or your server configuration might need some attention. Very old Postgres version, too, maybe?

You added Postgres 10.5, which was released 2018-08-09. The current minor release is 10.10. The Postgres project recommends:

We always recommend that all users run the latest available minor release for whatever major version is in use.

While selecting ~ 60 % of the table, with a row width of only 8 user-bytes (36 bytes total), not even an index-only scan on a pristine index without bloat (based on the same row size!) has much to offer over a sequential scan. There is just not much left to make this faster than reading sequentially and filtering 40%.

B-tree indexes have a smaller row header than the heap (8 vs. 24 bytes), but a fillfactor of 90 % by default. And indexes tend to bloat more and quicker than tables. Plus, the various index access methods all add some overhead. For an index-only scan Postgres needs to first read and evaluate the visibility map.

Test with much smaller percentages, wider table rows, higher cardinalities, including toasted columns, etc. Throw in some table and/or index bloat, and you'll get more interesting results.