Joe's new command
hypo to further boost development processes#
Building indexes for large tables may take a long time. The new release of Joe bot includes the ability to get a sneak peek of the SQL query plan, using hypothetical indexes, before proceeding to actually building large indexes.
A hypothetical index is an index that doesn't exist on disk. Therefore it doesn't cost IO, CPU, or any resource to create. It means that such indexes are created almost instantly.
With the brand new command,
hypo, you can create hypothetical indexes with Joe and ensure that PostgreSQL would use them. Once it's done, you can use
exec to build the actual indexes (in some cases, you'll need to wait some hours for this) and see the actual plan in action.
Note, since the command works on top of the HypoPG extension, your Database Lab image has to use a Docker image for Postgres that contains HypoPG, because this extension is not a part of the core PostgreSQL distribution. For convenience, we have prepared images with HypoPG (and some other extensions) included, for Postgres versions 9.6, 10, 11, and 12. Of course, you can always use your custom image.
To be able to see the plan without actual execution, we have added one more new command:
plan. It is aware of hypothetical indexes, so if one is detected in the plan, it presents two versions of the plan, with and without HypoPG involved.
Version 0.6.0 adds new commands to work with hypothetical indexes and get a query plan without execution, grand improvements in message processing, more. The full list of changes can be found in Changelog. Stay tuned!
First, we need a running Database Lab instance that uses a Docker image with HypoPG extension. Choose a custom Docker image in Database Lab Engine configuration, specifying
config.yml of your Database Lab instance:
Let's see how to use hypothetical indexes with Joe. Generate a synthetic database using standard PostgreSQL tool called pgbench:
Check the size of tables
Then, get a query plan that should benefit an index that’s not here:
The result is:
This query takes an enormously long time. The recommendations suggest adding an index. Before building a real index, let's verify our index idea with instant creation of the corresponding hypothetical index, simply using
hypo create index on pgbench_accounts (bid):
Check that index has been created
Get more details about the index such as estimated size and index definition
hypo desc 24588:
With the consideration that it may be annoying and not really useful to wait seconds (or even minutes) for actual execution when we deal with hypothetical index checks - so let's use the
plan select * from pgbench_accounts where bid = 1; and save even more time:
Joe's response will be:
Perfect! The index works! It means we can reset the hypothetical index
hypo reset and create the real one
exec create index pgbench_accounts_bid on pgbench_accounts (bid);:
It's obvious that
plan extremely save developers' time!
See the full list of Joe's commands in the docs: https://postgres.ai/docs/joe-bot/commands-reference.
- Open-source repository and issue tracker: https://gitlab.com/postgres-ai/joe/
- Full command list: https://postgres.ai/docs/joe-bot/usage
- Extended images with PostgreSQL: https://hub.docker.com/r/postgresai/extended-postgres Includes HypoPG, pg_hint_plan, more
- Proposals to add more extensions are welcome in the Custom Images repo: https://gitlab.com/postgres-ai/custom-images
- Community Slack (English): https://database-lab-team-slack-invite.herokuapp.com/. After joining, the live demo is available in the #joe-bot-demo channel: https://database-lab-team.slack.com/archives/CTL5BB30R