from_collapse_limit considerations

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

Responses

Browse pgsql-hackers by date

  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