Re: Exists, limit and alternate plans

От: Tom Lane
Тема: Re: Exists, limit and alternate plans
Дата: ,
Msg-id: 15039.1278945914@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Exists, limit and alternate plans  (Віталій Тимчишин)
Список: pgsql-performance

=?KOI8-U?B?96bUwcymyiD0yc3eydvJzg==?= <> writes:
> So, my Qs:
> 1) Do we really have alternative plans for SubPlan that are selected at
> runtime? Wow.

Yup, see the AlternativeSubPlan stuff.

> 2) Why "Seq scan" plan is selected by default? Is it because of outer limit
> not being applied when calculating costs for subplans at runtime?

It's currently driven off the estimated rowcount for the parent plan
node --- 6831169 in your example.  The subplan cannot see that the
parent plan node will be terminated short of full execution, so it
thinks that hashing the whole investor table will be a win.
Obviously it'd be nice to improve that for cases like upper LIMITs.

> 3) Why does limit inside exists helps?

I think it defeats the applicability of the hash-the-whole-subtable
approach.

            regards, tom lane


В списке pgsql-performance по дате сообщения:

От: phb07
Дата:
Сообщение: Re: Slow query with planner row strange estimation
От: Josh Berkus
Дата:
Сообщение: Re: partition queries hitting all partitions even though check key is specified