Lets say for a second that you manage to trick it into using index scan,
and then you actually call the function with one of the values that
returns 1,000s of rows. Probably it will take 10-100 times longer than
if it used a seq scan.
I don't know if it matters (I suspect that it does) but I am using
LIMIT 1 in the sub-query/stored function. All I need is one single
row meeting any of the criteria laid out in the stored procedure to
establish an offer_id is "pending".
So, in your case if you LIMIT the index scan will always be fast, and the
seq scan will be catastrophic, because you don't need to retrieve all the
rows, but just one. (IMHO the planner screws these LIMIT clauses becauses
it expects the data to be randomly distributed in the first page while in
real life it's not).
You could use EXIST to test the existence of a subquery (after all, thats
its purpose), or you could :
When SELECT ... FROM table WHERE stuff=value LIMIT 1
obstinately uses a seq scan, spray a little order by :
When SELECT ... FROM table WHERE stuff=value ORDER BY stuff LIMIT 1
the ORDER BY will make the planner think "I could use the index to
order"...
