1
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?

  • Performance questions need the Postgres version in use. And typically more ... – Erwin Brandstetter Sep 7 at 14:09
  • The default index fill factor is 90%, while the table fill factor is 100%, so in theory the optimizer might expect to read up to 10% more pages when scanning the index in your scenario. – mustaccio Sep 9 at 15:33
  • Your understanding is basically right. But you seem to under-estimate the overhead added by index access methods. – Erwin Brandstetter Sep 10 at 0:44
  • @ErwinBrandstetter not sure if you're addressing me or the OP. I'm just pointing out the elephant in the room; there might also be mice and other rodents in that room, contributing their 0.36. Do you know for a fact that the optimizer (we're looking at estimates here) considers "the overhead added by index access methods"? – mustaccio Sep 10 at 0:59
  • @mustaccio: Comments without @ address the OP. We can see from the cost estimates in the query plans that some overhead is accounted for. – Erwin Brandstetter Sep 10 at 2:59
3

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 fillfactor of 90 % by default. In your simple case, the (unbloated) index takes up more space on disk than the (unbloated) table. 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.

  • Thank you for your answer but I sill don't understand, could you check my edited question? – SangminKim Sep 9 at 15:16
  • The reason that index-only-scan need to check visibility map is because the index-table could still contain deleted data? – SangminKim Sep 10 at 15:38
  • 1
    @asleea: Yes, deleted, yet uncommitted or updated rows. Indexes to not contain visibility information, so all index access methods have to recheck by going to the heap (main relation). Index-only scans are the exception, but only allowed for data pages that are all-visible. The manual has more, follow the link I provided. – Erwin Brandstetter Sep 10 at 16:41
  • Thank you !! Could you answer one more question? this link is saying whether all rows stored in that page are old enough to be visible to all current and future transactions.. does old enough mean that it is enough time to run vacuum already? – SangminKim Sep 11 at 8:52
  • 1
    "Old enough" is a metaphor for "transaction ID < smallest transaction ID of any open transaction". ’VACUUM’ is required to actually update the vm (among other things). – Erwin Brandstetter Sep 11 at 11:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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