Обсуждение: Too many range table entries error
Hello.
Please could you help debug the error "too many range table entries”?
This error occurs when querying a view that is dependent on many other views (i.e. a view high up in the pyramid of views that we've constructed).
I get this error when running select * on the view, or when running an explain analyse on the select.
Views that use a total of more than around 40000 table references (in the complete tree considering all the dependent views recursively) don't work, but it works with 20000 table references. What is the maximum number of table references possible?
Can I increase this number somehow?
Perhaps relevant:
Postgres docs, what is range table https://www.postgresql.org/docs/current/static/querytree.html
postgres src, error message https://github.com/postgres/postgres/blob/master/src/backend/optimizer/plan/setrefs.c
Postgres version 10.3 from official docker image.
Thanks
Akshaya
Hi, On 2018-06-25 13:02:37 +0530, Akshaya Acharya wrote: > Hello. > > > Please could you help debug the error "too many range table entries”? > > > This error occurs when querying a view that is dependent on many other > views (i.e. a view high up in the pyramid of views that we've constructed). > > > I get this error when running select * on the view, or when running an > explain analyse on the select. > > > Views that use a total of more than around 40000 table references (in the > complete tree considering all the dependent views recursively) don't work, > but it works with 20000 table references. What is the maximum number of > table references possible? Why are you doing this? I can't imagine queries with that many table references ever being something useful? I'm pretty sure there's better solutions for what you're doing. > Can I increase this number somehow? It's not impossible, it's not entirely trivial either. The relevant variables currently are 16bit wide, and the limit is close to the max for that. Greetings, Andres Freund
On Mon, 25 Jun 2018 at 13:40, Andres Freund <andres@anarazel.de> wrote:
Hi,
On 2018-06-25 13:02:37 +0530, Akshaya Acharya wrote:
> Hello.
>
>
> Please could you help debug the error "too many range table entries”?
>
>
> This error occurs when querying a view that is dependent on many other
> views (i.e. a view high up in the pyramid of views that we've constructed).
>
>
> I get this error when running select * on the view, or when running an
> explain analyse on the select.
>
>
> Views that use a total of more than around 40000 table references (in the
> complete tree considering all the dependent views recursively) don't work,
> but it works with 20000 table references. What is the maximum number of
> table references possible?
Why are you doing this? I can't imagine queries with that many table
references ever being something useful? I'm pretty sure there's better
solutions for what you're doing.
Our entire application—all our business logic—is built as layers of views inside the database. The ref counts sort of multiple at each layer, hence the large number.
> Can I increase this number somehow?
It's not impossible, it's not entirely trivial either. The relevant
variables currently are 16bit wide, and the limit is close to the max
for that.
I understand.
At slide 25 of this presentation a patch is indicated. Is this relevant to our situation? https://www.slideshare.net/hansjurgenschonig/postgresql-joining-1-million-tables
Alternatively we will have to optimize our views or change the architecture of our application? Is there any other way to resolve this situation?
Greetings,
Andres Freund
On 2018-06-25 13:46:06 +0530, Akshaya Acharya wrote: > On Mon, 25 Jun 2018 at 13:40, Andres Freund <andres@anarazel.de> wrote: > > > Hi, > > > > On 2018-06-25 13:02:37 +0530, Akshaya Acharya wrote: > > > Hello. > > > > > > > > > Please could you help debug the error "too many range table entries”? > > > > > > > > > This error occurs when querying a view that is dependent on many other > > > views (i.e. a view high up in the pyramid of views that we've > > constructed). > > > > > > > > > I get this error when running select * on the view, or when running an > > > explain analyse on the select. > > > > > > > > > Views that use a total of more than around 40000 table references (in the > > > complete tree considering all the dependent views recursively) don't > > work, > > > but it works with 20000 table references. What is the maximum number of > > > table references possible? > > > > Why are you doing this? I can't imagine queries with that many table > > references ever being something useful? I'm pretty sure there's better > > solutions for what you're doing. > > > > Our entire application—all our business logic—is built as layers of views > inside the database. The ref counts sort of multiple at each layer, hence > the large number. That still doesn't explain how you realistically get to 40k references, and how that's a reasonable design. There's be quite the massive runtime and memory overhead for an approach like this. What was the reasoning leading to this architecture. > > > Can I increase this number somehow? > > > > It's not impossible, it's not entirely trivial either. The relevant > > variables currently are 16bit wide, and the limit is close to the max > > for that. > > > > I understand. > > At slide 25 of this presentation a patch is indicated. Is this relevant to > our situation? > https://www.slideshare.net/hansjurgenschonig/postgresql-joining-1-million-tables Yes, but that change likely isn't sufficient. > Alternatively we will have to optimize our views or change the architecture > of our application? Is there any other way to resolve this situation? Yes I think you will have to, and no I don't see any other. Greetings, Andres Freund
Andres Freund <andres@anarazel.de> writes:
> On 2018-06-25 13:46:06 +0530, Akshaya Acharya wrote:
>> Our entire application-all our business logic-is built as layers of views
>> inside the database. The ref counts sort of multiple at each layer, hence
>> the large number.
> That still doesn't explain how you realistically get to 40k references,
> and how that's a reasonable design.
The short answer here is that even if the system accepted queries with
that many tables, it's really unlikely to perform acceptably --- in fact,
I'm a bit astonished that you even found a way to reach this error without
having waited a few hours beforehand. And we are *not* going to promise
to fix all the performance issues you will hit with a schema design like
this. Redesign. Please.
regards, tom lane
Thank you very much for your guidance on this.
I was speaking with a friend about this, and he said something to the effect of "keep it aside and do it later" and then a solution hit me.
Since we can allow this data to be stale in our case, replacing some key views in the hierarchy of views with materialized views has worked for us.
With regards to the architecture/design, I am still learning as I go along, I will articulate my thoughts and post later. In the long term, we will redesign the system with this new knowledge in mind.
Regards
Akshaya
On Tue, 26 Jun 2018 at 06:08, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Andres Freund <andres@anarazel.de> writes:
> On 2018-06-25 13:46:06 +0530, Akshaya Acharya wrote:
>> Our entire application-all our business logic-is built as layers of views
>> inside the database. The ref counts sort of multiple at each layer, hence
>> the large number.
> That still doesn't explain how you realistically get to 40k references,
> and how that's a reasonable design.
The short answer here is that even if the system accepted queries with
that many tables, it's really unlikely to perform acceptably --- in fact,
I'm a bit astonished that you even found a way to reach this error without
having waited a few hours beforehand. And we are *not* going to promise
to fix all the performance issues you will hit with a schema design like
this. Redesign. Please.
regards, tom lane