Re: From with case - Mailing list pgsql-sql

2013/3/26 Pavel Stehule <pavel.stehule@gmail.com>:
> 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 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