**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

Inizia un entusiasmante percorso attraverso SQL con Tecniche Avanzate in SQL. Approfondisci le proprietà fondamentali ACID che garantiscono l'integrità dei dati nelle transazioni di database. Scopri metodi avanzati per ottimizzare le query e migliorare le prestazioni del database. Esplora funzionalità speciali di SQL come funzioni di finestra, trigger, Data Control Language (DCL) e database NoSQL. Questo ti permetterà di gestire la moderna amministrazione dei database con sicurezza ed efficacia.

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

Soluzione