Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id D7005650EA5 for ; Sat, 28 Jun 2008 20:01:15 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 73763-05 for ; Sat, 28 Jun 2008 20:01:08 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from oxford.xeocode.com (87-127-95-194.no-dns-yet.enta.net [87.127.95.194]) by postgresql.org (Postfix) with ESMTP id 500F6650DBC for ; Sat, 28 Jun 2008 20:01:12 -0300 (ADT) Received: from localhost ([127.0.0.1] helo=oxford.xeocode.com) by oxford.xeocode.com with esmtp (Exim 4.69) (envelope-from ) id 1KCjPp-0006mn-7I; Sun, 29 Jun 2008 00:01:09 +0100 To: "Ulrich" Cc: "Tom Lane" , Subject: Re: Subquery WHERE IN or WHERE EXISTS faster? In-Reply-To: <4866B624.1090906@gmx.net> (Ulrich's message of "Sun\, 29 Jun 2008 00\:07\:32 +0200") User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux) X-Draft-From: ("nnimap+mail01.enterprisedb.com:INBOX.performance" 6041) References: <48665741.2060900@gmx.net> <29009.1214668423@sss.pgh.pa.us> <4866B624.1090906@gmx.net> From: Gregory Stark Organization: EnterpriseDB Date: Sun, 29 Jun 2008 00:01:08 +0100 Message-ID: <87skuxp3kb.fsf@oxford.xeocode.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200806/208 X-Sequence-Number: 30558 "Ulrich" writes: > EXPLAIN ANALYZE SELECT speed FROM processors WHERE id IN (SELECT processorid > FROM users_processors WHERE userid=4040) ORDER BY speed ASC LIMIT 10 OFFSET 1; > > Limit (cost=113.73..113.75 rows=7 width=5) (actual time=0.335..0.340 rows=10 loops=1) > -> Sort (cost=113.73..113.75 rows=8 width=5) (actual time=0.332..0.333 rows=11 loops=1) ^^ > Sort Key: processors.speed > Sort Method: quicksort Memory: 17kB > -> Nested Loop (cost=47.22..113.61 rows=8 width=5) (actual time=0.171..0.271 rows=13 loops=1) > -> HashAggregate (cost=47.22..47.30 rows=8 width=4) (actual time=0.148..0.154 rows=13 loops=1) > -> Bitmap Heap Scan on users_processors (cost=4.36..47.19 rows=12 width=4) (actual time=0.074..0.117 rows=13 loops=1) ^^ > Index Cond: (userid = 4040) > -> Index Scan using processors_pkey on processors (cost=0.00..8.28 rows=1 width=9) (actual time=0.006..0.007 rows=1 loops=13) > Index Cond: (processors.id = users_processors.processorid) It looks to me like you have some processors which appear in "users_processors" but not in "processors". I don't know your data model but that sounds like broken referential integrity to me. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support!