When working with SQL databases, understanding how different queries are executed is essential for optimizing performance. The `EXPLAIN` command is a powerful tool that reveals the query plan chosen by the database engine. By comparing the plans for similar queries, you can identify which approach is more efficient and why. In this challenge, you will analyze and compare the `EXPLAIN` output for two queries: one that selects all rows from the `customers` table, and another that filters results using a `WHERE` clause on an indexed column.

import unittest
import psycopg2
import pandas as pd
import re

class TestPostgresQuery(unittest.TestCase):
    @classmethod
    def setUpClass(cls):
        cls.connection = psycopg2.connect(
            host="localhost",
            user="agent",
            password="qwerty",
            dbname="testdb",
            sslmode="disable"
        )
        cls.cursor = cls.connection.cursor()

    def test_explain_unfiltered_query_uses_seq_scan(self):
        """Check that unfiltered query uses sequential scan."""
        # Check that user provided EXPLAIN for unfiltered query
        with open("query.sql", "r") as f:
            sql = f.read().lower()
        has_explain_all = re.search(r"explain\s+select\s+\*\s+from\s+customers\s*;", sql)
        _dynamic_test(
            self,
            bool(has_explain_all),
            "User provided EXPLAIN for unfiltered query.",
            "User did not provide EXPLAIN for unfiltered query."
        )
        # Execute and check plan
        self.cursor.execute("EXPLAIN SELECT * FROM customers;")
        plan = " ".join([row[0].lower() for row in self.cursor.fetchall()])
        _dynamic_test(
            self,
            "seq scan" in plan,
            "Unfiltered query uses sequential scan.",
            "Unfiltered query does not use sequential scan."
        )

    def test_explain_filtered_query_uses_index_scan(self):
        """Check that filtered query uses index scan on customer_id and user provided correct EXPLAIN."""
        with open("query.sql", "r") as f:
            sql = f.read().lower()
        has_explain_filtered = re.search(r"explain\s+select\s+\*\s+from\s+customers\s+where\s+customer_id\s*=\s*2\s*;", sql)
        _dynamic_test(
            self,
            bool(has_explain_filtered),
            "User provided EXPLAIN for filtered query.",
            "User did not provide EXPLAIN for filtered query."
        )
        self.cursor.execute("EXPLAIN SELECT * FROM customers WHERE customer_id = 2;")
        plan = " ".join([row[0].lower() for row in self.cursor.fetchall()])
        condition = ("index scan" in plan or "index only scan" in plan) and "customers_pkey" in plan
        _dynamic_test(
            self,
            condition,
            "Filtered query uses index scan on customers_pkey.",
            "Filtered query does not use index scan on customers_pkey."
        )

    def test_filtered_query_scans_one_row(self):
        """Check that filtered query plans to scan only one row (the matching customer_id)."""
        self.cursor.execute("EXPLAIN SELECT * FROM customers WHERE customer_id = 2;")
        plan2 = " ".join([row[0] for row in self.cursor.fetchall()])
        m2 = re.search(r"rows=(\d+)", plan2)
        if m2:
            rows2 = int(m2.group(1))
            condition = rows2 == 1
        else:
            condition = False
        _dynamic_test(
            self,
            condition,
            "Filtered query plans to scan exactly one row.",
            "Filtered query does not plan to scan exactly one row."
        )

    def test_explanation_present_and_correct(self):
        """Check that user provides a meaningful, accurate explanation comparing both EXPLAIN plans and referencing index and rows scanned."""
        with open("query.sql", "r") as f:
            sql = f.read().lower()
        # Extract all SQL comments
        explanation = "\\n".join(re.findall(r"--\\s*(.*)", sql))
        explanation_norm = normalize_text(explanation)
        # Must mention sequential scan for unfiltered
        has_seq_scan = "seq scan" in explanation_norm or "sequential scan" in explanation_norm
        # Must mention index scan for filtered
        has_index_scan = "index scan" in explanation_norm
        # Must mention index or customers_pkey
        mentions_index = "index" in explanation_norm or "customers_pkey" in explanation_norm
        # Must mention fewer rows or only matching row
        mentions_row_count = ("one row" in explanation_norm or "1 row" in explanation_norm or "fewer row" in explanation_norm or "matching row" in explanation_norm or "minimizing the number of rows" in explanation_norm)
        # Must mention efficiency or performance
        mentions_efficiency = ("efficient" in explanation_norm or "performance" in explanation_norm or "faster" in explanation_norm)
        condition = has_seq_scan and has_index_scan and mentions_index and mentions_row_count and mentions_efficiency
        _dynamic_test(
            self,
            condition,
            "Explanation compares plans, references index, rows scanned, and efficiency.",
            "Explanation is missing required comparison or details about index, rows scanned, or efficiency."
        )

def _dynamic_test(test_case, condition, success_message, failure_message):
    if condition:
        test_case._testMethodName = success_message
        test_case.assertTrue(True, success_message)
    else:
        test_case._testMethodName = failure_message
        test_case.fail(failure_message)

def normalize_text(text):
    text = text.lower()
    text = re.sub(r"\s{2,}", " ", text)
    text = re.sub(r"\s*([,:?])\s*", r"\1 ", text)
    return text.strip()

if __name__ == "__main__":
    unittest.main()


test_solution.py

Dive deep into the world of SQL query optimization by mastering the EXPLAIN statement and understanding how query planners work. This course blends engaging theory with hands-on, real-world tasks to help you analyze, interpret, and optimize SQL queries using EXPLAIN and related tools.

Learn the fundamentals of the EXPLAIN statement, how query planners work, and how to interpret basic query plans.

Delve into more complex query plans, including multi-table joins, subqueries, and the impact of indexes.

Apply your knowledge to complex, real-world scenarios involving large datasets, reporting queries, and performance troubleshooting.

Challenge: Comparing Query Plans

Soluzione

Challenge: Comparing Query Plans

Soluzione