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

Re: IN list processing performance (yet again)

From: "Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au>
To: "Dave Tenny" <tenny(at)attbi(dot)com>,"Bruno Wolff III" <bruno(at)wolff(dot)to>
Cc: "Andreas Pflug" <Andreas(dot)Pflug(at)web(dot)de>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: IN list processing performance (yet again)
Date: 2003-05-29 01:53:55
Message-ID: 0b9a01c32585$2a8e6900$6500a8c0@fhp.internal (view raw or flat)
Thread:
Lists: pgsql-performance

> Also, IN (subquery) is a known performance problem in PGSQL, at least if
the subquery is going to return > many rows.
> It's too bad, since I'm rather fond of subqueries, but I avoid them like
the plague in PostgreSQL.

You're not really using a subquery - really just a long list of integers.
Subqueries are lightning fast, so long as you conver to the EXISTS form:

SELECT * FROM tab WHERE id IN (SELECT id2 FROM tab2);

converts to:

SELECT * FROM tab WHERE EXISTS (SELECT id2 FROM tab2 WHERE id2=id);

Chris




In response to

Responses

pgsql-performance by date

Next:From: Victor YegorovDate: 2003-05-29 06:18:18
Subject: Re: >24 hour restore
Previous:From: Christopher Kings-LynneDate: 2003-05-29 01:47:53
Subject: Re: IN list processing performance (yet again)

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