Thread: [PERFORM] Sudden drastic change in performance
Hello all,
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. Same for the where clause. The views are similar and also join 3-4 tables each. All in all, there are 3 of all the tables involved 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 order of T8/T9, then the same happens with T8. So I don’t think this has to do with the tables themselves. I have updated all the statistics and reindexed all involved tables.
Any idea as to what could be causing this issue? Am I having one too many joins and tripping the query execution? The query plans are very large in both cases, so I figured I’d abstract the cases a bit for this question, but could provide those plans if someone thinks it’d be useful.
Thank you,
Laurent.
"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
Am 15. Juni 2017 16:53:44 MESZ schrieb "ldh@laurent-hasson.com" <ldh@laurent-hasson.com>: >Hello all, > >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 > > >Thank you, >Laurent. Please show us the explain analyse for the queries. Regards, Andreas -- Diese Nachricht wurde von meinem Android-Mobiltelefon mit K-9 Mail gesendet.