Re: Can the query planner create indexes?

Hello Dario,

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"
http://postgresql.1045698.n5.nabble.com/Hypothetical-Indexes-PostgreSQL-extension-PGCON-2010-td3288085.html
http://archives.postgresql.org/pgsql-hackers/2010-12/msg00087.php

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

--
GHUM GmbH
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607

Amtsgericht Stuttgart, HRB 734971
-
persuadere.
et programmare