0Pricing
Advanced PostgreSQL: Indexing, Partitioning, Replication · レッスン

パーティショニングによるクエリ最適化

PostgreSQLのクエリプランナーがパーティションプルーニングによってパーティショニングを活用し、大幅なパフォーマンス向上を実現する仕組みを学びます。

「パーティショニングによるクエリ最適化」はCoddyKit上の無料Advanced PostgreSQL: Indexing, Partitioning, Replicationレッスンです。 これはレッスン1/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはAdvanced PostgreSQL: Indexing, Partitioning, Replication学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 Advanced PostgreSQL: Indexing, Partitioning, Replicationコースには全4レッスンが含まれています。

このレッスンの一部はまだ翻訳されておらず、英語で表示されています。

Optimize Queries with Partitioning

Partitioning isn't just for managing large tables! It also helps PostgreSQL run your queries much faster. This lesson explores how the database uses partitioning to optimize query performance.

You'll learn about "partition pruning" and how it makes a big difference in query speed.

Introducing Partition Pruning

Partition pruning is a smart optimization technique used by PostgreSQL. When you query a partitioned table, the database doesn't need to scan every single partition.

Instead, it intelligently identifies and skips partitions that cannot possibly contain the data you're looking for. This significantly reduces the amount of data PostgreSQL has to process.

The Pruning Mechanism

The PostgreSQL query planner looks at your query's WHERE clause. It compares the conditions in the WHERE clause with the partition definition (the bounds or list values).

  • If a partition's definition clearly shows it can't match the WHERE clause, that partition is "pruned" or excluded.
  • Only the relevant partitions are scanned, leading to much faster query execution.

Range Partitioning & Pruning

Let's see partition pruning in action with a range-partitioned table. We'll create a table orders partitioned by order_date.

Notice how a query for a specific date range only needs to check certain partitions.

-- Create a range-partitioned table
CREATE TABLE orders (
    order_id SERIAL,
    order_date DATE,
    amount NUMERIC
) PARTITION BY RANGE (order_date);

-- Create partitions for different years
CREATE TABLE orders_2022 PARTITION OF orders
FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

CREATE TABLE orders_2023 PARTITION OF orders
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

-- Insert some sample data
INSERT INTO orders (order_date, amount) VALUES
('2022-03-15', 100.00),
('2023-07-20', 250.00),
('2022-11-01', 50.00);

-- Query for a specific year
EXPLAIN SELECT * FROM orders WHERE order_date >= '2023-01-01';

Analyzing Range Pruning

When you run the EXPLAIN query from the previous scene, you'll see output indicating which partitions were scanned. For EXPLAIN SELECT * FROM orders WHERE order_date >= '2023-01-01';, PostgreSQL will only scan the orders_2023 partition.

The orders_2022 partition is automatically ignored because its range ('2022-01-01' to '2023-01-01') does not overlap with the query's condition.

List Partitioning & Pruning

Partition pruning also works beautifully with list-partitioned tables. Here, we'll create a products table partitioned by category.

A query for a specific category will only access the relevant partition.

-- Create a list-partitioned table
CREATE TABLE products (
    product_id SERIAL,
    name VARCHAR(100),
    category VARCHAR(50),
    price NUMERIC
) PARTITION BY LIST (category);

-- Create partitions for different categories
CREATE TABLE products_electronics PARTITION OF products
FOR VALUES IN ('Electronics');

CREATE TABLE products_books PARTITION OF products
FOR VALUES IN ('Books');

CREATE TABLE products_clothing PARTITION OF products
FOR VALUES IN ('Clothing');

-- Insert some sample data
INSERT INTO products (name, category, price) VALUES
('Laptop', 'Electronics', 1200.00),
('SQL Guide', 'Books', 30.00),
('T-Shirt', 'Clothing', 25.00);

-- Query for a specific category
EXPLAIN SELECT * FROM products WHERE category = 'Books';

Observing List Pruning

Similar to range partitioning, the EXPLAIN output for EXPLAIN SELECT * FROM products WHERE category = 'Books'; will show that PostgreSQL only scans the products_books partition.

The partitions for 'Electronics' and 'Clothing' are pruned because they don't contain the 'Books' category. This keeps your queries efficient even with many partitions.

Confirming Pruning with EXPLAIN

To truly understand if partition pruning is working, always use the EXPLAIN command. Look for lines like "Partition Pruning: Both" or "Partition Pruning: Dynamic" in the output.

  • Both means the planner pruned partitions at planning time.
  • Dynamic means partitions were pruned at execution time (e.g., when using parameterized queries).

These indicators confirm that PostgreSQL is effectively skipping irrelevant data.

Why Pruning is a Game-Changer

Partition pruning offers significant performance advantages:

  • Faster Query Execution: By scanning less data, queries complete much quicker.
  • Reduced I/O: Less data read from disk means less disk activity.
  • Better Cache Utilization: More relevant data fits into memory, improving subsequent query performance.
  • Improved Index Performance: Indexes on individual partitions become more efficient as their scope is narrowed.

Quick Check: Pruning Principles

Consider a table events partitioned by event_date (range partitioning). Partitions exist for each month of 2023 (e.g., events_2023_01, events_2023_02, etc.).

Which query is MOST likely to benefit from partition pruning?

Recap: Smart Queries with Pruning

You've learned that partition pruning is a powerful PostgreSQL optimization. It allows the query planner to intelligently skip irrelevant partitions based on your WHERE clause conditions.

This leads to significantly faster queries, reduced I/O, and better overall database performance. Always use EXPLAIN to verify that pruning is occurring and optimize your partitioned tables effectively!

よくある質問

「パーティショニングによるクエリ最適化」レッスンは無料ですか？

はい。「パーティショニングによるクエリ最適化」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、Advanced PostgreSQL: Indexing, Partitioning, Replicationコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 Advanced PostgreSQL: Indexing, Partitioning, Replicationコースには全4レッスンが含まれています。

「パーティショニングによるクエリ最適化」で何を学びますか？

PostgreSQLのクエリプランナーがパーティションプルーニングによってパーティショニングを活用し、大幅なパフォーマンス向上を実現する仕組みを学びます。 ブラウザで直接実行するハンズオンコードでAdvanced PostgreSQL: Indexing, Partitioning, Replicationを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

Advanced PostgreSQL: Indexing, Partitioning, Replicationを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのAdvanced PostgreSQL: Indexing, Partitioning, Replicationは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン1/4です。

「パーティショニングによるクエリ最適化」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このAdvanced PostgreSQL: Indexing, Partitioning, Replicationレッスンでコードを書いて実行できますか？

はい。すべてのAdvanced PostgreSQL: Indexing, Partitioning, Replicationレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

  1. パーティショニングによるクエリ最適化
  2. パーティションのアタッチとデタッチ
  3. パーティションプルーニングと除外
  4. パーティション単位の結合と集約
← Advanced PostgreSQL: Indexing, Partitioning, Replicationに戻る