Thread: not using index through procedure

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

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.

Martijn van Oosterhout <kleptog@svana.org> wrote:

> 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.

Is there even a way to solve it this way via a procedure?

I've tried different approaches, with moving the ago outside the procedure,
having the procedure take timestamp as argument instead of interval, but still
no luck.

However, remove the $1 argument all together and replace that with ago('60
seconds') makes the procedure fly.


Regards,
Robin
_________________________________________________________
This mail sent using V-webmail - http://www.v-webmail.org

"Robin Ericsson" <robin.ericsson@profecta.se> writes:
> Is there even a way to solve it this way via a procedure?

If you want the range to depend on a procedure parameter then you're
back to square one: the planner has no way to know the values that
parameter will take on, and its default assumption is that too much of
the table will be scanned to make an indexscan profitable.

It's important to realize that this default assumption is not
necessarily silly.  If you do something to fake it out and force an
indexscan, you will win for short lookback intervals but pay through the
nose for longer intervals.

But having said that, there's a commonly-used trick, which is

CREATE OR REPLACE FUNCTION get_machine_status(interval) RETURNS
timestamp AS
'SELECT entered
FROM data
WHERE data.entered > now() - $1 AND data.entered <= now()
' LANGUAGE 'sql' VOLATILE;

(I'm assuming data.entered should never be greater than now(), or that
you can pick some other future time certainly larger than what you want.)
The planner still doesn't know the exact range limits involved, but it
does see that this *is* a range query rather than a one-sided
inequality, and the default selectivity guess for that is a lot smaller
than for a one-sided inequality.  It's not an absolute guarantee but you
should generally get an indexscan plan from this.

Approach B is to use an EXECUTE so that the query is actually re-planned
on every execution of the function.  If you think that the interval will
vary enough that you might sometimes want a seqscan, this is the way to
go.

            regards, tom lane