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

この実践的なチャレンジでは、`products` テーブルと `sales` テーブルを使用した結合クエリの分析と最適化を行います。**クエリ最適化**は、特にデータ量が増加するにつれて、データベースのパフォーマンスを効率的に保つために不可欠です。遅いクエリは、データベースがどのように実行しているかを理解し、適切な **インデックス** を適用し、より良い実行計画のためにクエリを書き換えることで、改善できる場合が多くあります。

まず、"Electronics" カテゴリ内の各商品の合計販売数量を取得する、次の結合クエリを確認します:

```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;
```

このクエリは、特に結合やフィルタリングのステップが非効率な場合、データセットが大きくなると最適に動作しない可能性があります。`EXPLAIN` を使用してクエリを分析し、ボトルネックを特定し、最適化戦略を適用することが目標です。以下のステップを検討してください:

- `EXPLAIN` を使って現在のクエリ実行計画を確認し、シーケンシャルスキャンや非効率な結合がないか調査;
- 結合やフィルタ条件に基づき、どのカラムにインデックスを作成すると効果的かを判断;
- 結合やフィルタリングを高速化するために適切なインデックスを作成;
- 必要に応じて、より早い段階でのフィルタリングや結合順序の調整など、クエリを書き換えてさらなるパフォーマンス向上を図る。

これらの手法を適用することで、実際のSQL最適化作業を体験し、クエリの速度やリソース使用量を大幅に改善するスキルを身につけることができます。

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

データベーストランザクションにおけるデータの正確性を維持するための重要なACID特性について学びます。データベースのパフォーマンスを向上させるための高度なクエリ最適化手法を紹介します。

ACID特性、クエリ最適化、インデックス、トリガー、ウィンドウ関数、データ制御など、SQLの高度な概念を習得します。本コースは、SQLの基礎をしっかりと身につけている学習者が、クエリの最適化や高度なデータベース機能の活用に関する理解と実践的スキルをさらに深めることを目的としています。

チャレンジ：クエリ最適化

解答