Re: PostgreSQL and VIEWS

On Mon, Mar 25, 2013 at 4:32 PM, Misa Simic <misa(dot)simic(at)gmail(dot)com> wrote:
> Thanks Merlin,
>
> Well... sorry, It could be and my bad english... but let me explain
> chronologicaly things...
>
> I have first written concrete case...
>
> http://postgresql.1045698.n5.nabble.com/PostgreSQL-planner-tp5749427.html
>
> But because of I recognized the pattern - always is problem with JOIN to a
> view...
>
> I have written this abroad generic question.... Because of, I think,
> Postgres have problem with JOIN to a view in general...So probably someone
> before me have had the same problem - and if that is the case I just wanted
> to hear thier solution...
>
> But from others examples, and some tests EXPLAIN ANALYZE I have done...
>
> i.e. SELECT t1.a FROM t1 LEFTJOIN t2 USING (a)
>
> Planer includes some actions related to t2 - what are not necessary at
> all... again - it is just my opinion :)
> (Please, don't take this - I don't know... as some most important thing...)
>
> So that are "small" problems - on our simplified examples - what have big
> impact in performance on a bit complex examples...
>
> So what we have indentified until know - solution to our problem with views
> - is always: "rephrase the question" (not indexes - they exist - just not
> used...)
>
> for example:
>
> SELECT view.* FROM view INNER JOIN t1 USING (col1) WHERE t1.col2 = 1
>
> to get better performance, you need to say:
>
> SELECT view.* FROM view WHERE col1 = (SELECT t.col1 FROM t1 WHERE t1.col2 =
> 1)

yeah. I understand -- it would help to see a test case there. the
devil is always in the details. point being, let's take your other
example

or the supplied test case you mentioned (where you evaluate a volatile
function in a view), things are working as designed. the only
difference between a view and a regular query is you get pushed down
one level in terms if subquery. so,

select * from view;

is the same as:

select * from (<the view query>) q;

so, when using volatile function, the case basically boils down to:

SELECT * FROM (select volatile_func(), stuff FROM big_table) q WHERE
key = value;

that's a *very* different query vs:
select volatile_func(), stuff FROM big_table WHERE key = value;

the slower performance there is because logically you *have* to
evaluate volatile performance first -- things are working as designed.

merlin