I would like to introduce a PostgreSQL extension called Pg_dbscanner.
Pg_dbscanner analyzes table schemas and existing indexes and provides recommendations for suitable index types based on column data types and other available metadata.
1. What Problem It Solves
Choosing the right index type in PostgreSQL can require knowledge of different index access methods and an understanding of the data stored in the columns.
For example, a table may contain:
Text columns
Integer columns
Date or timestamp columns
Large tables with naturally ordered data
Users may not always know which index type would be most suitable or whether an existing index matches the expected use case.
Pg_dbscanner aims to provide these recommendations directly from PostgreSQL.
2. What This Extension Does
Inspects the schema of a user-defined table
Examines column data types
Detects existing indexes
Recommends potentially suitable index types
Compares existing indexes with the recommendations
Reports whether the current index matches the suggestion
Provides the results at query time without modifying the schema
[snip]
7. Feedback Requested
I would appreciate feedback from the PostgreSQL community on:
The overall extension design
The index recommendation approach
The heuristic rules used for different data types
Performance considerations
Additional index types or use cases that should be supported
Possible improvements for making the extension more useful and production-ready
I’m sharing Pg_dbscanner as a starting point for discussion and would welcome suggestions and feedback from the community.
Does it scan log_destination for slow (as defined by log_min_duration_statement) queries, or maybe keep a history of slow queries?
Because that is what determines what indices are needed.
