Currently seeing massive increase in performance when optimizer chooses Hash Join over Nested Loops. I achieve this by temporarily setting nested loops off. I'd like to setup some database variables where the optimizer prefers hash joins. Any suggestions?
-> Index Scan using dp_hist_gen_actual_idx2 on dp_hist_gen_actual dp (cost=0.56..2.78 rows=1 width=24) (actual time=0.020..20.012 rows=12336 loops=1)
Here it thinks it will find 1 row, but actually finds 12336. That is not conducive to good plans. Has the table been analyzed recently?
Index Cond: ((market_day >= '2015-01-01'::date) AND (market_day <= '2015-01-01'::date) AND (expiry_date IS NULL))
If you query just this one table with just these criteria, what do you get for the row estimates and actual rows, with and without the IS NULL condition?
