Re: How to get explain plan to prefer Hash Join

On Wed, Mar 11, 2015 at 10:01 AM, atxcanadian <matthew(dot)boyda(at)gmail(dot)com>
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