"Stephen Denne" <Stephen.Denne@datamail.co.nz> writes:
> The improvement wasn't to the part of the query that had the bad cost estimate, it was to the part that was being
performedhundreds of times instead of the one time the planner estimated.
> The planner still thought it was only going to perform a sequential scan of your wooper table once. So even if there
hadbeen any Ad Hoc Index creation code that had been used to consider creating indexes as part of a plan cost estimate,
itwouldn't have bothered creating any indexes on wooper.
Right. And even more to the point, if it had gotten the estimate right
and known that the subquery would have been repeated, that would have
(ideally) prompted it to shift to a different plan structure.
As Peter pointed out upthread, the existing hash join logic seems to be
a pretty decent facsimile of an "ad hoc index" --- in fact, the hash
table *is* a hash index for all intents and purposes. If you tilt your
head at the right angle, a merge join could be seen as comparable to
constructing a couple of ad-hoc btree indexes. Plus the join code is
not burdened by any overhead that actual index code would be likely to
have, such as niggling worries about crash-safety or concurrent access
to the index.
So in my mind the issue here is not why don't we have ad hoc indexes,
it's why the planner didn't choose a more appropriate join method.
It's clear that faulty rowcount estimation was one part of the answer.
Now it may also be that there are some outright structural limitations
involved due to the "join" arising from a sub-select --- I'm not sure
that the planner *can* generate a bulk join plan from a query expressed
like this one. But the TODO items that this suggests to me do not
include implementing anything I'd call an ad-hoc index.
regards, tom lane
