From: Ulrich <ulrich.mierendorff@gmx.net>
To: Rusty Conover <rconover@infogears.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Subquery WHERE IN or WHERE EXISTS faster?
Date: Mon, 30 Jun 2008 09:29:08 +0200
Message-ID: <48688B44.6070407@gmx.net> (raw)
In-Reply-To: <B7AEEA21-79C7-481D-BB7B-899002CC2174@infogears.com>
References: <48665741.2060900@gmx.net>
<29009.1214668423@sss.pgh.pa.us>
<4866B624.1090906@gmx.net>
<B7AEEA21-79C7-481D-BB7B-899002CC2174@infogears.com>
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
>
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: ulrich.mierendorff@gmx.net, rconover@infogears.com
Subject: Re: Subquery WHERE IN or WHERE EXISTS faster?
In-Reply-To: <48688B44.6070407@gmx.net>
* 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