On Mon, Mar 25, 2013 at 4:32 PM, Misa Simic <misa.simic@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
