To: =?KOI8-U?B?96bUwcymyiD0yc3eydvJzg==?= <tivv00@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Exists, limit and alternate plans 
In-reply-to: <AANLkTikRAHA-kTh5q65zdSQvaiAPEewMj01yf16mNPIa@mail.gmail.com> 
References: <AANLkTikRAHA-kTh5q65zdSQvaiAPEewMj01yf16mNPIa@mail.gmail.com>
Comments: In-reply-to =?KOI8-U?B?96bUwcymyiD0yc3eydvJzg==?= <tivv00@gmail.com>
	message dated "Mon, 12 Jul 2010 16:29:07 +0300"
Date: Mon, 12 Jul 2010 10:45:14 -0400
Message-ID: <15039.1278945914@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

=?KOI8-U?B?96bUwcymyiD0yc3eydvJzg==?= <tivv00@gmail.com> 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