PostgreSQL Performance & Query Optimization · 강의

성능 병목 식별

쿼리 실행 계획을 사용하여 느린 작업, 과도한 I/O 및 기타 성능 저해 요인을 식별합니다.

레슨 3/411개 단계

성능 병목 식별은(는) CoddyKit의 무료 PostgreSQL Performance & Query Optimization 강의입니다. 이것은 4개 중 3번째 강의입니다. 아래에서 전체 강의를 무료로 읽을 수 있으며, 내장 코드 에디터와 24/7 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을(를) 배우세요 — 무료

브라우저에서 실제 코드를 작성하고 실행하며, 24/7 AI 튜터로부터 즉각적인 도움을 받고, 웹이나 앱에서 중단한 부분부터 계속 학습하세요.

코스: 22
레슨: 88

무료로 학습 시작 전체 코스 보기

자주 묻는 질문

“성능 병목 식별” 강의는 무료인가요?

네 — “성능 병목 식별” 전체 내용을 이 웹사이트에서 무료로 읽을 수 있습니다. 인터랙티브하게 실습하려면(내장 코드 에디터와 24/7 AI 튜터), CoddyKit PRO로 업그레이드하면 PostgreSQL Performance & Query Optimization 강의 전체를 잠금 해제할 수 있습니다. PostgreSQL Performance & Query Optimization 강의에는 총 4개의 강의가 포함되어 있습니다.

“성능 병목 식별”에서 뭘 배우나요?

쿼리 실행 계획을 사용하여 느린 작업, 과도한 I/O 및 기타 성능 저해 요인을 식별합니다. 브라우저에서 직접 실행하는 실습 코드로 PostgreSQL Performance & Query Optimization을(를) 배우며, 24/7 AI 튜터가 강의를 진행하면서 질문에 답변해줍니다.

PostgreSQL Performance & Query Optimization을(를) 시작하는 데 경험이 필요한가요?

사전 경험은 필요하지 않습니다. CoddyKit의 PostgreSQL Performance & Query Optimization은(는) 초급자부터 고급 학습자까지를 위해 구성되어 있으므로, 여기서 시작하거나 처음부터 시작할 수 있으며 자신의 속도대로 진행할 수 있습니다. 이것은 4개 중 3번째 강의입니다.

“성능 병목 식별” 강의는 얼마나 걸리나요?

대부분의 CoddyKit 강의는 약 5~10분이 소요됩니다. 각 강의는 간결하고 인터랙티브하여 꾸준한 진행이 가능하며, 웹과 앱에서 중단한 부분부터 바로 시작할 수 있습니다.

이 PostgreSQL Performance & Query Optimization 강의에서 코드를 작성하고 실행할 수 있나요?

네. 모든 PostgreSQL Performance & Query Optimization 강의에는 내장 코드 에디터가 포함되어 있으므로, 브라우저에서 바로 실제 코드를 작성하고 실행한 후 즉시 AI 피드백을 받을 수 있습니다 — 로컬 설정이 필요 없습니다.

이 강의의 모든 강의

← PostgreSQL Performance & Query Optimization(으)로 돌아가기