Re: From with case

Hello

2013/3/26 Ben Morrow <ben(at)morrow(dot)me(dot)uk>:
> Quoth pavel(dot)stehule(at)gmail(dot)com (Pavel Stehule):
>> Dne 25.3.2013 23:51 "Ben Morrow" <ben(at)morrow(dot)me(dot)uk> napsal(a):
>> >
>> > I would use a view for this:
>> >
>> > create view vale_any as
>> > select 'P'::text "type", v.adiant, v.desc_per, v.cod
>> > from valepag v
>> > union all
>> > select 'R', v.adiant, v.desc_per, v.cod
>> > from valerec v;
>> >
>> > then
>> >
>> > for rSql in
>> > select a.adiant, a.desc_per
>> > from vale_any a
>> > where a.type = cTip and a.cod = 2
>> > loop
>>
>> This design has a performance problem. You read both tables everywhere -
>> for large tables can be bad
>
> You would think so, but, in general, Pg is cleverer than that. For the
> simple case of queries with constants in (so, a client-submitted query
> like
>
> select * from vale_any a where a.type = 'P' and a.cod = 2
>
> or the equivalent with bound placeholders) the planner won't even plan
> the parts of the view which don't get used. Try some experiments with
> EXPLAIN to see what I mean: the unused sections of the Append (that is,
> the UNION ALL) are either omitted entirely or get replaced with
>
> Result
> One-Time Filter: false
>
> (I'm not entirely sure what makes the difference, though it seems to be
> to do with how complicated the individual parts of the UNION are).
>
> PL/pgSQL is a bit more complicated, because (unless you use EXECUTE) it
> pre-plans all its statements, so the condition on a.type is not constant
> at planning time. However, if you PREPARE a statement like
>
> prepare v as select * from vale_any a
> where a.type = $1 and a.cod = $2
>
> and then run it with EXPLAIN ANALYZE EXECUTE v ('P', 2) you will see
> that although the plan includes the parts of the view that don't get
> used they are all marked '(never executed)' by EXPLAIN ANALYZE, because
> the executor had enough information to work out they could never return
> any rows. Skipping those parts of the plan at execute time does have a
> small cost--for small tables you will see the total query time go up a
> little for a prepared statement--but nothing like the cost of scanning a
> large table. I would expect it's about the same as the cost of a
> PL/pgSQL IF/THEN/ELSE.
>
> It's worth noting at this point that if you know the rows of a UNION
> will be distinct it's worth making it a UNION ALL, since otherwise Pg
> has to add a sort-and-uniq step which can be expensive.
>
> Ben
>

you have a true

CREATE OR REPLACE FUNCTION public.fo1(alfa integer)
RETURNS void
LANGUAGE plpgsql
AS $function$
declare r record;
begin
for r in explain
select * FROM (
select 1::int as filter, * from f1
union all
select 2 as filter, * from f2) x
where x.filter = alfa
loop
raise notice '%', r;
end loop;
end;
$function$

postgres=# select fo1(1);
NOTICE: ("Append (cost=0.00..34.00 rows=2400 width=8)")
NOTICE: (" -> Seq Scan on f1 (cost=0.00..34.00 rows=2400 width=8)")
fo1
-----

(1 row)

postgres=# select fo1(2);
NOTICE: ("Append (cost=0.00..34.00 rows=2400 width=8)")
NOTICE: (" -> Seq Scan on f2 (cost=0.00..34.00 rows=2400 width=8)")
fo1
-----

(1 row)

In this case is postgres smart enough (Postgres 9.3)

Is strange, so this example doesn't work on 9.1. - PREPARE and EXECUTE
works with "one time filter", but plpgsql code doesn't work - it
returns nothing

Regards

Pavel Stehule