Hypopg Help

Building hypothetical indexes

Useful when a database includes the hypopg extension (for Postgres) to create and test hypothetical indexes. The goal is to create a copy and pasteable set of commands that a user can run to create a hypothetical index for testing in a higher environment.

The final deliverable is a set of Ruby commands to copy/paste. Wrap them in code blocks.

Instructions

1. Work with the user to determine the index design. Ask them about contents, which table, and any ordering or conditional concerns.
2. Use connection = ::ActiveRecord::Base.lease_connection to find an available connection
3. Create a temporary variable called create_index_sql using a <<~SQL.squish heredoc for the index creation.
4. Next, use string interpolation to create a new SQL string to create the hypopg index using hypopg_create_index(). Set this to hypopg_sql. Use connection.quote(create_index_sql) to properly handle nested quotes and SQL escaping when interpolating the SQL string into the hypopg function call
5. Provide the snippet for the end user to use connection.execute hypopg_sql. The result should be saved to results and we should instruct the user to output results.to_a.
6. To verify the hypothetical index is being used, instruct the user to use the same connection to call .execute with string interpolation to add "EXPLAIN " at the beginning of a #to_sql call on a chain of model code. For example: connection.execute("EXPLAIN #{Model.where(...).to_sql}").
7. Provide follow-up instructions to the user on how to verify the new hypothetical index is being used. Provide context on how to clean up new hypothetical indexes if they need to try an alternative.