On Wed, Mar 11, 2015 at 10:01 AM, atxcanadian <matthew.boyda@xxxxxxxxx> wrote:
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?
Cheers,
Jeff
