Re: views much slower in 9.3 than 8.4

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: views much slower in 9.3 than 8.4
Дата
Msg-id 15849.1427730729@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Re: views much slower in 9.3 than 8.4  (Kevin Grittner <kgrittn@ymail.com>)
Ответы Re: views much slower in 9.3 than 8.4  (Jim Nasby <Jim.Nasby@BlueTreble.com>)
Список pgsql-performance
Kevin Grittner <kgrittn@ymail.com> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> But the other problem is that the planner considers less-than-1%
>> differences in cost estimates to be "in the noise", which means
>> that it's not going to consider cost differences of less than
>> 1480 units in the remaining join steps to be significant.  This
>> is how come we end up with the apparently brain-dead decisions to
>> use seqscans on some of the other tables such as "pi" and "ac":
>> comparing the seqscan to a potential inner indexscan, the total
>> cost of the join is "the same" according to the 1% rule,

> The 1% rule itself might be something to add to the R&D list.

Perhaps.  But it does make for a significant difference in planner speed,
and I would argue that any case where it really hurts is by definition
a cost estimation failure somewhere else.

> [ disable_cost skews the behavior pretty badly ]

True.  Your example suggests that it might be nice to have something other
than a cost-delta way of discriminating against seqscans.  In principle
this consideration could be added to add_path(), although I'm pretty
hesitant to make that function even more complex/slower.

Perhaps another way would be to generate seqscan paths last (I think
they're first at the moment), and only generate them if we didn't find
any other path for the rel.

Nestloops for join rels have the same issue and would need to be handled
similarly, whatever solution we pick.

            regards, tom lane


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

Предыдущее
От: Kevin Grittner
Дата:
Сообщение: Re: views much slower in 9.3 than 8.4
Следующее
От: "Kevin Viraud"
Дата:
Сообщение: Weird CASE WHEN behaviour causing query to be suddenly very slow