Re: Accounting for metapages in genericcostestimate()

Henson Choi <assam258@gmail.com> writes:
>> Per the discussion at [1], genericcostestimate() produces estimates
>> that are noticeably off for small indexes, because it fails to
>> discount the index metapage while computing numIndexPages.
>> Here's a first-draft attempt at improving that.

> I reviewed this patch and it looks good to me overall.

Thanks for reviewing!

> 4. The test adjustments (join.sql, memoize.sql, select.sql) all
> make sense as ways to preserve the original test intent despite
> the cost shift.  However, I noticed that all test changes are
> defensive -- they keep existing plans from changing -- but there
> is no positive test case showing that the patch actually produces
> a better plan choice.

> I'm attaching a positive test case based on the motivating
> scenario from pgsql-performance: a tiny partial index vs a full
> index on the same column.  Without the patch the planner picks
> the full index; with the patch, it correctly prefers the partial
> one.  All regression tests pass with both patches applied.

Fair point.  But I thought that it was kind of silly to build
a whole new moderately-large table when the adjacent tests are
exercising perfectly good small partial indexes on the existing
table onek2.  All we need is a non-partial index to compete
against, so transiently making that should be cheaper.  So
I did this:

-- onek2_u2_prtl should be preferred over this index, but we have to
-- discount the metapage to arrive at that answer
begin;
create index onek2_index_full on onek2 (stringu1, unique2);
explain (costs off)
select unique2 from onek2
  where stringu1 < 'B'::name;
rollback;

(The begin/rollback is to ensure that no other tests can see this
index, in case it could mess up their results.)

Pushed with those changes.

			regards, tom lane