Gaetano Mendola wrote:
Tom Lane wrote:
joins. Is that the case?
Like:
SELECT * FROM ta JOIN tb USING (id) where tb.foo = 4;
If ta is a view with some calculated fields are the function on ta
evaluated only for record matching the filters or in some case (
like a full scan on ta ) also for the records discarded due to the join?
Tom Lane wrote:
Gaetano Mendola <mendola@bigfoot.com> writes:
number of times volatile functions get evaluated.
I suspect that functions are evaluated also for record discarded due to[ 8.2 evaluates volatile functions in the targetlist of a view ]
If I mark the function as STABLE or IMMUTABLE then even with version
8.2 the function is not evaluated. Is this the intended behavior?
Yes; people complained that we needed to be more careful about theIf I mark the function as STABLE or IMMUTABLE then even with version
8.2 the function is not evaluated. Is this the intended behavior?
number of times volatile functions get evaluated.
joins. Is that the case?
Like:
SELECT * FROM ta JOIN tb USING (id) where tb.foo = 4;
If ta is a view with some calculated fields are the function on ta
evaluated only for record matching the filters or in some case (
like a full scan on ta ) also for the records discarded due to the join?
kalman=# create table ta ( a integer, b integer );
CREATE TABLE
kalman=# create table tb ( b integer, c integer );
CREATE TABLE
kalman=#
kalman=# CREATE OR REPLACE FUNCTION sp_test ( INTEGER )
kalman-# RETURNS INTEGER AS'
kalman'# DECLARE
kalman'# a_id ALIAS FOR $1;
kalman'# BEGIN
kalman'# RAISE NOTICE ''here'';
kalman'#
kalman'# return 3;
kalman'# END;
kalman'# ' LANGUAGE 'plpgsql';
CREATE FUNCTION
kalman=#
kalman=# CREATE OR REPLACE VIEW v_ta AS
kalman-# SELECT
kalman-# sp_test(a) AS a,
kalman-# b AS b
kalman-# FROM
kalman-# ta c
kalman-# ;
CREATE VIEW
kalman=#
kalman=# insert into ta values (2,3);
INSERT 0 1
kalman=# insert into ta values (3,4);
INSERT 0 1
kalman=# insert into tb values (4,5);
INSERT 0 1
kalman=#
kalman=# select * from v_ta join tb using (b) where c = 5;
NOTICE: here
NOTICE: here
b | a | c
- ---+---+---
4 | 3 | 5
(1 row)
Is really this what we want? I did a migration 8.0.x => 8.2.3 and I had on first hour of service up
lot of queries "blocked" due to this, consider in my case I have on v_ta milions of records and usually
that join extracts 1 row. Is there a way to set till I don't check all my huge schema to disable this
behaviour?
Regards
Gaetano Mendola
