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_original_query_exists(self):
        """Check if EXPLAIN output for the original query is produced."""
        with open("query.sql", "r") as file:
            sql = file.read()
        explain_count = sql.upper().count("EXPLAIN SELECT * FROM PRODUCTS WHERE NAME LIKE '%MOUSE%' AND PRICE < 30;")
        _dynamic_test(
            self,
            explain_count >= 1,
            "EXPLAIN for the original query is present.",
            "EXPLAIN for the original query is missing."
        )

    def test_index_created(self):
        """Check if an index is created on products(name, price)."""
        with open("query.sql", "r") as file:
            sql = file.read().upper()
        has_index = "CREATE INDEX IDX_PRODUCTS_NAME_PRICE ON PRODUCTS(NAME, PRICE);" in sql or "CREATE INDEX" in sql
        _dynamic_test(
            self,
            has_index,
            "Index creation statement is present.",
            "Index creation statement is missing."
        )

    def test_explain_optimized_query_exists(self):
        """Check if EXPLAIN output for the optimized query is produced."""
        with open("query.sql", "r") as file:
            sql = file.read()
        explain_count = sql.upper().count("EXPLAIN SELECT * FROM PRODUCTS WHERE NAME LIKE '%MOUSE%' AND PRICE < 30;")
        _dynamic_test(
            self,
            explain_count >= 2,
            "EXPLAIN for the optimized query is present.",
            "EXPLAIN for the optimized query is missing."
        )

    def test_index_used_in_plan(self):
        """Check if the optimized plan uses the created index or an index scan, or at least that the plan is improved (i.e., index is available)."""
        # Create the index first
        self.cursor.execute("DROP INDEX IF EXISTS idx_products_name_price;")
        self.cursor.execute("CREATE INDEX idx_products_name_price ON products(name, price);")
        self.connection.commit()
        # Run EXPLAIN
        self.cursor.execute("EXPLAIN SELECT * FROM products WHERE name LIKE '%Mouse%' AND price < 30;")
        rows = self.cursor.fetchall()
        explain_text = " ".join([row[0] for row in rows]).lower()
        # Acceptable: index scan, bitmap index scan, bitmap heap scan, or mention of the index name
        # But if LIKE '%Mouse%' prevents index use, the index should still exist
        index_exists = False
        self.cursor.execute("SELECT indexname FROM pg_indexes WHERE tablename = 'products';")
        indexes = [row[0] for row in self.cursor.fetchall()]
        for idx in indexes:
            if 'idx_products_name_price' in idx:
                index_exists = True
        uses_index = (
            "index scan" in explain_text or
            "bitmap index scan" in explain_text or
            "bitmap heap scan" in explain_text or
            "idx_products_name_price" in explain_text or
            index_exists
        )
        _dynamic_test(
            self,
            uses_index,
            "Optimized plan uses the new index or the index exists for optimizer.",
            "Optimized plan does not use the new index and index does not exist."
        )

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 a Product Search

Solution

Challenge: Optimizing a Product Search

Solution