AI for Reading SQL EXPLAIN Plans

End-of-lesson check

15 questions · take it digitally for instant feedback at tendril.neural-forge.io/learn/quiz/end-ai-coding-AI-sql-explain-plan-review-creators

1. When you run EXPLAIN ANALYZE on a query in PostgreSQL, what additional information do you get compared to just running EXPLAIN?

   1. The actual execution time and row counts returned by each plan node
   2. The exact server hardware specifications being used
   3. The SQL syntax errors if any exist in the query
   4. A list of all indexes that exist on every table in the database

2. Why should you walk through an EXPLAIN ANALYZE plan from bottom to top (reverse order) when analyzing it with an LLM?

   1. Top nodes always take the longest time regardless of what happens below
   2. The database optimizer requires this order to validate the plan
   3. Child nodes feed data into parent nodes, so understanding what each child produces helps explain the parent's behavior
   4. PostgreSQL stores plans in reverse order for historical reasons

3. What does it typically mean when the 'rows' estimate in a query plan differs significantly from the actual rows processed?

   1. The SQL syntax is invalid and needs correction
   2. The query is definitely returning incorrect results
   3. The database has detected hardware failure
   4. The query planner's statistics may be outdated, causing it to choose a suboptimal plan

4. What is a major limitation of using an LLM to suggest indexes for your production database?

   1. The LLM will delete existing indexes that conflict with its suggestions
   2. The LLM doesn't know your application's read/write ratio, so it may suggest indexes that severely slow down writes
   3. LLMs always suggest incorrect SQL syntax for index creation
   4. The LLM cannot connect to your database to run EXPLAIN ANALYZE

5. After running VACUUM ANALYZE on a PostgreSQL database, what might change about future query plans?

   1. Query plans will always become faster after VACUUM
   2. The planner's row estimates may become more accurate because fresh statistics were collected
   3. The database will switch to a different storage engine
   4. All existing indexes will be automatically rebuilt

6. Why is it dangerous to apply an LLM's index suggestions directly to a production database without testing?

   1. The index might significantly slow down INSERT, UPDATE, and DELETE operations while only marginally improving reads
   2. The LLM might suggest indexes that corrupt data
   3. The suggestion might use syntax that causes syntax errors
   4. PostgreSQL prevents index changes without downtime

7. What specific information should you provide to an LLM along with an EXPLAIN ANALYZE output to get useful index suggestions?

   1. A list of all queries ever run against the database
   2. The database server's IP address and password
   3. The table DDL (CREATE TABLE statements) showing column types and existing indexes
   4. The complete database backup file

8. What does the 'actual time' value in each node of an EXPLAIN ANALYZE output represent?

   1. The network latency between the client and database server
   2. The time in milliseconds that node spent executing, accumulated across all iterations
   3. The estimated time the planner predicts the node will take
   4. The time PostgreSQL took to compile the query plan

9. What is a 'hot node' in the context of query plan analysis?

   1. A node that is currently being accessed by multiple database connections
   2. A node that performs encryption on sensitive data
   3. A plan node that consumes a disproportionate amount of execution time or processes far more rows than expected
   4. A node that has been cached in PostgreSQL's shared buffer

10. When an LLM suggests an index, what 'tradeoff' should you evaluate before creating it?

    1. The index will use additional disk space and memory
    2. All of the above
    3. The index might become outdated as data grows
    4. The index will speed up queries but add overhead to every data-modifying operation

11. What does an LLM NOT know about your database that could affect whether its index suggestions are appropriate?

    1. The username of the database administrator
    2. The exact version of PostgreSQL being used
    3. The total size of all tables combined
    4. Your application's typical read-to-write ratio and query patterns

12. What should you do before applying any index suggestion from an LLM to production?

    1. Ask the LLM to confirm the suggestion three times
    2. Apply it during peak traffic to see real impact
    3. Test the index on a production-like clone and measure the actual performance difference
    4. Wait exactly 24 hours before applying

13. What does the 'buffers' information in EXPLAIN ANALYZE output tell you?

    1. The exact memory allocation for each query operation
    2. How many pages were read from memory (cache) vs disk for each node
    3. The network buffer sizes configured for PostgreSQL
    4. A list of all database connection buffers currently in use

14. If EXPLAIN shows a 'Nested Loop' join with an estimated 100 rows but actual 50,000 rows, what is likely happening?

    1. The query returned an error and only partial results
    2. The database ran out of memory and switched to a different algorithm
    3. The join is performing far more work than the planner expected, likely due to outdated statistics
    4. The index on the inner table is being used incorrectly

15. What is one reason an LLM might incorrectly estimate the effectiveness of an index suggestion?

    1. The LLM always uses the most recent PostgreSQL documentation
    2. The LLM performs test queries to verify suggestions
    3. The LLM cannot know about other concurrent queries and overall system load
    4. The LLM has access to your actual production data