Skip to Content
SQL Advanced: Queries That Hold Up Under Load
Advanced
0 likes
Courses
SQL Advanced: Queries That Hold Up Under Load

Check Yourself: Reading Plans

Five scenario questions on filters, loops, stale statistics, dependent columns and EXPLAIN ANALYZE on data changes. If you miss more than one, reread the lessons in this section.

5 min SQLDatabasesTesting and DebuggingPerformance Optimization Advanced
Prev Next
Fullscreen Share
Course content

Sign in to save your progress.

Course Home
1. In a plan, a Seq Scan node shows Rows Removed by Filter: 18400 and returns 1,600 rows. What does that tell you?
18,400 rows were deleted from the table by the query. The scan took 18,400 milliseconds. The node read about 20,000 rows and threw away 18,400 of them to keep 1,600. An index removed 18,400 dead rows from the table.
2. A node under a SubPlan shows actual rows=2500 and loops=8. How many rows did that node return in total?
2,500, because actual rows is always the total. 8, because loops is the row count. About 20,000, because actual rows is the average for one loop and the node ran 8 times. 10,500, because the two numbers are added.
3. Right after a bulk load, EXPLAIN shows an estimate of 90 rows for a filter that really returns 600. The table was never analyzed. What is the first thing to try?
Add an index on the filtered column. Raise work_mem to 1 GB. Rewrite the query with a CTE. Run ANALYZE on the table and read the plan again.
4. Each warehouse belongs to exactly one region. The query filters on warehouse W03 and region R02, and the planner expects 100 rows while 1,000 come back. What is the cause and the fix?
The planner multiplies the two shares as if the columns were independent. Declare the dependency with CREATE STATISTICS and run ANALYZE. PostgreSQL cannot estimate a filter on two columns. Use only one column in the query. The table is too small. Add more rows until the estimate improves. The estimate is wrong because autovacuum is on. Turn it off.
5. You want to see the real plan of a DELETE on the production server. Which way is safe?
Run EXPLAIN without ANALYZE and trust the actual row counts it prints. Run EXPLAIN ANALYZE inside BEGIN and finish with ROLLBACK. Run EXPLAIN ANALYZE as a read-only user, which turns the DELETE into a SELECT. Run EXPLAIN ANALYZE on its own. It only shows a plan.

About

Five scenario questions on filters, loops, stale statistics, dependent columns and EXPLAIN ANALYZE on data changes. If you miss more than one, reread the lessons in this section.

Duration
5 min
Last update
09/24/2026
Was this helpful?
​ 0 ​ 0

Comments (0)

Sign in to comment

No comments yet.