Re: getting ERROR: stack depth limit exceeded on a WHERE IN query on a view - Mailing list pgsql-novice

Aditya Rastogi <adirastogi@outlook.com> writes:
> The query is similar to the following query:
>        select count(*) from gui_die_summary where (x_coord, y_coord) in
((25,5),(41,13),(25,7),(28,3),(25,8),(34,7),(26,6),(21,10));, 
> only that the list of pairs specified in the in clause is pretty large - around 5000-4000 pairs and that's when I get
thestack depth limit exceed error. 

Even without the stack depth issue, that would perform pretty horridly for
so many pairs.

Do you know that the pairs are all distinct, so that you don't really need
the duplicate-elimination behavior of IN?  If so, you could recast this
like so:

select count(*) from
  gui_die_summary,
  (values (25,5),(41,13),(25,7),(28,3),(25,8),(34,7),(26,6),(21,10)) v(vx,vy)
where (x_coord, y_coord) = (vx, vy);

which should work better for large numbers of pairs.

            regards, tom lane