**Video Description:**  
This short video introduces the challenge of creating a B-tree index on the `category` column of the `products` table. It demonstrates why such an index is useful for optimizing queries that filter products by category, and walks you through the syntax for index creation and how to write a query that benefits from the new index.

インデックス、特に**B-treeインデックス**が、大規模なテーブルで値をフィルタリングまたは検索するクエリのパフォーマンスを大幅に向上させることを学びました。このチャレンジでは、`products`テーブルに対して**B-treeインデックス**の恩恵を受けるクエリを特定することが目標です。

たとえば、特定のカテゴリ、例えば`"Electronics"`の商品をすべて取得する必要が頻繁にあるシナリオを考えてみましょう。インデックスがない場合、データベースは一致するカテゴリを見つけるために`products`テーブルのすべての行をスキャンしなければなりません。`category`列に**B-treeインデックス**を作成することで、データベースは指定したカテゴリの商品を迅速に特定でき、これらのクエリがはるかに効率的になります。

この種のクエリを最適化するには、次の点に注意してください：
- `WHERE`テーブルの`category`列で`products`句を使用してフィルタリングするクエリを探すこと；
- **B-treeインデックス**は、（商品カテゴリのように）多くの重複値を持つ列に最適であり、その列で頻繁に検索やフィルタリングを行う場合に特に有効であることを認識すること。

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_index_created(self):
        """Check if a B-tree index on category column of products table was created."""
        with open("query.sql", "r") as file:
            query = file.read()
    
        # Execute only CREATE INDEX statements from user's file
        index_lines = [
            line.strip().rstrip(";")
            for line in query.splitlines()
            if line.strip().lower().startswith("create index")
        ]
    
        for stmt in index_lines:
            self.cursor.execute(stmt)
    
        self.connection.commit()
    
        self.cursor.execute("""
            SELECT indexdef
            FROM pg_indexes
            WHERE tablename = 'products';
        """)
        defs = [row[0].lower() for row in self.cursor.fetchall()]
    
        found = any(
            ('(category' in d or '( category' in d) and ('products' in d)
            for d in defs
        )
    
        _dynamic_test(
            self,
            found,
            "A B-tree index on the category column of products table was created.",
            "No B-tree index was found on the category column of products table."
        )

    def test_query_selects_electronics(self):
        """Check that the query selects all columns from products where category = 'Electronics'."""
        with open("query.sql", "r") as file:
            query = file.read()
        # Remove index creation line if present
        user_query = "\\n".join([
            line for line in query.splitlines()
            if not line.strip().lower().startswith("create index")
        ]).strip()
        # Run user's select query
        self.cursor.execute(user_query)
        user_result = self.cursor.fetchall()
        user_columns = [desc[0] for desc in self.cursor.description]
        user_df = pd.DataFrame(user_result, columns=user_columns)
        # Get correct result for Electronics
        self.cursor.execute("SELECT * FROM products WHERE category = 'Electronics';")
        sol_result = self.cursor.fetchall()
        sol_columns = [desc[0] for desc in self.cursor.description]
        sol_df = pd.DataFrame(sol_result, columns=sol_columns)
        # Compare results ignoring order
        condition = (
            set(tuple(row) for row in user_df.to_numpy()) == set(tuple(row) for row in sol_df.to_numpy())
            and set(user_df.columns) == set(sol_df.columns)
        )
        _dynamic_test(
            self,
            condition,
            "The query selects all columns from products where category = 'Electronics'.",
            "The query does not correctly select all columns from products where category = 'Electronics'."
        )

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の基礎をしっかりと身につけている学習者が、クエリの最適化や高度なデータベース機能の活用に関する理解と実践的スキルをさらに深めることを目的としています。

チャレンジ：Bツリーインデックスの作成

解答