Re: Using case or if to return multiple rows

"Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)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