Index are suggested while an index already exists

[frost242]

Hello,

Some tests shows that the optimize wizard suggest index that already exist. Also, if you create another index that is suggested, but didn't exist previously, the UI continues to suggest it.

Also, on a side note, when you fall in this case, the gain is estimated to nearly 0%, which means that hypopg is quite good at estimate index scan costs.

Thanks

[rjuju]

Hello @frost242 ,

Sorry for late answer.

For the indexes created after activity, I think it's better to keep current behavior. The index should only be suggested if you analyze activity before they were created, so this problem will disappear after the powa retention. And it can also be useful to compare current indexes with the proposed one.

For the first problem (suggesting indexes that always existed), the only explanation is that they should theoretically be used be weren't. I wonder if the cause was bad statistics, too conservative postgres configuration or something else. Can reproduce this issue or do you have access to the original data?