Both plans touch the same pages. The index scan just touches some of those pages over and over again. A large setting of effective_cache_size would tell it that the page will most likely still be in cache when it comes back to touch it again, meaning the cost of doing so will be small, basically free. > and in a typical situation those are cold.
But they won't be, because it is heating them up itself, and effective_cache_size says that stay then hot for the duration of the query.
(Re-sending as I've missed to add the list.)
But IndexScan means, that not only index, table is also accessed.
And although index is small get's hot quite quickly (yes, e_c_s is 96GB on this dedicated box),
table is not. And this clearly adds up to the total time.
I am wondering, if heap page accesses are also accounted for during planning.
Also, with a random_page_cost of 2.5, you are telling it that even cold pages are not all that cold.
Yes, this was new for me and I will review my setup.
Current setting is based on the fact we're running SSDs.
What are the correlations of the is_current column to the ctid order, and of the loan_id column to the ctid order?
SELECT attname,null_frac,avg_width,n_distinct,correlation FROM pg_stats WHERE tablename='loan_agreements' AND attname IN ('loan_id','is_current','due_date');