How to create an index using Joe bot
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.
Joe bot uses a restricted database user that is not allowed to create extensions. Therefore, to use hypothetical indexes, you must configure Database Lab Engine to install the HypoPG extension at snapshot preparation time. To do so:
- Create a new SQL file with the query:
create extension if not exists hypopg;
- In the
queryPreprocessingsection, specify the option
queryPathto create HypoPG extension
Fore more details, see Database Lab Engine configuration.