On Tue, Aug 2, 2022 at 4:50 PM Martijn van Oosterhout <kleptog@gmail.com> wrote:
Now it's morning I've thought of a way to reproduce it more easily, see the attached script. The tricky part is getting the tuples in a position that triggers the planner in the right way. So the script includes a list of (ctid, primary key) and creates a table using that with quite a large amount of bloat. It then creates some constraints, vacuums and runs the offending query. On my system it reproduces with 100% reliability (so far anyway).
Thanks for the report! I can reproduce it on HEAD. The part of the plan that causes problem looks like:
Please note that the expression below appears in the targetlist of seqscan:
unnest('{}'::jsonb[]) ->> 'timestamp'::text
The func for operator '->>' itself is not proretset, but one of its args (the unnest func) is proretset. And that causes problem when we set up projection info for the seqscan node.
So why does this expression appear in the targetlist of seqscan node? I did some debug on that. Since this expression is the desired pathkey of the query, relation_can_be_sorted_early() checks on it and finds that it can be computed from the reltarget of rel 'results', which is true since this expression can be computed all by itself. So it is considered as a useful ordering for rel 'results' and generate_useful_gather_paths() creates the Sort and then Gather Merge nodes for 'results' based on this pathkey.
