With Joe, it is possible not only to gather query performance metrics but also to change the database schema, create indexes, etc. It can be done with the
exec command. All changes are done against fully-independent clones and will not affect other Joe users, feel free to experiment with the database.
You can use any PostgreSQL command with Joe's
exec command, but the response will not be shown. Still, the changes will be applied to your copy of the database.
execcommand with your query, e.g.
exec create index on pgbench_accounts (bid).
- After a moment session to execute your query and experiment with the database will be created, if it didn't exist before.
- When the query is successfully executed you will see the ✅ OK status and the time it took to complete. The session is still present. You can check new query plans or make other changes now. Also, you can reset the state of the session with the
resetcommand, see the How to reset the state of a Joe session guide.
In the case of big tables when index creation may take many hours you can experiment with HypoPG hypothetical indexes. They're useful to know if specific indexes can increase performance for problematic queries since you can know if PostgreSQL will use these indexes or not without having to spend resources to create them.
- Use the
execcommand with special HypoPG query, e.g.
SELECT * FROM hypopg_create_index('create index on pgbench_accounts (bid)').
- Use the
plancommand instead of the
explaincommand to get the Plan without execution, as hypothetical indexes can be taken into account only there.