Re: Too many range table entries error

On 2018-06-25 13:46:06 +0530, Akshaya Acharya wrote:
> On Mon, 25 Jun 2018 at 13:40, Andres Freund <andres(at)anarazel(dot)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