Re: Subquery flattening causing sequential scan

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: Subquery flattening causing sequential scan
Дата
Msg-id 12106.1325009566@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Subquery flattening causing sequential scan  (Jim Crate <jimcfl@gmail.com>)
Ответы Re: Subquery flattening causing sequential scan  (Ondrej Ivanič <ondrej.ivanic@gmail.com>)
Re: Subquery flattening causing sequential scan  (Віталій Тимчишин <tivv00@gmail.com>)
Re: Subquery flattening causing sequential scan  (Jim Crate <jimcfl@gmail.com>)
Список pgsql-performance
Jim Crate <jimcfl@gmail.com> writes:
> My question is why does it do a seq scan when it flattens this
> subquery into a JOIN?

Because it thinks there will be 3783 rows out of the msg scan, which if
true would make your desired nestloop join a serious loser.  You need to
see about getting that estimate to be off by less than three orders of
magnitude.  Possibly raising the stats target on emsg_messages would
help.  I'd also try converting the inner NOT IN into a NOT EXISTS, just
to see if that makes the estimate any better.  Using something newer
than 9.0.2 might help too, as we fixed some outer-join estimation bugs a
few months ago.

            regards, tom lane

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

Предыдущее
От: Jim Crate
Дата:
Сообщение: Subquery flattening causing sequential scan
Следующее
От: "Carlo Stonebanks"
Дата:
Сообщение: Performance costs of various PL languages