Thread: Costs of Heap Fetches in Postgres 13

Hi,

does the planner do estimates about heap fetches on index only scans and takes them into account?
At least in Pg 13? If so, is it  possible to lower those costs? random_page_costs seems not to have any influence.
Looks like they cause bad planner decisions.
Tuning Vacuum is not an option ;)

regards, Jens

On Fri, 31 Jul 2020 at 05:21, <Jens.Wilke@parcit.de> wrote:
> does the planner do estimates about heap fetches on index only scans and takes them into account?
> At least in Pg 13? If so, is it  possible to lower those costs? random_page_costs seems not to have any influence.
> Looks like they cause bad planner decisions.
> Tuning Vacuum is not an option ;)

You'll probably need to give us an example of this not working by
means of an EXPLAIN output.

The planner uses the ratio of blocks marked as all visible from
pg_class.relallvisible and the current number of blocks in the
relation and applies random_page_cost to the expected heap blocks it
would read from an Index Scan offset by 1.0 - that ratio.

e.g. If the table has 1000 blocks and you have 900 marked as all
visible, and an index scan expects to read 200 blocks, then it will
apply random_page_cost * 200 * (1.0 - (900.0 / 1000.0)).  Which in
this case is 20 blocks. Your all visible ratio here is 90%, 10% are
not all visible, so 10% of 200 blocks is 20 blocks.

If I mock up a case like that and then tweak random_page_cost, then I
see the total cost changing just fine.  I did only test in master, but
we'll not have changed that since branching for PG13.

Perhaps you've got some tablespace level random_page_cost set and
you're not actually changing it?

David