Re: How to get explain plan to prefer Hash Join

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: How to get explain plan to prefer Hash Join
Дата
Msg-id CAMkU=1x9X5wnWaH_f_+Yrkt7aSgWojnn-XSbRywy_1PQK5GPgw@mail.gmail.com
обсуждение исходный текст
Ответ на How to get explain plan to prefer Hash Join  (atxcanadian <matthew.boyda@gmail.com>)
Ответы Re: How to get explain plan to prefer Hash Join  (atxcanadian <matthew.boyda@gmail.com>)
Список pgsql-performance
Дерево обсуждения
On Wed, Mar 11, 2015 at 10:01 AM, atxcanadian <matthew.boyda@gmail.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

В списке pgsql-performance по дате отправления:

Предыдущее
От: "Nicholson, Brad (Toronto, ON, CA)"
Дата:
Сообщение: Re: How to get explain plan to prefer Hash Join
Следующее
От: atxcanadian
Дата:
Сообщение: Re: How to get explain plan to prefer Hash Join