Re: gincostestimate and hypothetical indexes

On 01/12/2015 00:37, Tom Lane wrote:
> Julien Rouhaud <julien.rouhaud@dalibo.com> writes:
>> I figured out that it's not possible to use a hypothetical gin index, as
>> the gincostestimate function try to retrieve some statistical data from
>> the index meta page.
> 
> Good point.
> 
>> Attached patch fixes this. I believe this should be back-patched as was
>> a2095f7fb5a57ea1794f25d029756d9a140fd429.
> 
> I don't much care for this patch though.  The core problem is that just
> returning all zeroes seems quite useless: it will probably result in silly
> cost estimates.  The comment in the patch claiming that this would be the
> situation in a never-vacuumed index is wrong, because ginbuild() updates
> those stats too.  But I'm not sure exactly what to do instead :-(.
> 

Oops, it looks that this is only true for pre 9.1 indexes (per comment
shown below).

> Ideally we'd put it on the head of the hypothetical-index plugin to invent
> some numbers, but I dunno if we want to create such an API or not ... and
> we certainly couldn't back-patch such a change.
> 
> Maybe we could do something along the lines of pretending that 90% of the
> index size given by the plugin is entry pages?  Don't know what a good
> ratio would be exactly, but we could probably come up with one with a bit
> of testing.
> 

I used zero values because gincostestimate already handle empty
statistics, and pretend that 100% of the pages are entry pages:


/*
* nPendingPages can be trusted, but the other fields are as of the last
* VACUUM. Scale them by the ratio numPages / nTotalPages to account for
* growth since then. If the fields are zero (implying no VACUUM at all,
* and an index created pre-9.1), assume all pages are entry pages.
*/if (ginStats.nTotalPages == 0 || ginStats.nEntryPages == 0){    numEntryPages = numPages;    numDataPages = 0;
numEntries= numTuples; /* bogus, but no other info available */}
 


But I don't have any clue of what would be a better ratio either.

>             regards, tom lane
> 


-- 
Julien Rouhaud
http://dalibo.com - http://dalibo.org