PostgreSQL Performance & Query Optimization · 课时

解读计划节点

学习解读常见的计划节点，例如顺序扫描、索引扫描、连接类型和排序。

解读计划节点 是 CoddyKit 上的免费 PostgreSQL Performance & Query Optimization 课时。这是第 2 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 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 WHERE clause 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 like GROUP BY or 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!

常见问题解答

「解读计划节点」课时是免费的吗？

是的 — 「解读计划节点」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 PostgreSQL Performance & Query Optimization 课程的其余内容，请升级到 CoddyKit PRO。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。

「解读计划节点」这节课中我会学到什么？

学习解读常见的计划节点，例如顺序扫描、索引扫描、连接类型和排序。你通过在浏览器中直接运行的动手代码来练习 PostgreSQL Performance & Query Optimization，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 PostgreSQL Performance & Query Optimization 需要有经验吗？

无需任何先前经验。CoddyKit 上的 PostgreSQL Performance & Query Optimization 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 2 节课，共 4 节。

「解读计划节点」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 PostgreSQL Performance & Query Optimization 课中编写并运行代码吗？

能。每节 PostgreSQL Performance & Query Optimization 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 PostgreSQL Performance & Query Optimization