title: Very large number of SQL UNION SELECT statements exceeds max_stack_depth
body: <p>I'm trying to develop a spatial based SQL query of all unique points within one or more of a given set of polygons. I am using PostGIS on a cloud VPS with 6 vCPU's and 16 GB of RAM. The spatial test in question is ST_Contains on the WHERE clause. The polygon set is approximately 40,000 unique geometries that constrain the 3.7 million feature point dataset.</p>
  
  <p>My problem is that when I create a query with more than 13,000 polygons (an therefore 13,000 SELECT statements) the PostGIS server responds with an <code>ERROR: stack depth limit exceeded"</code>
  <code>HINT:  Increase the configuration parameter "max_stack_depth"</code>. </p>
  
  <p><b>I want to know why and if I have options to work around it.</b></p>
  
  <p>This is part of an optimization exercise. I'm already retrieving the polygon geometries as a separate SELECT to form the required SQL query. I want to execute the query that tests the polygon set as a single SQL statement. So far I have been building a SELECT subquery for each polygon then UNION each together as a start point. When compiled, the query using only 13,000 polygons is ~28,000,000 characters which I believe is much less than the PostGIS SQL statement limit.</p>
  
  <p>I have tried smaller sizes and found that there is normal performance right up to the approximate limit. I had hit this limit earlier but after taking the advice of the error message I increased the "max_stack_depth" to approximately the size of what "ulimit -s" returns. At my current understanding, this SQL statement is not a recursive function of any kind which is what I would expect to cause the stack depth to be exceeded.</p>
  
  <p>Also from my reading around stack vs heap memory, I cannot understand why this query would overload the stack as most of the required stored data should end up in the heap. I also expect the query to execute sequentially while collecting the results but it seems like PostGIS might be running all the SELECT substatements first then tallying up the results.</p>
  
  <p>I have chosen to not try to merge the individual polygon geometries into a single polygon as they cover a very geographically diverse area (i.e not clustered into a simple mass) which I believe would drastically decrease benefits from spatial indexing.</p>
  
  <p>My current working SQL script follows the pattern (trimmed to fit on this post):</p>
  
  <pre><code>SELECT * FROM point_table WHERE ST_Contains("poly1_geom_str", pt_geom_col)
  UNION
  SELECT * FROM point_table WHERE ST_Contains("poly2_geom_str", pt_geom_col)
  UNION
  ....
  SELECT * FROM point_table WHERE ST_Contains("polyN_geom_str", pt_geom_col);</code></pre>
  
  <p>Is my strategy for constructing this SQL statement unlikely to be able to be resolved? Is there an alternative strategy I can try that would avoid the recursion problem?</p>