プランノードの読み解き方
シーケンシャルスキャン、インデックススキャン、結合方式、ソートなど、一般的なプランノードを読み解く方法を学びます。
「プランノードの読み解き方」はCoddyKit上の無料PostgreSQL Performance & Query Optimizationレッスンです。 これはレッスン2/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはPostgreSQL Performance & Query Optimization学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 PostgreSQL Performance & Query Optimizationコースには全4レッスンが含まれています。
このレッスンの一部はまだ翻訳されておらず、英語で表示されています。
Decoding Query Plans
Welcome back! In the previous lesson, you learned how to use EXPLAIN to view a query's execution plan. Now, let's dive into interpreting the different 'nodes' within these plans.
Each node represents a specific operation PostgreSQL performs. Understanding them is key to identifying performance bottlenecks.
What are Plan Nodes?
Think of a query plan as a tree, where each branch and leaf is a 'node'. These nodes tell you:
- What operation is being done (e.g., scanning, sorting, joining).
- How it's being done (e.g., sequentially, using an index).
- Cost estimates: How much time and resources PostgreSQL *expects* the operation to take.
We'll look at the most common and important node types.
Sequential Scan: The Full Read
A Sequential Scan (often called a 'Seq Scan') means PostgreSQL reads every single row in a table from start to finish to find the data it needs.
- When it happens: For small tables, or when querying a large portion of a table with no suitable index.
- Performance impact: Can be slow for large tables, especially if only a few rows are needed.
It's like looking through every page of a book to find one sentence.
Seq Scan Example
Let's see a sequential scan in action. We'll create a simple table and then query it without an index.
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10, 2)
);
INSERT INTO products (name, price) VALUES
('Laptop', 1200.00),
('Mouse', 25.00),
('Keyboard', 75.00),
('Monitor', 300.00);
EXPLAIN SELECT * FROM products WHERE price > 100;Index Scan: Targeted Search
An Index Scan is much more efficient. PostgreSQL uses an index to quickly locate the specific rows it needs, much like using an index in a book.
- When it happens: When a query uses a
WHEREclause on an indexed column, and the index is selective enough. - Performance impact: Generally much faster than a sequential scan for selective queries on large tables.
It allows PostgreSQL to jump directly to the relevant data pages.
Index Scan Example
Now, let's add an index to our products table and observe the change in the query plan.
CREATE INDEX idx_products_price ON products (price);
EXPLAIN SELECT * FROM products WHERE price > 100;Sort Node: Ordering Data
The Sort node appears when PostgreSQL needs to order data, typically for an ORDER BY or GROUP BY clause, and there isn't an index that can provide the data in the required order.
- When it happens: Explicit
ORDER BY, or implicitly for some operations likeGROUP BYor unique constraints. - Performance impact: Sorting can be CPU and I/O intensive, especially for large datasets.
If the sort happens 'on disk' (meaning it can't fit in memory), it becomes even slower.
Sort Node Example
Here's an example where PostgreSQL has to sort the results because no index exists for the ordering column.
EXPLAIN SELECT name, price FROM products ORDER BY name DESC;Join Nodes: Combining Tables
When you join two or more tables, PostgreSQL uses specific Join Nodes to combine the data. There are three primary types:
- Nested Loop Join: Often good for small inner tables or when an index is available.
- Hash Join: Efficient for larger tables where no useful index is present on the join key.
- Merge Join: Requires both inputs to be sorted on the join key, then merges them.
The choice depends on table sizes, available indexes, and data distribution.
Nested Loop Join Example
Let's create another table and then join it with products to see a Nested Loop Join. This often happens when one side of the join is small.
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
product_id INT,
quantity INT
);
INSERT INTO orders (product_id, quantity) VALUES
(1, 1),
(2, 2),
(1, 3);
EXPLAIN SELECT p.name, o.quantity
FROM products p JOIN orders o ON p.product_id = o.product_id
WHERE o.order_id = 2;Identify the Scan Type
Consider the following query and its execution plan snippet. What kind of scan is most likely being performed on the customers table?
EXPLAIN SELECT * FROM customers WHERE age > 30;
Partial Plan Output (assume no index on age):
-> Seq Scan on customers (cost=0.00..10.50 rows=3 width=...)Recap: Decoding Plan Nodes
You've taken a big step in understanding PostgreSQL performance by learning to interpret key plan nodes!
- Sequential Scan: Full table read, can be slow for large tables.
- Index Scan: Uses an index for targeted row access, faster for selective queries.
- Sort: Occurs when data needs ordering and no suitable index exists.
- Join Nodes: (Nested Loop, Hash, Merge) combine data from multiple tables, chosen based on data size and indexes.
In the next lesson, we'll put this knowledge to use to identify actual performance bottlenecks!
よくある質問
「プランノードの読み解き方」レッスンは無料ですか？
はい。「プランノードの読み解き方」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、PostgreSQL Performance & Query Optimizationコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 PostgreSQL Performance & Query Optimizationコースには全4レッスンが含まれています。
「プランノードの読み解き方」で何を学びますか？
シーケンシャルスキャン、インデックススキャン、結合方式、ソートなど、一般的なプランノードを読み解く方法を学びます。 ブラウザで直接実行するハンズオンコードでPostgreSQL Performance & Query Optimizationを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。
PostgreSQL Performance & Query Optimizationを始めるのに経験は必要ですか？
事前経験は必要ありません。CoddyKitのPostgreSQL Performance & Query Optimizationは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン2/4です。
「プランノードの読み解き方」レッスンにはどのくらい時間がかかりますか？
ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。
このPostgreSQL Performance & Query Optimizationレッスンでコードを書いて実行できますか？
はい。すべてのPostgreSQL Performance & Query Optimizationレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。