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 <mmonc...@gmail.com> > On Mon, Mar 25, 2013 at 4:32 PM, Misa Simic <misa.si...@gmail.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 >
