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.
-- create example tables. p1 as a regular table, q1 as a partitioned table with
1024 partitions
drop table if exists p1;
create table p1 (
ts timestamptz,
p int
);
drop table if exists q1;
create table q1 (
ts timestamptz,
q int
) partition by range(ts);
do $an$
DECLARE
i int;
begin
for i in select * from generate_series(1, 1024) loop
execute format($f$ create table q1_%s partition of q1 for values from
('%s') to ('%s') $f$, i, '2015-04-04'::date - i, '2015-04-04'::date - i + 1);
end loop;
end;
$an$;
-- this yields the error. removing one aggregation runs fine
explain
select
sum(p*q), sum(p*q), sum(p*q), sum(p*q), sum(p*q), sum(p*q), sum(p*q),
sum(p*q),
sum(p*q), sum(p*q), sum(p*q), sum(p*q), sum(p*q), sum(p*q), sum(p*q),
sum(p*q),
sum(p*q), sum(p*q), sum(p*q), sum(p*q), sum(p*q), sum(p*q), sum(p*q),
sum(p*q),
sum(p*q), sum(p*q), sum(p*q), sum(p*q), sum(p*q), sum(p*q), sum(p*q),
sum(p*q),
sum(p*q), sum(p*q), sum(p*q), sum(p*q), sum(p*q), sum(p*q), sum(p*q),
sum(p*q),
sum(p*q), sum(p*q), sum(p*q), sum(p*q), sum(p*q), sum(p*q), sum(p*q),
sum(p*q),
sum(p*q), sum(p*q), sum(p*q), sum(p*q), sum(p*q), sum(p*q), sum(p*q),
sum(p*q),
sum(p*q), sum(p*q), sum(p*q), sum(p*q), sum(p*q), sum(p*q), sum(p*q),
sum(p*q)
from p1
cross join lateral (
select coalesce((select q from q1 where ts = now() and ts > p1.ts limit 1),
1) q
) _
;
Example EXPLAIN output if we just do 2 sum(p*q) aggregations instead of 64
aggregations:
Aggregate (cost=62223.20..62223.21 rows=1 width=16)
-> Seq Scan on p1 (cost=0.00..30.40 rows=2040 width=12)
SubPlan 2
-> Limit (cost=0.00..15.24 rows=1 width=4)
-> Append (cost=0.00..46812.16 rows=3072 width=4)
Subplans Removed: 1023
-> Seq Scan on q1_1024 q1_1024_1 (cost=0.00..45.70 rows=3
width=4)
Filter: ((ts > p1.ts) AND (ts = now()))
SubPlan 1
-> Limit (cost=0.00..15.24 rows=1 width=4)
-> Append (cost=0.00..46812.16 rows=3072 width=4)
Subplans Removed: 1023
-> Seq Scan on q1_1024 (cost=0.00..45.70 rows=3 width=4)
