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

EXPLAINによるクエリプランの分析

EXPLAINとEXPLAIN ANALYZEを使い、PostgreSQLがクエリを実行してインデックスを利用する仕組みを詳しく理解します。

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

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

What's a Query Plan?

When you ask PostgreSQL for data, it doesn't just grab it. It first figures out the best way to get that data. This 'best way' is called a query plan.

Understanding query plans is crucial for database performance tuning. It helps you see how PostgreSQL executes your queries and, more importantly, why some queries are slow.

Meet EXPLAIN

PostgreSQL provides the EXPLAIN command to show you the execution plan for any SQL statement. It's like peeking behind the curtain to see the database's strategy.

EXPLAIN doesn't actually run your query. Instead, it generates an estimated plan based on table statistics. This plan includes details like the chosen access methods (how data is read) and join orders.

Basic EXPLAIN Output

Let's see EXPLAIN in action with a simple query. We'll create a small table and then ask for its plan.

Notice the Seq Scan, meaning 'sequential scan,' which reads every row of the table.

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100)
);

INSERT INTO products (name) VALUES
  ('Laptop'),
  ('Mouse'),
  ('Keyboard'),
  ('Monitor');

EXPLAIN SELECT * FROM products WHERE name = 'Mouse';

Understanding Costs & Rows

The output from EXPLAIN can seem complex, but two key metrics are cost and rows:

cost: An estimated measure of the query's execution expense. It has two numbers: {startup_cost}..{total_cost}. Startup cost is before the first row is returned; total cost is for the entire operation. These are relative, not absolute time units.
rows: The estimated number of rows that the plan node will output.

Lower costs are generally better, but always consider the estimated rows for accuracy.

Scan Types: Seq vs. Index

One of the first things to look for in a query plan is the scan type. This tells you how PostgreSQL accesses the data:

Sequential Scan (Seq Scan): Reads every single row in the table, one by one. This is efficient for small tables or when you need most of the table's data.
Index Scan: Uses an index to quickly locate specific rows. This is usually much faster for queries filtering a small subset of a large table.

The choice depends on your query and table structure.

EXPLAIN ANALYZE: The Real Deal

While EXPLAIN provides estimates, EXPLAIN ANALYZE actually runs the query and collects real-world statistics. This is incredibly powerful for identifying performance bottlenecks.

It adds actual execution times and row counts to the plan, allowing you to compare estimates with reality. Large discrepancies often point to outdated table statistics or a poorly chosen plan.

EXPLAIN ANALYZE in Action

Let's create a larger table and run EXPLAIN ANALYZE on a query that will likely perform a sequential scan. Pay attention to the actual time and rows.

We'll create 1000 users and query for a specific age range.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(100) UNIQUE,
  age INT
);

INSERT INTO users (email, age)
SELECT
  'user' || i || '@example.com',
  (i % 50) + 18 -- Ages from 18 to 67
FROM generate_series(1, 1000) s(i);

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 60;

Seeing Index Benefits

Now, let's create an index on the age column from our users table. Then, we'll re-run the same EXPLAIN ANALYZE query.

You should see a significant change in the plan, likely from a Seq Scan to an Index Scan, and a reduction in actual execution time.

CREATE INDEX idx_users_age ON users (age);

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 60;

Reading Complex Plans

Query plans can get very complex with multiple operations. Here are tips for reading them:

Read Bottom-Up: PostgreSQL typically processes the innermost (bottom) operations first.
Look for Indentation: Indentation shows nested operations. An inner operation provides input to its parent.
Identify Expensive Nodes: Look for nodes with high total_cost or actual time. These are your bottlenecks.
Compare Estimates vs. Actuals: Significant differences in rows between estimated and actual can indicate bad statistics or a misleading plan.

Analyze This Plan!

Consider the following EXPLAIN output for a query on a table named orders. What does it tell us about how the query will be executed?

Seq Scan on orders  (cost=0.00..10.50 rows=5 width=100)
  Filter: (amount > 100)

Recap: Plan Your Queries

Congratulations! You've taken a deep dive into analyzing PostgreSQL query plans.

EXPLAIN shows an estimated query plan.
EXPLAIN ANALYZE runs the query and shows actual execution statistics.
Look for scan types (Seq Scan, Index Scan) to understand data access.
Compare estimated vs. actual costs and rows to find bottlenecks.
Read plans bottom-up to follow the execution flow.

Mastering EXPLAIN is a cornerstone of PostgreSQL performance tuning. Keep practicing!

よくある質問

「EXPLAINによるクエリプランの分析」レッスンは無料ですか？

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

「EXPLAINによるクエリプランの分析」で何を学びますか？

EXPLAINとEXPLAIN ANALYZEを使い、PostgreSQLがクエリを実行してインデックスを利用する仕組みを詳しく理解します。ブラウザで直接実行するハンズオンコードでAdvanced PostgreSQL: Indexing, Partitioning, Replicationを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

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

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

「EXPLAINによるクエリプランの分析」レッスンにはどのくらい時間がかかりますか？

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

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

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

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

← Advanced PostgreSQL: Indexing, Partitioning, Replicationに戻る