Обсуждение: View efficiency questions
Dear Experts, I have a couple of questions about the efficiency of queries involving views. Say I have a large table T, and a view V that just adds some extra columns to T, using for example some date-to-text formatting functions. The functions are defined as immutable. Now I "select * from V where pkey=xxxxx". My hope was that the "where" filter would run on the table T and the functions would only run on the single row that is returned. Instead it looks as if the functions are applied to every row, i.e. V is completely built, and then the one row is selected. (In contrast, if I don't use a view but put the functions in the select, I think that they are run only for the selected row.) Is this the expected behaviour? I can supply a more detailed example if it would help. The second case is similar though a little more complex. This time, rather than immutable functions adding extra columns in the view, it is joins. For example, T might have codes which are expanded to human-readable descriptions by joining with a code-to-description table. Again I select a single row using T's primary key, and hope that the code-to-description is only done for that one row, but instead it seems to be done for every row. Thoughts, anyone? Regards, --Phil Endecott.
Phil Endecott <spam_from_postgresql_general@chezphil.org> writes: > Instead it looks as if the functions are applied to every row, i.e. V is > completely built, and then the one row is selected. This would depend very largely on the details of V and on the details of the calling query ... not to mention which Postgres version you are using. In general I would not expect a trivial case to act that way (see attached counterexample), so I suspect you haven't told us everything. Give us a concrete example, please ... regards, tom lane regression=# create function f1(int) returns int language plpgsql as ' regression'# begin regression'# raise notice ''f1: %'', $1; regression'# return $1; regression'# end'; CREATE FUNCTION regression=# select * from int4_tbl; f1 ------------- 0 123456 -123456 2147483647 -2147483647 (5 rows) regression=# create view v1 as select *,f1(f1) as func from int4_tbl; CREATE VIEW regression=# select * from v1; NOTICE: f1: 0 NOTICE: f1: 123456 NOTICE: f1: -123456 NOTICE: f1: 2147483647 NOTICE: f1: -2147483647 f1 | func -------------+------------- 0 | 0 123456 | 123456 -123456 | -123456 2147483647 | 2147483647 -2147483647 | -2147483647 (5 rows) regression=# select * from v1 where f1 = 0; NOTICE: f1: 0 f1 | func ----+------ 0 | 0 (1 row) regression=#
Hi Tom, Thanks for writing. On Saturday I had a query that took 3000 ms but reduced to only 1200 ms when I took out the date formatting functions from the views. Now it takes only 700 ms with or without the functions. Perhaps an ANALYZE in the meantime has changed everything. Anyway, thanks for your example. The "raise notice" is a good debugging idea. I will post again if the problem comes back and I can construct a repeatable test case. Regards, --Phil.