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
