X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 46DA6474E5C for ; Sun, 23 Feb 2003 21:29:04 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 68077-04 for ; Sun, 23 Feb 2003 21:28:53 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 59507474E53 for ; Sun, 23 Feb 2003 21:28:53 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h1O2Ss5u021155; Sun, 23 Feb 2003 21:28:57 -0500 (EST) To: Josh Berkus Cc: Ryan Bradetich , Oleg Lebedev , "pgsql-performance@postgresql.org" Subject: Re: slow query In-reply-to: <200302231347.58960.josh@agliodbs.com> References: <993DBE5B4D02194382EC8DF8554A5273033583@postoffice.waterford.org> <200302231252.30681.josh@agliodbs.com> <1046034334.13010.3.camel@beavis.ybsoft.com> <200302231347.58960.josh@agliodbs.com> Comments: In-reply-to Josh Berkus message dated "Sun, 23 Feb 2003 13:47:58 -0800" Date: Sun, 23 Feb 2003 21:28:54 -0500 Message-ID: <21154.1046053734@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/169 X-Sequence-Number: 1225 Josh Berkus writes: >> I am assuming you said this because EXISTS is faster for > 12 rows? > That's my rule of thumb, *NOT* any kind of relational-calculus-based truth. Keep in mind also that the tradeoffs will change quite a lot when PG 7.4 hits the streets, because the optimizer has gotten a lot smarter about how to handle IN, but no smarter about EXISTS. Here's one rather silly example using CVS tip: regression=# explain analyze select * from tenk1 a where regression-# unique1 in (select hundred from tenk1 b); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Merge Join (cost=486.32..504.11 rows=100 width=248) (actual time=453.19..468.86 rows=100 loops=1) Merge Cond: ("outer".unique1 = "inner".hundred) -> Index Scan using tenk1_unique1 on tenk1 a (cost=0.00..1571.87 rows=10000 width=244) (actual time=0.12..5.25 rows=101 loops=1) -> Sort (cost=486.32..486.57 rows=100 width=4) (actual time=452.91..453.83 rows=100 loops=1) Sort Key: b.hundred -> HashAggregate (cost=483.00..483.00 rows=100 width=4) (actual time=447.59..449.80 rows=100 loops=1) -> Seq Scan on tenk1 b (cost=0.00..458.00 rows=10000 width=4) (actual time=0.06..276.47 rows=10000 loops=1) Total runtime: 472.06 msec (8 rows) regression=# explain analyze select * from tenk1 a where regression-# exists (select 1 from tenk1 b where b.hundred = a.unique1); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------- Seq Scan on tenk1 a (cost=0.00..35889.66 rows=5000 width=244) (actual time=3.69..1591.78 rows=100 loops=1) Filter: (subplan) SubPlan -> Index Scan using tenk1_hundred on tenk1 b (cost=0.00..354.32 rows=100 width=0) (actual time=0.10..0.10 rows=0 loops=10000) Index Cond: (hundred = $0) Total runtime: 1593.88 msec (6 rows) The EXISTS case takes about the same time in 7.3, but the IN case is off the charts (I got bored of waiting after 25 minutes...) regards, tom lane