Hello 2013/3/26 Ben Morrow <b...@morrow.me.uk>: > Quoth pavel.steh...@gmail.com (Pavel Stehule): >> Dne 25.3.2013 23:51 "Ben Morrow" <b...@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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql