function calls in WHERE clause
I have a view like this:
CREATE or replace VIEW program_member_this_year AS
SELECT DISTINCT person.person_id
FROM unit.person, unit.visit
WHERE
unit.person.person_id = unit.visit.person_id
AND
visit.program_id = this_program()
AND
visit.time_stamp > shared.last_september();
and I would like to speed it up ...
I notice that if I write it like this:
CREATE or replace VIEW program_member_this_year AS
SELECT DISTINCT person.person_id
FROM unit.person, unit.visit
WHERE
unit.person.person_id = unit.visit.person_id
AND
visit.program_id = 3
AND
visit.time_stamp > '2004-9-1';
It takes about 1/4 the time as with the function calls.
Both of those function calls will always return the exact
same thing for any given query.
My approach now is to make a Pl/PgSQL function which
will build up the correct query with the function calls
replaced by their constant results .... but is there a
better way?
I should note that this_program() is defined as VOLATILE.
It was a while ago, but I believe this is because I have
a this_program() defined in each schema and they
return a different value depending on which schema
you are in when you call the function...
Thanks for any insight.
_________________________________________________________________
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.
http://join.msn.com/?page=features/virus
"Lee Harr" <missive@hotmail.com> writes:
I should note that this_program() is defined as VOLATILE.
So don't do that ;-)
It was a while ago, but I believe this is because I have
a this_program() defined in each schema and they
return a different value depending on which schema
you are in when you call the function...
But each one of these is individually immutable, no? If it's not,
how are you expecting your query-builder function to give the same
answers as before?
regards, tom lane
On 2004-09-08, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Lee Harr" <missive@hotmail.com> writes:
I should note that this_program() is defined as VOLATILE.
So don't do that ;-)
I had a feeling that was the answer... Actually, that was my
first thought, but then I looked back at the definition of
this_program() it was marked volatile. No other function
is marked like that, so it must be that without that it just
was not working right.
I think the problem is that at other times, calls to this_program()
in the same transaction might have different results.
I have this giant Pl/PgSQL function that runs reports every
night. It goes through each schema (program)
calling that schema's report function, etc, etc.
It was a while ago, but I believe this is because I have
a this_program() defined in each schema and they
return a different value depending on which schema
you are in when you call the function...But each one of these is individually immutable, no? If it's not,
how are you expecting your query-builder function to give the same
answers as before?
So I guess I lied... :o(
It is immutable here where I am working now, but I am trying to
use the same function that needs to be mutable someplace
else... So either I need to just define a totally separate
this_program_immutable() function or go with my Pl/PgSQL
wrapper -- which is already written and working well.
So I guess that is that :o)
Thank you!
_________________________________________________________________
Protect your PC - get McAfee.com VirusScan Online
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
Import Notes
Resolved by subject fallback
... So either I need to just define a totally separate
this_program_immutable() function or go with my Pl/PgSQL
wrapper -- which is already written and working well.
So I guess that is that :o)
Installed my plpgsql wrapped view today and the query
that was taking 4 seconds is down to 100msec. What
a nice surprise! Really made my day...
I cannot say enough how much I appreciate all the hard
work that goes in to postgresql. This place is the best.
_________________________________________________________________
MSN 8 with e-mail virus protection service: 2 months FREE*
http://join.msn.com/?page=features/virus
Import Notes
Resolved by subject fallback