Re: getting ERROR: stack depth limit exceeded on a WHERE IN query on a view

Aditya Rastogi <adirastogi(at)outlook(dot)com> writes:
> Thanks Tom, I'll try rewriting the query, with the distinct list of
pairs. But I am still curious to know two things and would really
appreciate if you could give me some pointers to help me understand them:
1. How does the stack depth come into play while evaluating this query ?

The IN clause is rewritten into

((((x_coord, y_coord) = (25,5) OR (x_coord, y_coord) = (...)) OR (x_coord, y_coord) = (...)) OR ...)

that is, you've got thousands of nested OR constructs, and what's failing
is parser processing of that nest.

We could possibly dodge the stack problem by flattening the output of
transformAExprIn to an N-way OR instead of a nest of binary ORs.
I've not experimented with that though. In any case, it'd just move the
performance issue someplace else --- you'd still have a situation where
each of those row equality clauses is processed separately for parsing and
planning purposes. That's intentional in case some of them are not like
the others, but in this example they are all pretty much equivalent so
you're just wasting cycles.

regards, tom lane