temporary indexes

Just a "wouldn't it be nice if" sort of feature request. I'm not sure
how practical it is.

Someone in our organization wrote a data fix query, which has sort of
odd logic, but it does what they need. The problem is that it ran for
14 hours in a test against a copy of the data. I looked at it and
figured it could do better with an extra index. The index took five
minutes to build, and the run time for the query dropped to five
minutes. The index is not needed for production, so it was then
dropped.

It struck me that it would be outstanding if the planner could
recognize this sort of situation, and build a temporary index based on
the snapshot of the data visible to the transaction. It seems to me
that the obvious downside of this would be the explosion in the number
of permutations the planner would need to examine -- based not just on
what indexes ARE there, but which ones it could build. At a minimum,
there would need to be a cost threshold below which it would not even
consider the option. (In this case, as long as the optimizer spent less
than 13 hours and 50 minutes considering its options, we would have come
out ahead.)

I'm not sure the details of this particular incident are that relevant,
but I've attached the query and the two plans.

-Kevin