Re: cannot get stable function to use index

Andy Colson <andy(at)squeakycode(dot)net> writes:
> Ok, I can reproduce this now. The full vacuum analyze isn't needed.
> If I drop and recreate the table it goes back to preferring table scan.
> I can "analyze search" and it still table scans.
> But once I "vacuum analyze search", then it starts index scanning.

Hah. You didn't say what your table recreation process is, but now I bet
it involves create the table, create the index, *then* fill the table.
I was just running the dump script, which creates the index last.
If I do it the other way then I get insane estimates.

Tracing through that, when gincostestimate looks at the GIN index's
metapage stats, it sees this:

(gdb) p *metadata
$1 = {head = 2, tail = 136, tailFreeSize = 3272, nPendingPages = 135,
nPendingHeapTuples = 33424, nTotalPages = 2, nEntryPages = 1,
nDataPages = 0, nEntries = 0, ginVersion = 2}

ie, the page counts are as of the time of index creation not current.
The insanity must come from trying to scale these up to the current index
size and getting silly results. In particular, it's still gonna end up
with numDataPages equal to zero, which I bet is bad news ...

After VACUUM I see

(gdb) p *metadata
$2 = {head = 4294967295, tail = 4294967295, tailFreeSize = 0,
nPendingPages = 0, nPendingHeapTuples = 0, nTotalPages = 685,
nEntryPages = 410, nDataPages = 16, nEntries = 44125, ginVersion = 2}

and the cost estimate is far saner.

regards, tom lane