One approach I've had a lot of success with: always ask for multiple options.
In this case I might try a prompt along the lines of:
"Here is the schema for a table: (schema here). Here is a query that runs against it: (query here). Provide several suggestions for potential indexes that might speed up the query, and for each of those suggestions provide several hypothetical reasons that the index might be a bad idea."
In this case I might try a prompt along the lines of:
"Here is the schema for a table: (schema here). Here is a query that runs against it: (query here). Provide several suggestions for potential indexes that might speed up the query, and for each of those suggestions provide several hypothetical reasons that the index might be a bad idea."
I tried a version of that here, the results look pretty good to me: https://chat.openai.com/share/dc5d7007-cf05-4e5c-89c4-fc669b...