From: | Robin Ericsson <robin(dot)ericsson(at)profecta(dot)se> |
---|---|
To: | Postgres general mailing list <pgsql-general(at)postgresql(dot)org> |
Subject: | not using index through procedure |
Date: | 2004-10-14 15:30:58 |
Message-ID: | 1097767858.8849.117.camel@pylver.localhost.nu. |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
After some discussion on performance list, I guess this is back to a
general question :)
This is very simplified query of my real problem, but it should show the
way of the problems.
CREATE OR REPLACE FUNCTION ago(interval) RETURNS timestamp AS
'SELECT (now() - $1)::timestamp without time zone'
LANGUAGE 'sql' IMMUTABLE STRICT;
This query uses the index without problem.
SELECT entered
FROM data
WHERE ago('60 seconds') < data.entered;
However using this function
CREATE OR REPLACE FUNCTION get_machine_status(interval) RETURNS
timestamp AS
'SELECT entered
FROM data
WHERE ago($1) < data.entered
' LANGUAGE 'sql' VOLATILE;
and call it like this:
select * from get_machine_status('60 seconds');
makes the query not use index, I guess it some basic problem I'm having,
maybe I should make this into a view instead?
Regards,
Robin
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fuhr | 2004-10-14 15:47:49 | Re: Date format for bulk copy |
Previous Message | Thomas Hallgren | 2004-10-14 15:27:20 | Verifying a user. |