Query plan good in 8.4, bad in 9.2 and better in 9.3

Поиск
Список
Период
Сортировка
От Scott Marlowe
Тема Query plan good in 8.4, bad in 9.2 and better in 9.3
Дата
Msg-id CAOR=d=1znj5LbHxNVi8j+sP4rS5EV0bS2CM4rqksL15qA4Jhyg@mail.gmail.com
обсуждение исходный текст
Ответы Re: Query plan good in 8.4, bad in 9.2 and better in 9.3
Re: Query plan good in 8.4, bad in 9.2 and better in 9.3
Список pgsql-performance
OK so we have a query that does OK in 8.4, goes to absolute crap in
9.2 and then works great in 9.3. Thing is we've spent several months
regression testing 9.2 and no time testing 9.3, so we can't just "go
to 9.3" in an afternoon. But we might have to. 9.2 seems hopelessly
broken here.

The query looks something like this:

SELECT COUNT(*) FROM u, ug
WHERE u.ugid = ug.id
AND NOT u.d
AND ug.somefield IN  (SELECT somefunction(12345));

In 8.4 we get this plan http://explain.depesz.com/s/r3hF which takes ~5ms
In 9.2 we get this plan http://explain.depesz.com/s/vM7 which takes ~10s
In 9.3 we get this plan http://explain.depesz.com/s/Wub which takes ~0.35ms

The data sets are identical, the schemas are identical. Making changes
to random_page_cost, sequential_page_cost and various other tuning
parameters don't make it any better.

PG versions: 8.4.20, 9.2.8,  9.3.4

Adding a limit to the function DOES make 9.2 better, ala:

SELECT COUNT(*) FROM u, ug
WHERE u.ugid = ug.id
AND NOT u.d
AND ug.somefield IN  (SELECT somefunction(12345) limit 199);

If the limit is 200 the bad plan shows up again.

Question, is this a known issue with 9.2? If so is it something that
will one day be fixed or are we stuck with it? Is there a workaround
to make it better? Note: I'd rather not have to compile 9.2 from
source with a patch, but at this point that would be acceptable over
"you're stuck with it".

--
To understand recursion, one must first understand recursion.


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

Предыдущее
От: changchao
Дата:
Сообщение: Re: how do functions affect query plan?
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Query plan good in 8.4, bad in 9.2 and better in 9.3