Date: Tue, 26 Mar 2013 06:22:10 +0000
From: Ben Morrow <ben@morrow.me.uk>
To: pavel.stehule@gmail.com, pgsql-sql@postgresql.org
Subject: Re: From with case
Message-ID: <20130326062206.GA84680@anubis.morrow.me.uk>
References: <5ba0a01548c7c6540b1c693cdf7e979d@sygecom.com.br>
	<20130325225034.GA73919@anubis.morrow.me.uk>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAFj8pRCzVM7waFKX+rnYmYST+Z4yL0UVwyAUs=cX4BDV1LFK4Q@mail.gmail.com>
Organization: morrow.me.uk
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql