Optimizing PostgreSQL chevron_right Planner Settings & Overriding Behavior in PostgreSQL (Use with Caution!)

Planner Settings & Overriding Behavior in PostgreSQL (Use with Caution!)

calendar_today schedule 6 min read

Learning Objectives: By the end of this lesson, you will be able to:

  • Identify key planner settings that influence query plans
  • Experiment with enabling/disabling scan and join types using session settings
  • Understand when (and when not) to override planner behavior

1. Welcome and Introduction (5 minutes)

Icebreaker Activity

Have you ever wondered if you could “force” PostgreSQL to use an index, or stop it from doing a sequential scan? Today, you’ll learn about the planner’s “knobs”—and why you should use them sparingly!

Lesson Overview

  • Duration: 90 minutes
  • Prerequisites: Completed Days 1-4, understanding of query plans and statistics
  • Materials Needed: PostgreSQL database, SQL client

2. Learning Objectives and Session Outline (5 minutes)

What We’ll Cover Today:

  1. What are planner settings and why do they exist?
  2. How to use SET LOCAL to override planner behavior
  3. The risks of forcing planner decisions
  4. Hands-on practice and exercises
  5. Common pitfalls and troubleshooting

Success Criteria:

By the end of this session, you should be able to safely experiment with planner settings and understand their impact on query plans.

3. Foundation Knowledge (15-20 minutes)

Core Concepts

What Are Planner Settings?

Planner settings are configuration options that influence which scan and join types PostgreSQL considers for a query. They are useful for testing and debugging, but rarely for production use.

Real-World Analogy: Imagine telling your GPS to avoid highways, even if it makes your trip longer. You can do it, but it’s usually not the best idea!

Why This Matters:

  • Helps you understand why the planner chooses certain plans
  • Lets you test “what if” scenarios for query optimization

Key Planner Settings

  • enable_seqscan: Allow/disallow sequential scans
  • enable_indexscan: Allow/disallow index scans
  • enable_bitmapscan: Allow/disallow bitmap index scans
  • enable_sort: Allow/disallow explicit sorts
  • enable_hashjoin, enable_mergejoin, enable_nestloop: Control join types

Using SET LOCAL

  • Temporarily changes a setting for the current transaction/session
  • Example: SET LOCAL enable_seqscan = off;

Key Terminology

  • Sequential Scan (Seq Scan): Reads the whole table
  • Index Scan: Uses an index to find rows
  • Bitmap Scan: Combines multiple indexes or conditions
  • Join Types: Nested Loop, Hash Join, Merge Join
  • Session Setting: A configuration that applies only to your current session/transaction

4. Detailed Explanation and Examples (25-30 minutes)

Understanding Planner Settings in Action

Planner settings let you see how the plan changes when certain options are disabled. This is useful for learning and for diagnosing why the planner made a particular choice.

Practical Applications

  • Test if an index would help by disabling sequential scans
  • See how join types affect performance
  • Debug unexpected plan choices

Common Misconceptions

  1. Misconception: “Disabling sequential scans will always make queries faster.” Reality: Sometimes a sequential scan is the best option (e.g., small tables, broad queries).
  2. Misconception: “You should force the planner in production.” Reality: The planner usually knows best; overrides are for testing only.

5. Live Coding Demonstration (20-25 minutes)

Example 1: Disabling Sequential Scan

SET LOCAL enable_seqscan = off;
EXPLAIN SELECT * FROM users WHERE id = 42;

Explanation:

  • Forces the planner to use an index if possible
  • May result in a worse plan if no suitable index exists

Example 2: Disabling Index Scan

SET LOCAL enable_indexscan = off;
EXPLAIN SELECT * FROM users WHERE id = 42;
  • Forces a sequential scan, even if an index is available

Example 3: Forcing Join Types

SET LOCAL enable_hashjoin = off;
EXPLAIN SELECT * FROM users u JOIN orders o ON u.id = o.user_id;
  • Disables hash joins, so the planner must use nested loop or merge join

Debugging Demonstration

Show how disabling a scan type can make a plan worse:

-- With index scan enabled
EXPLAIN SELECT * FROM users WHERE id = 42;
-- Now disable index scan
SET LOCAL enable_indexscan = off;
EXPLAIN SELECT * FROM users WHERE id = 42;

6. Hands-On Practice (30-40 minutes)

Exercise 1: Basic Practice (10 minutes)

Objective: Experiment with planner settings and observe plan changes

Instructions:

  1. Pick a query that uses an index
  2. Run EXPLAIN and note the plan
  3. Disable the relevant scan type and rerun the plan
  4. Compare the results

Starter Code:

EXPLAIN SELECT * FROM your_table WHERE some_column = 'value';
SET LOCAL enable_indexscan = off;
EXPLAIN SELECT * FROM your_table WHERE some_column = 'value';

Solution:

-- Example output
Index Scan on your_table ...
-- After disabling index scan
Seq Scan on your_table ...

Exercise 2: Intermediate Challenge (15 minutes)

Objective: Test join type settings

Requirements:

  • Run a join query
  • Disable one or more join types
  • Observe how the plan changes

Hints:

  • Use SET LOCAL enable_hashjoin = off; or similar
  • Try with different table sizes

Exercise 3: Creative Application (15 minutes)

Objective: Explore the impact of multiple planner settings

Challenge:

  • Disable several scan/join types at once
  • See if the planner can still find a plan
  • Try with complex queries

Extension Ideas:

  • Experiment with enable_sort and see how plans change
  • Try with different data distributions

7. Common Pitfalls and Troubleshooting (10 minutes)

Frequent Mistakes

  1. Problem: Leaving planner settings changed in production Cause: Forgetting to reset settings Solution: Use SET LOCAL (not SET) and always revert changes

  2. Problem: Forcing a plan that is actually worse Cause: Overriding the planner’s best choice Solution: Use overrides for testing only, not as a permanent fix

Debugging Tips

  • Always compare performance before and after changing settings
  • Use SET LOCAL to avoid affecting other sessions
  • Document any overrides used for testing

8. Best Practices and Professional Tips (5 minutes)

Industry Standards

  • Use planner overrides for testing and learning, not production
  • Trust the planner unless you have strong evidence otherwise
  • Document any experiments with planner settings

Next Steps in Your Learning Journey

  • Learn about common query patterns and their plans (see Day 6)
  • Explore advanced planner parameters for research
  • Practice with real-world queries and overrides

9. Summary and Review (5 minutes)

Key Takeaways

  1. Planner settings let you experiment with query plans
  2. Use overrides with caution—usually for testing only
  3. Always compare plans and performance before and after changes

Quick Review Questions

  1. What does SET LOCAL enable_seqscan = off; do?
  2. Why shouldn’t you force planner settings in production?
  3. How can you safely experiment with planner overrides?

For Instructors

Teaching Tips

  • Demonstrate with queries that change dramatically when scan types are disabled
  • Emphasize the risks of forcing planner decisions
  • Use real-world examples of when overrides helped diagnose a problem

Assessment Ideas

  • Have students experiment with planner settings and explain the results
  • Ask students to identify when an override made a plan worse
  • Peer review: students share their findings from planner experiments

Adaptation Notes

  • For beginners: focus on disabling/enabling scan types
  • For advanced students: include join types and complex queries
  • Online: use screen sharing to show live plan changes