"ldh@laurent-hasson.com" <ldh@laurent-hasson.com> writes:
> I have a query with many joins, something like:
> Select c1, c2, c3, sum(c5)
> From V1
> Join V2 on ...
> Left join V3 on ...
> Left join T4 on ...
> Join T5 on ...
> Join T6 on ...
> Left join T7 on ...
> Join T8 on ...
> Left join T9 on ...
> Where ...
> Group by c1, c2, c3
> The join clauses are fairly innocuous and work directly on foreign key relationships, so there is no voodoo there.
Samefor the where clause. The views are similar and also join 3-4 tables each. All in all, there are 3 of all the
tablesinvolved that have millions of rows and all the other tables have thousands of rows. In particular, T9 is totally
empty.
> If I remove T9 from the query, it takes 9s to run. If I keep T9, the query takes over 30mn to run! If I switch the
orderof T8/T9, then the same happens with T8. So I don't think this has to do with the tables themselves. I have
updatedall the statistics and reindexed all involved tables.
You need to raise join_collapse_limit to keep the planner from operating
with its stupid cap on. Usually people also increase from_collapse_limit
if they have to touch either, but I think for this specific query syntax
only the former matters.
regards, tom lane
