not using index through procedure

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

Responses

Browse pgsql-general by date

  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.