Skip site navigation (1) Skip section navigation (2)

Re: Subquery WHERE IN or WHERE EXISTS faster?

From: Gregory Stark <stark(at)enterprisedb(dot)com>
To: "Ulrich" <ulrich(dot)mierendorff(at)gmx(dot)net>
Cc: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Subquery WHERE IN or WHERE EXISTS faster?
Date: 2008-06-29 12:08:57
Message-ID: 87k5g8phnq.fsf@oxford.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-performance
"Ulrich" <ulrich(dot)mierendorff(at)gmx(dot)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!

In response to

pgsql-performance by date

Next:From: Greg SmithDate: 2008-06-29 18:59:39
Subject: Re: A guide/tutorial to performance monitoring and tuning
Previous:From: UlrichDate: 2008-06-29 10:15:25
Subject: Re: Subquery WHERE IN or WHERE EXISTS faster?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group