BTW, in Steinar's case the query runs much faster with nestloop with a
parameterized inner path, since test_foo is small and test_bar is very
large, and there is an index on test_bar.foo_id. You can see that by
"set enable_mergejoin to off":
# EXPLAIN (costs off)
SELECT SUM(
test_foo.id) FROM test_bar, test_foo WHERE test_bar.foo_id =
test_foo.id AND test_foo.active AND test_bar.active;
QUERY PLAN
--------------------------------------------------------------
Aggregate
-> Nested Loop
-> Seq Scan on test_foo
Filter: active
-> Index Scan using test_bar_foo_id_idx on test_bar
Index Cond: (foo_id =
test_foo.id)
Filter: active
(7 rows)
In my box the total cost and execution time of mergejoin vs nestloop
are:
mergejoin nestloop
Cost estimate 1458.40 12355.15
Actual (best of 3) 3644.685 ms 13.114 ms
So it seems we have holes in cost estimate for mergejoin or nestloop
with parameterized inner path, or both.
Thanks
Richard