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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Reply via email to