When an index is available for a query, the planner decides whether to use it or not depending on whether it would make the query perform better, right? However if an index, which does not exist, would make the query run better the planner is not able (allowed?) to create such index, use it, and drop it once the query is done. Why is it so?
From my knowledge there is more then one answer:
a) There is a proposal (and, at the time being) also some code on pgfoundry creating "hypothetical indexes"
The idea is: to play with "what-would-be-if-there-would-be-an-index".
With keywords "hypothetical index", "Index advisor" and "virtual index" there is some research googleable
b) creating an index requires to read the data-to-be-indexed. So, to have an index pointing at the interesting rows for your query, the table has to be read ... which would be the perfect time to allready select the interesting rows. And after having the interesting rows: the index is worthless
c) PostgreSQL is in fact doing something quite similiar like "temporary indexes during the query"; only for the selected rows. The words to google for is "hash maps", "bitmap access".
Why is the query planner not allowed to create indexes, but only allowed to use or not use what's available?
as in b): Creating an index is quite expensiv
additionally: having multiple possible plans is also creating a new decision problem: which of the possible plans will lead to the better result; again with some meanings of "better": faster result or less processor usage or less memory usage or less disk accesses. So adding additional indices during planning would worsen this problem; which has to be balanced against possible gains.
So: hypothetical indizes are a good idea, BUT current limitations would most likely force them to be made outside the life query process.
Best wishes,
Harald
persuadere.
