num_sa_scans in genericcostestimate

From: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: num_sa_scans in genericcostestimate
Date: 2022-08-21 18:45:14
Message-ID: CAMkU=1zaB2JV9O=XxGn7AqUEw_nB7zo-ExTiT40OnkJZ596qbg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

When costing a btree index scan, num_sa_scans gets computed twice, once in
btcostestmeate and once in genericcostestimate. But the computations are
different. It looks like the generic one includes all =ANY in any column
in the index, while the bt one includes only =ANY which or on columns for
which all the preceding index columns are tested for equality.

It looks like the generic one was there first then the bt-specific one was
added later to improve planning of btree indexes. But then shouldn't the
value be passed down to generic, rather than recomputed (differently)?
I've attached a patch to do that. Generic still computes the value itself
for other-than-btree indexes.

Or, is there a reason we want a different value to be used in
genericcostestimate?

The context for this is that I was looking at cases where btree indexes
were not using all the columns they could, but rather shoving some of the
conditions down into a Filter unnecessarily/unhelpfully. This change
doesn't fix that, but it does seem to be moving in the right direction.

This does cause a regression test failure due to an (apparently?)
uninteresting plan change.

Cheers,

Jeff

Attachment Content-Type Size
eq_any.patch application/octet-stream 1.4 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Smith 2022-08-21 23:11:50 Re: Perform streaming logical transactions by background workers and parallel apply
Previous Message Nikita Malakhov 2022-08-21 18:04:50 Re: Proposal: CREATE/ALTER DOMAIN ... STORAGE/COMPRESSION = ...