From: Gregory Stark <stark@enterprisedb.com>
To: Ulrich <ulrich.mierendorff@gmx.net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Subquery WHERE IN or WHERE EXISTS faster?
Date: Sun, 29 Jun 2008 13:08:57 +0100
Message-ID: <87k5g8phnq.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <486760BD.3010409@gmx.net>
References: <48665741.2060900@gmx.net>
<29009.1214668423@sss.pgh.pa.us>
<4866B624.1090906@gmx.net>
<87skuxp3kb.fsf@oxford.xeocode.com>
<486760BD.3010409@gmx.net>
"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!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: stark@enterprisedb.com, ulrich.mierendorff@gmx.net, tgl@sss.pgh.pa.us
Subject: Re: Subquery WHERE IN or WHERE EXISTS faster?
In-Reply-To: <87k5g8phnq.fsf@oxford.xeocode.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox