Too many range table entries error

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?

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: