Re: Extension - Pg_dbscanner: Intelligent Index Recommendation for PostgreSQL

On Mon, Aug 17, 2026 at 5:59 AM lakshmi <lakshmigcdac(at)gmail(dot)com> wrote:

> Hello PostgreSQL Community,
>
> 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.

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!