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 ? What part of the query makes recursive calls ?2. What would be the recommended approach to make such queries ? I tried putting the pairs in a temporary table and then joined it against the view to get the relevant tuples, which seemed to work.
Thanks,Aditya
> From: tgl(at)sss(dot)pgh(dot)pa(dot)us
> To: adirastogi(at)outlook(dot)com
> CC: pgsql-novice(at)postgresql(dot)org
> Subject: Re: [NOVICE] getting ERROR: stack depth limit exceeded on a WHERE IN query on a view
> Date: Mon, 24 Feb 2014 11:40:06 -0500
>
> Aditya Rastogi <adirastogi(at)outlook(dot)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 the stack 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
>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice