PostgreSQL Performance & Query Optimization · 课时

识别性能瓶颈

使用查询计划识别运行缓慢的操作、过多的 I/O 以及其他性能阻碍因素。

第 3 / 4 课11 个步骤

识别性能瓶颈 是 CoddyKit 上的免费 PostgreSQL Performance & Query Optimization 课时。这是第 3 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 PostgreSQL Performance & Query Optimization 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。

本课时的部分内容尚未翻译，以英文显示。

What's a Query Bottleneck?

Imagine your database query as a car race. A bottleneck is like a slow section of the track that causes all cars to slow down, holding up the entire race.

In PostgreSQL, a bottleneck is any operation within a query execution plan that consumes a disproportionate amount of resources (time, CPU, I/O) and thus slows down the entire query.

Why Find Bottlenecks?

Identifying bottlenecks is the first step towards improving query performance. By pinpointing the slowest parts, you can focus your optimization efforts where they'll have the biggest impact.

Faster Queries: Your applications respond quicker.
Less Resource Usage: Database server runs more efficiently.
Better User Experience: Happier users!

Key EXPLAIN ANALYZE Metrics

When using EXPLAIN ANALYZE, several metrics help us spot bottlenecks:

actual time: The real time (in milliseconds) spent executing an operation. This is crucial!
rows: Number of rows processed or returned by an operation.
loops: How many times an operation was executed.
Buffers: Details on disk I/O, indicating how much data was read from or written to memory/disk.

Spotting Slow Operations: `actual time`

The most direct way to find a slow operation is to look for plan nodes with a high actual time value.

Compare the actual time for different nodes. If one node's actual time is significantly higher than others, it's likely a bottleneck.

Remember, actual time has two values: (start_time..end_time). We care about the difference, which is the total time for that node.

Example: High `actual time` Seq Scan

Let's create a table and run a query that will likely result in a slow Seq Scan (sequential scan) because it lacks an index on the filtered column.

Notice the high actual time for the Seq Scan node in the simulated output:

-> Seq Scan on products (cost=0.00..15.50 rows=1000 width=36) (actual time=50.231..120.567 rows=100 loops=1) Filter: (price > 90) Rows Removed by Filter: 900

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

INSERT INTO products (name, price)
SELECT 'Product ' || i, (i % 100) + 1
FROM generate_series(1, 1000) s(i);

EXPLAIN ANALYZE SELECT * FROM products WHERE price > 90;

Excessive I/O: `Buffers`

High disk I/O is a common bottleneck. The Buffers section in EXPLAIN ANALYZE helps identify this.

shared hit: Data found in shared buffers (memory). Good!
shared read: Data had to be read from disk. This is what we want to minimize!
shared dirtied/written: Data modified/written to disk.

Many shared read buffers often indicate a need for better indexing or a more selective query.

Example: High `Buffers: shared read`

If a query needs to scan a large portion of a table that isn't cached, you'll see many shared read buffers.

Here, even with an index, scanning a large range might still involve reading many blocks from disk:

-> Index Scan using products_price_idx on products (cost=0.43..15.50 rows=500 width=36) (actual time=5.123..25.456 rows=500 loops=1) Index Cond: (price BETWEEN 10 AND 60) Buffers: shared hit=100 read=400

The read=400 indicates 400 data blocks were fetched from disk. This can be a bottleneck.

CREATE INDEX products_price_idx ON products (price);

EXPLAIN ANALYZE SELECT * FROM products WHERE price BETWEEN 10 AND 60;

Inefficient Filtering: Many Rows, Few Results

Sometimes an operation processes many rows but discards most of them with a filter. This is inefficient.

Look for a plan node where rows is very high, but the number of Rows Removed by Filter is also high, meaning a lot of work was done only to throw data away.

This often suggests that the filter could be applied earlier, perhaps with a more specific index.

Other Bottleneck Indicators

Beyond time and I/O, keep an eye on these:

Sort operations: If a sort takes a long time or reports Sort Method: external merge Disk: XkB, it means it couldn't fit in work_mem and spilled to disk, which is slow.
High work_mem usage: Some operations (like hashes or sorts) need memory. If they use a lot, it can impact other queries or cause disk spills.
Expensive Join Types: Nested Loop, Hash Join, or Merge Join can be bottlenecks if they process huge intermediate result sets.

Identify the Bottleneck

Consider the following simplified EXPLAIN ANALYZE output:

->  Hash Join (cost=10.00..200.00 rows=1000 width=64) (actual time=10.000..1500.000 rows=1000 loops=1)
      Hash Cond: (a.id = b.id)
      Buffers: shared hit=50 read=1000
      ->  Seq Scan on table_a a (cost=0.00..100.00 rows=10000 width=32) (actual time=0.100..50.000 rows=10000 loops=1)
            Buffers: shared hit=10 read=90
      ->  Hash (cost=9.00..9.00 rows=1000 width=32) (actual time=9.000..9.000 rows=1000 loops=1)
            ->  Seq Scan on table_b b (cost=0.00..9.00 rows=1000 width=32) (actual time=0.050..8.000 rows=1000 loops=1)

Which part of this plan is the most likely bottleneck?

在交互式课程中回答

Recap: Pinpointing Bottlenecks

You've learned to identify performance bottlenecks in PostgreSQL query plans!

Key takeaways:

Look for high actual time to find slow operations.
Monitor Buffers: shared read for excessive disk I/O.
Be wary of operations processing many rows but returning few.
Sort operations spilling to disk are also red flags.

With these skills, you can now effectively diagnose why your queries are running slow!

免费开始

用 AI 导师学习 SQL — 免费

在浏览器中编写并运行真实代码，获得全天候 AI 导师的即时帮助，并在网页或应用中继续学习。

课程: 22
课程: 88

免费开始学习查看完整课程

常见问题解答

「识别性能瓶颈」课时是免费的吗？

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

「识别性能瓶颈」这节课中我会学到什么？

使用查询计划识别运行缓慢的操作、过多的 I/O 以及其他性能阻碍因素。你通过在浏览器中直接运行的动手代码来练习 PostgreSQL Performance & Query Optimization，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

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

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

「识别性能瓶颈」课时需要多长时间？

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

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

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

此课程中的所有课时

← 返回 PostgreSQL Performance & Query Optimization