5

In our postgres db we historically have many similar indexes (e.g. is_deleted boolean) that have quite bad selectivity value. Worse than that, sometimes query planner breaks and postgres starts to use some of them instead of much more efficient indexes so we need to call ANALYZE manually to fix performance, so we decided to delete most of them.

Do we need to call ANALYZE for each table after deleting index?

According to the answer in Is it necessary to ANALYZE a table after an index has been created? postgres collects stats about actual values in table (for simple indexes) without index-related info but I couldn't find proof in docs.

7

No, it's not necessary. Statistics on the main table are not affected by indexes at all.

One thing to observe: expression indexes introduce new "derived" columns, and Postgres gathers separate statistics for those. Removing such indexes also removes the special statistics - which may lead to different query plans, beyond the fact that the index is gone.

Since you mention indexes on boolean columns: those hardly ever make sense and it's a good idea to remove those. boolean column typically only make sense as condition in a partial index. There is an example in the manual.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.