Re: Weird, bad 0.5% selectivity estimate for a column equal to itself

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: Weird, bad 0.5% selectivity estimate for a column equal to itself
Дата
Msg-id 22653.1372210907@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Re: Weird, bad 0.5% selectivity estimate for a column equal to itself  (Josh Berkus <josh@agliodbs.com>)
Список pgsql-performance
Josh Berkus <josh@agliodbs.com> writes:
>> Personally, I'll bet lunch that that external software is outright
>> broken, ie it probably thinks "X = X" is constant true and they found
>> they could save two lines of code and a few machine cycles by emitting
>> that rather than not emitting anything.

> Well, it was more in the form of:
> tab1.x = COALESCE(tab2.y,tab1.x)

Hm.  I'm not following how you get from there to complaining about not
being smart about X = X, because that surely ain't the same.

> Well, I'd be more satisfied with having a solution for:
> WHERE tab1.x = tab1.y
> ... in general, even if it didn't have correlation stats.  Like, what's
> preventing us from using the same selectivity logic we would on a join
> for that?

It's a totally different case.  In the join case you expect that each
element of one table will be compared with each element of the other.
In the single-table case, that's exactly *not* what will happen, and
I don't see how you get to anything very useful without knowing
something about the value pairs that actually occur.  As a concrete
example, applying the join selectivity logic would certainly give a
completely wrong answer for X = X, unless there were only one value
occurring in the column.

            regards, tom lane


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

Предыдущее
От: Ben
Дата:
Сообщение: Re: incorrect row estimates for primary key join
Следующее
От: Willy-Bas Loos
Дата:
Сообщение: seqscan for 100 out of 3M rows, index present