On Mon, 25 Jun 2018 at 13:40, Andres Freund <andres@xxxxxxxxxxx> 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
