Re: From with case

2013/3/26 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> 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

just one note - it works on 9.1. well - my mistake - tested on
different server with different client_min_messages.

Regards

Pavel