Show HN: PostgreSQL index advisor (github.com)

Article summary

The PostgreSQL Index Advisor is a PostgreSQL extension that recommends indexes to improve query performance. It supports generic parameters, materialized views, and identifies tables/columns obfuscated by views. The extension provides an API to search for SQL DDL create index statements that improve a query's execution time. It can be installed and run on PostgreSQL with hypopg installed.

Main themes

• PostgreSQL Index Advisor
• Query Performance Optimization
• Database Indexing
• PostgreSQL Extensions
• Query Planning

What commenters say

• Some users struggle with the PostgreSQL query planner and wish for more control over the physical query plan or a stable API to manually specify it.
• Disabling parallel query behavior or using plan hints can be a temporary fix, but may not be a long-term solution and can create new problems.
• Understanding why the optimizer is making certain choices is crucial, as simply pinning a plan or disabling parallel operations may not be optimal in the long run.
• Automatically detecting slow queries and creating necessary indexes is a desirable feature, but it comes with trade-offs such as extra disk space, slower inserts, and updates.
• Some databases, like Azure SQL Database, have automatic index tuning features, but these may not be available in all database systems, such as on-prem SQL Server.
• Exposing possible indexes to the user and letting them choose, rather than automating the creation of indexes, is a more prudent approach due to the potential trade-offs involved.