In this chapter, you will apply your knowledge of `EXPLAIN` to a real-world scenario involving a library event. Imagine the library wants to invite both authors and borrowers who have interacted through book loans to a special event. You need to analyze and optimize a query that joins the `authors` and `borrowers` tables, using the `books` and `loans` tables as connectors. Your goal is to ensure the query runs efficiently, especially as the dataset grows.

import unittest
import psycopg2
import pandas as pd

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_present(self):
        """Check that EXPLAIN is used in both queries."""
        with open("query.sql", "r") as file:
            query = file.read()
        explain_count = query.upper().count("EXPLAIN")
        _dynamic_test(
            self,
            explain_count >= 2,
            "EXPLAIN is used for both the original and optimized queries.",
            "EXPLAIN must be used for both the original and the optimized queries."
        )

    def test_distinct_used_in_optimized(self):
        """Check that DISTINCT is used in the optimized query to avoid duplicate pairs."""
        with open("query.sql", "r") as file:
            query = file.read()
        # Find the second EXPLAIN and its SELECT
        parts = query.upper().split("EXPLAIN")
        optimized_query = parts[-1] if len(parts) > 1 else ''
        has_distinct = "SELECT DISTINCT" in optimized_query
        _dynamic_test(
            self,
            has_distinct,
            "DISTINCT is used in the optimized query to avoid duplicates.",
            "DISTINCT must be used in the optimized query to avoid duplicate author-borrower pairs."
        )

    def test_column_names(self):
        """Check that the optimized query selects the correct columns and aliases."""
        with open("query.sql", "r") as file:
            query = file.read()
        # Extract the second query (optimized)
        parts = query.split("EXPLAIN")
        if len(parts) < 3:
            self.skipTest("Optimized query not found.")
        sql = parts[-1]
        # Remove any semicolon before SELECT
        sql = sql.strip()
        if not sql.upper().startswith("SELECT"):
            sql = sql[sql.upper().find("SELECT"):]
        # Run the query without EXPLAIN
        try:
            self.cursor.execute(sql)
            columns = [desc[0] for desc in self.cursor.description]
        except Exception as e:
            _dynamic_test(self, False, "", f"Optimized query is not executable: {e}")
            return
        expected_columns = ["author_first", "author_last", "borrower_first", "borrower_last"]
        condition = all(col in columns for col in expected_columns)
        _dynamic_test(
            self,
            condition,
            "Optimized query returns the correct columns.",
            f"Optimized query must return columns: {expected_columns}. Got: {columns}"
        )

    def test_no_duplicate_pairs(self):
        """Check that the optimized query does not return duplicate author-borrower pairs."""
        with open("query.sql", "r") as file:
            query = file.read()
        # Extract the second query (optimized)
        parts = query.split("EXPLAIN")
        if len(parts) < 3:
            self.skipTest("Optimized query not found.")
        sql = parts[-1]
        sql = sql.strip()
        if not sql.upper().startswith("SELECT"):
            sql = sql[sql.upper().find("SELECT"):]
        # Run the query without EXPLAIN
        self.cursor.execute(sql)
        rows = self.cursor.fetchall()
        columns = [desc[0] for desc in self.cursor.description]
        df = pd.DataFrame(rows, columns=columns)
        # Check for duplicates on author_first, author_last, borrower_first, borrower_last
        duplicates = df.duplicated(subset=["author_first", "author_last", "borrower_first", "borrower_last"]).sum()
        _dynamic_test(
            self,
            duplicates == 0,
            "No duplicate author-borrower pairs are present.",
            f"Found {duplicates} duplicate author-borrower pairs in the result."
        )

    def test_joins_present(self):
        """Check that all four tables are joined in the optimized query."""
        with open("query.sql", "r") as file:
            query = file.read()
        parts = query.split("EXPLAIN")
        if len(parts) < 3:
            self.skipTest("Optimized query not found.")
        sql = parts[-1]
        sql = sql.lower()
        tables = ["authors", "books", "loans", "borrowers"]
        joins_present = all(tbl in sql for tbl in tables)
        _dynamic_test(
            self,
            joins_present,
            "All required tables are joined in the optimized query.",
            "The optimized query must join authors, books, loans, and borrowers tables."
        )

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: Optimizing Author-Borrower Queries

解答