'too many range table entries' error with partitioned tables andaggregations

Hi all,

Recently we hit a strange 'too many range table entries' error for some particular queries in our database. Some googling revealed that it's an uncommon error usually occurring due to strange database design [1]. However, I feel like the case in which it occurs this time, although uncommon, doesn't look that weird in itself. I've produced a simple example that reproduces on both PG11 and PG12 for me. It involves creating a partitioned table and then selecting from it from within a subquery and a lot of aggregations.

Note that this is just a simple minimal example that triggers it. We create one regular table and one partitioned table with 1024 partitions. A query that

a) selects from the regular table

b) lateral joins the partitioned table to look up just one single row+column, referencing some column from the first table and doing this inside some subplan (coalesce/ case when/ etc.)

c) does a lot of aggregations - in this simple case it just aggregated the same column multiple times, but imagine this is a table with a lot of different columns that we want to sum/avg etc.

In this case, the planner generates a plan with one SubPlan per aggregate (see below for EXPLAIN output). Each SubPlan initially has to create a range table entry for every partition. So if we do 10 aggregations with 1024 partitions, we get 10240 entries in our range table. You can imagine it's pretty easy to hit the 65k limit on range table entries like this. Of course, all but one get pruned away due to partition pruning, but this happens too late for clauses such as now() as in this example. For PG11, this error even occurs when pruning for static timestamps, but on PG12 the particular case for static timestamps is fixed due to the recent work on partition pruning at planning time.

I was wondering if developers here are aware of this issue. In the previous threads that I read about this issue, it always looked like a far-fetched scenario that was very difficult to hit. This one (although still not trivial?) seems to be potentially more common. In any case it looks inefficient to me that SubPlans are chosen here if they lead to so many references in the range table? I understand a lot of this code probably comes from a time before partitioning, where it's nearly impossible to hit these kind of issues, but we might see more of this in the future.

I'm not really looking for a particular solution to my problem here (we can rewrite our queries to just avoid these SubPlans for now, so that's not a problem). I just wanted to share my findings to see if the community indeed finds it strange that examples like this yield errors and if they should be fixed, or if users should work around them on their own by rewriting their queries not to use SubPlans for partitioned tables.

-Floris

[1] https://www.postgresql.org/message-id/flat/20180625181410.fg5xejq4qsbnryl2%40alap3.anarazel.de#5756b3df32a3cc6184baba5eb9dd4174​