The online_advisor extension

The online_advisor extension recommends indexes, extended statistics, and prepared statements based on your actual query workload. It uses the same executor hook mechanism as auto_explain to collect and analyze execution data.

What it does

• Suggests indexes when queries filter many rows
• Suggests extended statistics when the planner's row estimates are far off from actuals
• Identifies queries that could benefit from prepared statements when planning time is high

Requirements

• Supported on Postgres 17
• Create the extension in every database you want to inspect
• Activate it by calling any provided function (for example, get_executor_stats())

Version availability

Please refer to the Supported Postgres extensions page for the latest supported version of online_advisor on Neon.

Enable the online_advisor extension

You can create the extension in each target database using CREATE EXTENSION:

CREATE EXTENSION online_advisor;

For information about using the Neon SQL Editor, see Query with Neon's SQL Editor. For information about using the psql client with Neon, see Connect with psql.

Start collecting recommendations

1. Activate the extension by calling any function:

   SELECT get_executor_stats();

2. Run your workload to collect data.

3. View recommendations:

   -- Proposed indexes
   SELECT * FROM proposed_indexes ORDER BY elapsed_sec DESC;
   
   -- Proposed extended statistics
   SELECT * FROM proposed_statistics ORDER BY elapsed_sec DESC;

Apply accepted recommendations

Run the create_index or create_statistics statement from the views, then analyze the table:

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_date ON orders(customer_id, order_date);
VACUUM (ANALYZE) orders;

Configure thresholds

You can tune online_advisor with these settings:

Change a setting for the current session:

SET online_advisor.filtered_threshold = 2000;

Check planning and execution stats

Use get_executor_stats() to see planning and execution times and whether prepared statements might help:

SELECT * FROM get_executor_stats(false); -- false = do not reset counters

Look at avg_planning_overhead. Values greater than 1 suggest that some queries would benefit from prepared statements.

Combine or separate index proposals

By default, online_advisor tries to combine related predicates into a single compound index. To view separate recommendations for each predicate:

SELECT * FROM propose_indexes(false);

Limitations

• Does not check operator ordering for compound indexes
• Does not suggest indexes for joins or ORDER BY clauses
• Does not estimate the benefit of adding an index — pair with HypoPG if you want to simulate usage
• Recommendations are per database

Remove the extension

DROP EXTENSION IF EXISTS online_advisor;

If you're not using it anywhere, remove it from shared_preload_libraries and restart.

Example workflow

-- Activate and run workload
SELECT get_executor_stats();

-- View index proposals
SELECT create_index, n_filtered, n_called, elapsed_sec
FROM proposed_indexes
ORDER BY elapsed_sec DESC
LIMIT 10;

-- View extended statistics proposals
SELECT create_statistics, misestimation, n_called, elapsed_sec
FROM proposed_statistics
ORDER BY misestimation DESC
LIMIT 10;

-- Apply a recommendation
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_date ON orders(customer_id, order_date);
VACUUM (ANALYZE) orders;

-- Check planning/execution times
SELECT * FROM get_executor_stats(true); -- reset after reading

Resources

• online_advisor GitHub repository
• PostgreSQL auto_explain documentation