You have learned how **indexes** can significantly improve query performance by allowing the database to quickly locate rows that match certain conditions. In this challenge, you will apply your knowledge to a practical scenario involving the `books` table. Imagine you are tasked with optimizing a search query that retrieves books by `genre` and `published_year`. You will need to analyze the query, consider how the `EXPLAIN` output would look without an index, and then create an index that improves the search efficiency.

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_index_exists(self):
        """Check if index on books(genre, published_year) exists."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        self.cursor.execute("""
            SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'books';
        """)
        indexes = self.cursor.fetchall()
        found = False
        for name, definition in indexes:
            if (
                ('genre' in definition and 'published_year' in definition) and
                name.startswith('idx_books')
            ):
                found = True
                break
        _dynamic_test(
            self,
            found,
            "Index on books(genre, published_year) exists.",
            "Index on books(genre, published_year) does not exist or is incorrect."
        )

    def test_index_syntax(self):
        """Check if CREATE INDEX syntax is used correctly."""
        with open("query.sql", "r") as file:
            query = file.read()
        # Normalize for easier matching
        norm_query = normalize_text(query)
        has_create_index = (
            'create index' in norm_query and
            'on books' in norm_query and
            '(' in norm_query and 'genre' in norm_query and 'published_year' in norm_query
        )
        _dynamic_test(
            self,
            has_create_index,
            "CREATE INDEX syntax is used correctly.",
            "CREATE INDEX syntax is missing or incorrect."
        )

    def test_index_columns_order(self):
        """Check if index includes both genre and published_year, in any order."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        self.cursor.execute("""
            SELECT indexdef FROM pg_indexes WHERE tablename = 'books';
        """)
        indexes = self.cursor.fetchall()
        found = False
        for (definition,) in indexes:
            if 'genre' in definition and 'published_year' in definition:
                found = True
                break
        _dynamic_test(
            self,
            found,
            "Index includes both genre and published_year columns.",
            "Index does not include both genre and published_year columns."
        )

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: Indexing for Faster Searches

Solution

Challenge: Indexing for Faster Searches

Solution