To: "Ulrich" <ulrich.mierendorff@gmx.net>
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>,  <pgsql-performance@postgresql.org>
Subject: Re: Subquery WHERE IN or WHERE EXISTS faster?
In-Reply-To: <486760BD.3010409@gmx.net> (Ulrich's message of "Sun\,
	29 Jun 2008 12\:15\:25 +0200")
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux)
References: <48665741.2060900@gmx.net> <29009.1214668423@sss.pgh.pa.us>
	<4866B624.1090906@gmx.net> <87skuxp3kb.fsf@oxford.xeocode.com>
	<486760BD.3010409@gmx.net>
From: Gregory Stark <stark@enterprisedb.com>
Organization: EnterpriseDB
Date: Sun, 29 Jun 2008 13:08:57 +0100
Message-ID: <87k5g8phnq.fsf@oxford.xeocode.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

"Ulrich" <ulrich.mierendorff@gmx.net> writes:

> Hi,
> Yes that looks strange. But it is not possible that I have processors in
> "users_processors" which do not appear in "processors", because
> "users_processors" contains foreign keys to "processors".
>
> If I remove the LIMIT 10 OFFSET 1 the line "Sort (cost=.... rows=11.."
> disappears and the query return 13 correct processors from "processors". 

Oh, er, my bad. That makes perfect sense. The "actual" numbers can be affected
by what records are actually requested. The LIMIT prevents the records beyond
11 from ever being requested even though they exist. 

While the bitmap heap scan has to fetch all the records even though they don't
all get used, the nested loop only fetches the records as requested.

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's PostGIS support!