"Robin Ericsson" <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org