Re: Estimates on partial index

2016-08-18 18:59 GMT+03:00 Jeff Janes <jeff(dot)janes(at)gmail(dot)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