Re: From with case - Mailing list pgsql-sql

Hello

2013/3/26 Ben Morrow <ben@morrow.me.uk>:
> Quoth pavel.stehule@gmail.com (Pavel Stehule):
>> Dne 25.3.2013 23:51 "Ben Morrow" <ben@morrow.me.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 voidLANGUAGE 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