| 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: | Whole Thread | Raw Message | 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. |