Re: Can the query planner create indexes?

Dne 21.12.2010 20:03, Jeremy Harris napsal(a):
> On 2010-12-21 18:50, Tomas Vondra wrote:
>>> Then the index you just built gets automatically dropped, as I said
>>> above.
>>
>> I'm a bit confused. Should the indexes be dropped automatically (as you
>> state here) or kept for the future. Because if they should be dropped,
>> then it does not make sense to do this magic just for a limited time
>> after the DB goes live.
>
> Here what I said:
> "track those that actually get re-used and remove the rest".
>
> Which part is confusing?

As I described, identifying which indexes are actually used is a very
tricky task. And it's not difficult to come up with scenarios where this
causes significantly more harm than good.

Basically the time to keep the indices needs to be long enough that the
indexes that are actually used are not dropped (and the resources spent
creating them actually pays off). But on the other side it needs to be
short so that resources are not wasted because of unused indices.

Which are clearly contradictory requirements.

And the 'limits' you've proposed make that even worse, because when the
unnecessary indices get created first and take most of the resources
(e.g. disk space), then the indexes that are actually needed won't be
created because of those limits.

regards
Tomas