Re: [PERFORM] anti-join with small table via text/varchar cannotestimate rows correctly

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: [PERFORM] anti-join with small table via text/varchar cannotestimate rows correctly
Дата
Msg-id CAMkU=1xp4Xg-Lr_=4i-r842V3_00iFF2J3qjp-vLsW4_xao-gQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [PERFORM] anti-join with small table via text/varchar cannotestimate rows correctly  ("David G. Johnston" <david.g.johnston@gmail.com>)
Ответы Re: [PERFORM] anti-join with small table via text/varchar cannotestimate rows correctly
Список pgsql-performance
Дерево обсуждения
On Wed, Mar 1, 2017 at 2:12 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wed, Mar 1, 2017 at 3:00 PM, Stefan Andreatta <s.andreatta@synedra.com> wrote:
plain analyze     select tmp_san_1.id     from tmp_san_1       left join tmp_san_2 on tmp_san_1.text = tmp_san_2.text     where tmp_san_2.id is null;
​Does it help if you check for "tmp_san_2.text is null"?



Yes.  And if you swap it so that the left join is on the integer while IS NULL is on the text, that also gets poorly estimated.  Also, if you make both column of both tables be integers, same thing--you get bad estimates when the join condition refers to one column and the where refers to the other.  I don't know why the estimate is poor, but it is not related to the types of the columns, but rather the identities of them.

Cheers,

Jeff

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

Предыдущее
От: "David G. Johnston"
Дата:
Сообщение: Re: [PERFORM] anti-join with small table via text/varchar cannotestimate rows correctly
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: [PERFORM] anti-join with small table via text/varchar cannotestimate rows correctly