**Video: Query Optimization Challenge and EXPLAIN Demonstration**

This short video introduces the optimization challenge for the chapter. It walks you through analyzing a slow join query using the `EXPLAIN` command, visualizing how the database executes the query, and highlights how indexes and query rewriting can improve performance.

In this practical challenge, you will analyze and optimize a join query using the `products` and `sales` tables. **Query optimization** is essential for efficient database performance, especially as data volumes grow. A slow query can often be improved by understanding how the database executes it, applying the right **indexes**, and rewriting the query for better execution plans.

Start by examining the following join query that retrieves the total quantity sold for each product in the "Electronics" category:

```sql
SELECT p.name, SUM(s.quantity) AS total_sold
FROM products p
JOIN sales s ON p.product_id = s.product_id
WHERE p.category = 'Electronics'
GROUP BY p.name;
```

This query may not perform optimally as the dataset grows, especially if the join or filtering step is inefficient. Your goal is to analyze the query using `EXPLAIN`, identify possible bottlenecks, and apply optimization strategies. Consider the following steps:

- Use `EXPLAIN` to review the current query execution plan and look for any sequential scans or inefficient joins;
- Determine which columns would benefit from indexing based on the join and filter conditions;
- Create appropriate indexes to speed up the join and filtering;
- Rewrite the query if necessary to further optimize performance, such as by filtering rows earlier or adjusting the join order.

By applying these techniques, you will gain hands-on experience with real-world SQL optimization tasks that can dramatically improve query speed and resource usage.

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_on_original_query(self):
        """Check if EXPLAIN is used on the original query."""
        with open("query.sql", "r") as file:
            query = file.read()
        has_explain = False
        has_original_query = False
        for stmt in query.split(';'):
            if 'explain' in stmt.lower() and 'from products' in stmt.lower() and 'join sales' in stmt.lower() and "where p.category = 'electronics'" in stmt.lower():
                has_explain = True
                has_original_query = True
        _dynamic_test(
            self,
            has_explain and has_original_query,
            "EXPLAIN is used on the original join query.",
            "EXPLAIN on the original join query is missing."
        )

    def test_index_on_products_category(self):
        """Check if index is created on products.category."""
        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 = 'products';
        """)
        indexes = self.cursor.fetchall()
        found = False
        for idx in indexes:
            if 'category' in idx[1]:
                found = True
        _dynamic_test(
            self,
            found,
            "Index on products.category is created.",
            "Index on products.category is missing."
        )

    def test_index_on_sales_product_id(self):
        """Check if index is created on sales.product_id."""
        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 = 'sales';
        """)
        indexes = self.cursor.fetchall()
        found = False
        for idx in indexes:
            if 'product_id' in idx[1]:
                found = True
        _dynamic_test(
            self,
            found,
            "Index on sales.product_id is created.",
            "Index on sales.product_id is missing."
        )

    def test_explain_on_optimized_query(self):
        """Check if EXPLAIN is used on the optimized query."""
        with open("query.sql", "r") as file:
            query = file.read()
        found = False
        for stmt in query.split(';'):
            if 'explain' in stmt.lower() and 'from products' in stmt.lower() and 'join sales' in stmt.lower() and "where p.category = 'electronics'" in stmt.lower():
                found = True
        _dynamic_test(
            self,
            found,
            "EXPLAIN is used on the optimized query.",
            "EXPLAIN on the optimized query is missing."
        )

    def test_explain_on_rewritten_query(self):
        """Check if EXPLAIN is used on a rewritten query with filtering before join (subquery)."""
        with open("query.sql", "r") as file:
            query = file.read()
        found = False
        for stmt in query.split(';'):
            if 'explain' in stmt.lower() and 'from (select * from products where category = ' in stmt.lower() and 'join sales' in stmt.lower():
                found = True
        _dynamic_test(
            self,
            found,
            "EXPLAIN is used on a rewritten query with subquery filtering.",
            "EXPLAIN on a rewritten query with subquery filtering is missing."
        )

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

Start på en spennende reise gjennom SQL med avanserte teknikker i SQL. Lær om de viktige ACID-egenskapene som bidrar til å opprettholde datanøyaktighet i databasetransaksjoner. Oppdag avanserte metoder for å optimalisere spørringer for å forbedre databaseytelsen. Utforsk spesielle SQL-funksjoner som vindusfunksjoner, triggere, Data Control Language (DCL) og NoSQL-databaser. Dette vil hjelpe deg å håndtere moderne databaseadministrasjon trygt og effektivt.

Master advanced SQL concepts including ACID properties, query optimization, indexing, triggers, window functions, and data control. This course is designed for learners with a solid SQL foundation who want to deepen their understanding and practical skills in optimizing queries and leveraging advanced database features.

Challenge: Query Optimization

Løsning