2016-08-18 18:59 GMT+03:00 Jeff Janes <jeff.ja...@gmail.com>: > 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'); attname null_frac avg_width n_distinct correlation ---------- --------- --------- ---------- ----------- due_date 0 4 1197 0.982312 is_current 0 1 2 0.547268 loan_id 0 8 -0.202438 0.937507 -- Victor Y. Yegorov