Re: PostgreSQL and VIEWS

hm...

I have provided examples? Tables definitions and plan for each query? (in
another thread..)

I am not sure I can buy it that that are *very* different queries.... I
would say - they are the same - why would you need to evalute 100 rows and
reduce end result on one?

execute function - is most expensive step... most expensive step - I
would do on the end - not on the beginning... after i applied all filters -
of course if my function is not part of the filter - if it is - then it is
something ... unavoidable - and must be executed on all rows...)

And, even I would do it - just if it is needed i.e.

on:

SELECT stuff FROM (select immutable_func(), stuff FROM big_table) q

I would never execute the function - even it is immutable... how i
understand it - immutable function has just advantage that it could be
executed just once - instead of number of rows times - even you want all
rows... but if it is not in top query - who cares...why to execute it at
all...

I mean - I don't know - maybe it is "by design" - but is there some
(hidden) reason why you must execute volatile function on all rows - not
just after filter - number of filtered rows times?

P.S. I took volatile function as potentially worst possible scenario...

Though I dont think it is true...

Because of :

SELECT * FROM view_with_volatile_function WHERE indexed_column = 5 - uses
index...

but

SELECT * FROM view_with_volatile_function INNER JOIN (SELECT 5 AS
indexed_column) q USING (indexed_column) - does not!

Logically - that are the same queries...

Thanks,

Misa

2013/3/26 Merlin Moncure <mmoncure(at)gmail(dot)com>

> 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
>