Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id B5B2764FD07 for ; Wed, 30 Jul 2008 23:45:29 -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 41055-04 for ; Wed, 30 Jul 2008 23:45:18 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from ciao.gmane.org (main.gmane.org [80.91.229.2]) by postgresql.org (Postfix) with ESMTP id 0E3FE64FD05 for ; Wed, 30 Jul 2008 23:45:24 -0300 (ADT) Received: from list by ciao.gmane.org with local (Exim 4.43) id 1KOOAK-00080b-7A for pgsql-performance@postgresql.org; Thu, 31 Jul 2008 02:45:20 +0000 Received: from apn-77-113-78-203.gprs.plus.pl ([77.113.78.203]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Thu, 31 Jul 2008 02:45:20 +0000 Received: from public by apn-77-113-78-203.gprs.plus.pl with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Thu, 31 Jul 2008 02:45:20 +0000 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-performance@postgresql.org From: Miernik Subject: why "WHERE uid NOT IN" is so slow, and EXCEPT in the same situtation is fast? Date: Thu, 31 Jul 2008 04:45:08 +0200 Lines: 42 Message-ID: <20080731024508.53F6.0.NOFFLE@turbacz.local> Reply-To: Miernik X-Complaints-To: usenet@ger.gmane.org X-Gmane-NNTP-Posting-Host: apn-77-113-78-203.gprs.plus.pl X-Archive: encrypt=none User-Agent: tin/1.9.3-20080506 ("Dalintober") (UNIX) (Linux/2.6.25-2-686 (i686)) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200807/264 X-Sequence-Number: 30864 Two queries which do the same thing, first one takes ages to complete (did wait several minutes and cancelled it), while the second one took 9 seconds? Don't they do the same thing? miernik=> EXPLAIN SELECT uid FROM locks WHERE uid NOT IN (SELECT uid FROM locks INNER JOIN wys USING (uid, login)); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------ Seq Scan on locks (cost=38341.39..61365389.71 rows=48446 width=4) Filter: (NOT (subplan)) SubPlan -> Materialize (cost=38341.39..39408.47 rows=79508 width=4) -> Hash Join (cost=3997.27..37989.89 rows=79508 width=4) Hash Cond: (((wys.uid)::integer = (locks.uid)::integer) AND ((wys.login)::text = (locks.login)::text)) -> Seq Scan on wys (cost=0.00..13866.51 rows=633451 width=16) -> Hash (cost=2069.91..2069.91 rows=96891 width=16) -> Seq Scan on locks (cost=0.00..2069.91 rows=96891 width=16) (9 rows) Time: 231,634 ms miernik=> EXPLAIN SELECT uid FROM locks EXCEPT (SELECT uid FROM locks INNER JOIN wys USING (uid, login)); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------- SetOp Except (cost=59306.12..60188.11 rows=17640 width=4) -> Sort (cost=59306.12..59747.12 rows=176399 width=4) Sort Key: "*SELECT* 1".uid -> Append (cost=0.00..41823.79 rows=176399 width=4) -> Subquery Scan "*SELECT* 1" (cost=0.00..3038.82 rows=96891 width=4) -> Seq Scan on locks (cost=0.00..2069.91 rows=96891 width=4) -> Subquery Scan "*SELECT* 2" (cost=3997.27..38784.97 rows=79508 width=4) -> Hash Join (cost=3997.27..37989.89 rows=79508 width=4) Hash Cond: (((wys.uid)::integer = (locks.uid)::integer) AND ((wys.login)::text = (locks.login)::text)) -> Seq Scan on wys (cost=0.00..13866.51 rows=633451 width=16) -> Hash (cost=2069.91..2069.91 rows=96891 width=16) -> Seq Scan on locks (cost=0.00..2069.91 rows=96891 width=16) (12 rows) Time: 1479,238 ms miernik=> -- Miernik http://miernik.name/