Re: not using index through procedure

"Robin Ericsson" <robin(dot)ericsson(at)profecta(dot)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