Re: not using index through procedure

One's marked VOLATILE, the other is marked IMMUTABLE. This affects
whether it's considered a constant, the planner estimates and hence
whether it uses the index.

On Thu, Oct 14, 2004 at 05:30:58PM +0200, Robin Ericsson wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.