From: | Antonin Houska <ah(at)cybertec(dot)at> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | from_collapse_limit considerations |
Date: | 2014-09-22 17:16:30 |
Message-ID: | 18009.1411406190@linux-j735.site |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
While doing experiments with rather long FROM-lists, I looked closely at the
logic related to from_collapse_limit.
I noticed that - unlike join_collapse_limit - the from_collapse_limit does not
enforce maximum length of the top-level list. Shouldn't it do? Too long
FROM-list can obviously lead to excessive planning time.
Also, the order of FROM-list items seems to affect the way RTEs are grouped
into (sub)lists. In this example, the join of tab_0, tab_1, tab_2, tab_3 gets
expanded into 4 separate RTE refs:
SET from_collapse_limit TO 5;
SELECT *
FROM
(
(
tab_0
JOIN
tab_1
ON tab_0.id = tab_1.id
)
JOIN
(
tab_2
JOIN
tab_3
ON tab_2.id = tab_3.id
)
ON tab_1.id = tab_2.id
),
tab_4
JOIN
tab_5
ON tab_4.id = tab_5.id
WHERE tab_3.id = tab_4.id;
However, in the next example (the JOIN of tab_4 and tab_5 moved to the
beginning of the FROM list), the "bigger join" (tab_0 through tab_3) "comes
too late", so it's inserted as a sub-list.
SET from_collapse_limit TO 5;
SELECT *
FROM
tab_4
JOIN
tab_5
ON tab_4.id = tab_5.id,
(
(
tab_0
JOIN
tab_1
ON tab_0.id = tab_1.id
)
JOIN
(
tab_2
JOIN
tab_3
ON tab_2.id = tab_3.id
)
ON tab_1.id = tab_2.id
)
WHERE tab_3.id = tab_4.id;
Is anything wrong about the idea not to estimate the total length of the FROM
list in deconstruct_recurse and to do additional collapsing later instead? The
patch attached here tries to do so.
I wonder if change of the logic behind from_collapse_limit should be
considered acceptable for users or not: although it improves control over
planning of queries having long FROM-list, it can make some plans of existing
applications worse, unless from_collapse_limit is increased accordingly.
Attachment | Content-Type | Size |
---|---|---|
from_list_folding.patch | text/x-diff | 3.8 KB |
unknown_filename | text/plain | 159 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2014-09-22 19:38:30 | Re: END_OF_RECOVERY shutdowns and ResetUnloggedRelations() |
Previous Message | Tom Lane | 2014-09-22 15:43:19 | Re: from_collapse_limit considerations |