From: Tom Lane <tgl@sss.pgh.pa.us>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Ragnar <gnari@hive.is>
Cc: Ashish Karalkar <ashish.karalkar@info-spectrum.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Using case or if to return multiple rows
Date: Thu, 12 Jul 2007 11:34:44 -0400
Message-ID: <18505.1184254484@sss.pgh.pa.us> (raw)
In-Reply-To: <162867790707120425p42e6de45k1342107418c80498@mail.gmail.com>
References: <005401c7c450$3d6670e0$170211ac@LIONKING.COM>
<1184233928.5778.117.camel@localhost.localdomain>
<162867790707120425p42e6de45k1342107418c80498@mail.gmail.com>
"Pavel Stehule" <pavel.stehule@gmail.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, pavel.stehule@gmail.com, gnari@hive.is, ashish.karalkar@info-spectrum.com
Subject: Re: Using case or if to return multiple rows
In-Reply-To: <18505.1184254484@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox