you can disable mergejoin, I think the output about this will appear.
I did it and disabled nodes were displayed in the query explain:
alena@postgres=# CREATE TABLE tab_a (id integer);
alena@postgres=# CREATE TABLE tab_a (id integer);
alena@postgres=# CREATE TABLE tab_b (id integer);
alena@postgres=# CREATE TABLE tab_b (id integer);
alena@postgres=# SET enable_nestloop = off;
alena@postgres=# SET enable_nestloop = off;
alena@postgres=# SET enable_hashjoin = off;
alena@postgres=# SET enable_mergejoin = off;
alena@postgres=# EXPLAIN SELECT * FROM tab_a JOIN tab_b USING (id);
QUERY PLAN
---------------------------------------------------------------------
Nested Loop (cost=0.00..97614.88 rows=32512 width=4)
Disabled Nodes: 1
Join Filter: (tab_a.id = tab_b.id)
-> Seq Scan on tab_a (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on tab_b (cost=0.00..35.50 rows=2550 width=4)
(6 rows)
The number of disabled nodes
alena@postgres=# set enable_seqscan =off;
SET
alena@postgres=# EXPLAIN SELECT * FROM tab_a JOIN tab_b USING (id);
QUERY PLAN
---------------------------------------------------------------------
Nested Loop (cost=0.00..97614.88 rows=32512 width=4)
Disabled Nodes: 3
Join Filter: (tab_a.id = tab_b.id)
-> Seq Scan on tab_a (cost=0.00..35.50 rows=2550 width=4)
Disabled Nodes: 1
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
Disabled Nodes: 1
-> Seq Scan on tab_b (cost=0.00..35.50 rows=2550 width=4)
Disabled Nodes: 1
(9 rows)
Here is an example, if you also disable seqscan. the number of disabled nodes in a join connection is equal to the sum of all disabled subnodes and the nestedloop itself (it is also disabled).
Honestly, I like this patch. Before this patch, when disabling any algorithm in the optimizer, the cost increased significantly and I’m not sure that this was a reliable solution due to the fact that the cost even without disabling can be greatly increased because of the high cardinality, for example.
Right there, the mechanism is simple and more honest in my opinion - we simply count the number of disabled nodes and discard the paths with the largest number of them.
--
Regards,
Alena Rybakina
Postgres Professional
