Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id A14169FC01D for ; Thu, 12 Jul 2007 12:35:03 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 96385-03 for ; Thu, 12 Jul 2007 12:34:56 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id C892E9FC017 for ; Thu, 12 Jul 2007 12:34:59 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l6CFYiCS018506; Thu, 12 Jul 2007 11:34:44 -0400 (EDT) To: "Pavel Stehule" cc: Ragnar , "Ashish Karalkar" , pgsql-sql@postgresql.org Subject: Re: Using case or if to return multiple rows In-reply-to: <162867790707120425p42e6de45k1342107418c80498@mail.gmail.com> References: <005401c7c450$3d6670e0$170211ac@LIONKING.COM> <1184233928.5778.117.camel@localhost.localdomain> <162867790707120425p42e6de45k1342107418c80498@mail.gmail.com> Comments: In-reply-to "Pavel Stehule" message dated "Thu, 12 Jul 2007 13:25:55 +0200" Date: Thu, 12 Jul 2007 11:34:44 -0400 Message-ID: <18505.1184254484@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200707/65 X-Sequence-Number: 28800 "Pavel Stehule" writes: > it's possible with one big disadvantage. This query will do seq scan > both tables and it can be slow on big tables. No, it should be reasonably OK, because if the added condition doesn't involve the tables being scanned it'll be turned into a one-time filter. As an example using the regression database: regression=# explain select * from tenk1 where 1 in (select f1 from int4_tbl) union all select * from tenk1 where 1 not in (select f1 from int4_tbl); QUERY PLAN ------------------------------------------------------------------------- Append (cost=1.06..1118.13 rows=20000 width=244) -> Result (cost=1.06..459.06 rows=10000 width=244) One-Time Filter: (hashed subplan) -> Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244) SubPlan -> Seq Scan on int4_tbl (cost=0.00..1.05 rows=5 width=4) -> Result (cost=1.06..459.06 rows=10000 width=244) One-Time Filter: (NOT (hashed subplan)) -> Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244) SubPlan -> Seq Scan on int4_tbl (cost=0.00..1.05 rows=5 width=4) (11 rows) The probe into int4_tbl will happen twice (but not more than that) and whichever tenk1 scan gets discarded won't happen at all. So unless the test condition itself is horribly expensive this should work as well as you could expect. regards, tom lane