Message-ID: <48688B44.6070407@gmx.net>
Date: Mon, 30 Jun 2008 09:29:08 +0200
From: Ulrich <ulrich.mierendorff@gmx.net>
User-Agent: Thunderbird 2.0.0.14 (X11/20080505)
MIME-Version: 1.0
To: Rusty Conover <rconover@infogears.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Subquery WHERE IN or WHERE EXISTS faster?
References: <48665741.2060900@gmx.net> <29009.1214668423@sss.pgh.pa.us>
	<4866B624.1090906@gmx.net>
	<B7AEEA21-79C7-481D-BB7B-899002CC2174@infogears.com>
In-Reply-To: <B7AEEA21-79C7-481D-BB7B-899002CC2174@infogears.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

I think it will be fast, because the "IN set", which is the result of 
"SELECT processorid FROM users_processors WHERE userid=4040", is limited 
to a maximum of ~500 processors which is not very big. Increasing 
Postgres' RAM would be difficult for me, because I am only running a 
very small server with 256MB RAM and the webserver also likes to use 
some RAM.

Does Postgre cache the HASH-Table for later use? For example when the 
user reloads the website.

Kind regards
Ulrich

Rusty Conover wrote:
> This is what I've found with tables ranging in the millions of rows.
>
> Using IN is better when you've got lots of rows to check against the 
> IN set and the IN set may be large and possibly complicated to 
> retrieve (i.e. lots of joins, or expensive functions).
>
> Postgres will normally build a hash table of the IN set and just 
> search that hash table. It's especially fast if the entire hash table 
> that is built can fit into RAM.  The cpu/io cost of building the IN 
> set can be quite large because it needs to fetch every tuple to hash 
> it, but this can be faster then searching tuple by tuple through 
> possibly many indexes and tables like EXISTS does.  I like to increase 
> work_mem a lot (512mb and up) if I know I'm going to be doing a lot of 
> matches against a large IN set of rows because I'd prefer for that 
> hash table to never to be written to disk.
>
> EXISTS is better when you're doing fewer matches because it will pull 
> the rows out one at a time from its query possibly using indexes, its 
> main advantage is that it doesn't pull all of the tuples before it 
> starts processing matches.
>
> So in summary both are good to know how to use, but choosing which one 
> to use can really depend on your data set and resources.
>
> Cheers,
>
> Rusty
> -- 
> Rusty Conover
> InfoGears Inc.
> http://www.infogears.com
>