As of YugabyteDB 2025.1, you can now use the PostgreSQL feature EXPLAIN (SETTINGS) to see which planner-related GUCs (configuration settings) were non-default when a query was planned.
This is super helpful when:
• Diagnosing why the planner chose a particular path
• Reproducing a plan from another environment (dev vs prod)
• Verifying if session-level overrides (e.g. disabling join types) are in effect
Example: Forcing the Planner to Avoid Nested Loops
-- Create example tables
CREATE TABLE departments(dept_id INT PRIMARY KEY, dept_name TEXT);
CREATE TABLE employees(emp_id INT, dept_id INT REFERENCES departments(dept_id), salary INT);
-- Turn off nested loop joins for this session
SET enable_nestloop = off;
-- Use EXPLAIN (SETTINGS) to check which non-defaults were applied
EXPLAIN (SETTINGS)
SELECT *
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary > 100000;
Example:
yugabyte=# -- Create example tables
yugabyte=# CREATE TABLE departments(dept_id INT PRIMARY KEY, dept_name TEXT);
CREATE TABLE
yugabyte=# CREATE TABLE employees(emp_id INT, dept_id INT REFERENCES departments(dept_id), salary INT);
CREATE TABLE
yugabyte=# -- Turn off index scan for this session
yugabyte=# SET enable_indexscan = off;
SET
yugabyte=# -- Use EXPLAIN (SETTINGS) to check which non-defaults were applied
yugabyte=# EXPLAIN (SETTINGS)
yugabyte-# SELECT *
yugabyte-# FROM employees e
yugabyte-# JOIN departments d ON e.dept_id = d.dept_id
yugabyte-# WHERE e.salary > 100000;
QUERY PLAN
-------------------------------------------------------------------------------
Hash Join (cost=112.50..223.19 rows=1000 width=48)
Hash Cond: (e.dept_id = d.dept_id)
-> Seq Scan on employees e (cost=0.00..102.50 rows=1000 width=12)
Storage Filter: (salary > 100000)
-> Hash (cost=100.00..100.00 rows=1000 width=36)
-> Seq Scan on departments d (cost=0.00..100.00 rows=1000 width=36)
Settings: enable_indexscan = 'off'
(7 rows)
Note the Settings section in the output above:
Settings: enable_indexscan = 'off'
I used this specific example because I ran into a similar situation in the past while tuning some queries. At one point, I temporarily disabled index scans. Then I stepped away from my laptop, probably for dinner, and later returned to work on tuning a different set of queries.
As I kept testing, I couldn’t figure out why none of the new indexes I had just created were being used. After some head-scratching, I finally remembered that I had disabled index scans earlier. I only discovered this by manually checking pg_settings for any non-default values.
If I had access to EXPLAIN (SETTINGS) back then, it would’ve saved me a lot of time and frustration!
Bonus: Combine With ANALYZE
This runs the query, shows actual timings, and includes the influencing settings.
Example:
yugabyte=# EXPLAIN (ANALYZE, SETTINGS)
yugabyte-# SELECT *
yugabyte-# FROM employees e
yugabyte-# JOIN departments d ON e.dept_id = d.dept_id
yugabyte-# WHERE e.salary > 100000;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Hash Join (cost=112.50..223.19 rows=1000 width=48) (actual time=0.407..0.408 rows=0 loops=1)
Hash Cond: (e.dept_id = d.dept_id)
-> Seq Scan on employees e (cost=0.00..102.50 rows=1000 width=12) (actual time=0.406..0.406 rows=0 loops=1)
Storage Filter: (salary > 100000)
-> Hash (cost=100.00..100.00 rows=1000 width=36) (never executed)
-> Seq Scan on departments d (cost=0.00..100.00 rows=1000 width=36) (never executed)
Settings: enable_indexscan = 'off'
Planning Time: 0.158 ms
Execution Time: 0.449 ms
Peak Memory Usage: 56 kB
(10 rows)
Takeaway
If a query plan looks off, or just different than expected -> check your configuration settings. This feature eliminates the guesswork and makes query debugging a lot more transparent.
Have Fun!
