On 2018-06-25 13:46:06 +0530, Akshaya Acharya wrote: > 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. 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
