I've been messing with this the whole day, without much progress :-(
I'm 99.9999% sure it's the same issue described by the quoted comment,
because the plan looks like this:
Nested Loop Left Join
-> Sample Scan on pg_namespace
Sampling: system ('7.2'::real)
-> Incremental Sort
Sort Key: ...
Presorted Key: ...
-> Unique
-> Sort
Sort Key: ...
-> Append
-> Nested Loop
...
-> Nested Loop
...
so yeah, the plan does have set operations, and generate_append_tlist
does generate Vars with varno == 0, causing this issue.
After some digging I believe here is what happened.
1. For the UNION query, we build an upper rel of UPPERREL_SETOP and
generate Append path for it. Since Append doesn't actually evaluate its
targetlist, we generate 'varno 0' Vars for its targetlist. (setrefs.c
would just replace them with OUTER_VAR when adjusting the final plan so
this usually does not cause problems.)
2. To remove duplicates for UNION, we use hash/sort to unique-ify the
result. If sort is chosen, we add Sort path and then Unique path above
Append path, with pathkeys made from Append's targetlist.
3. Also the Append's targetlist would be built into
root->processed_tlist and with that we calculate root->sort_pathkeys.
4. When handling ORDER BY clause, we figure out the pathkeys of
Unique->Sort->Append path share some same prefix with
root->sort_pathkeys and thus incremental sort would be considered.
5. When calculating cost for incremental sort, estimate_num_groups does
not cope with 'varno 0' Vars extracted from root->sort_pathkeys.
With this scenario, here is a simple recipe:
create table foo(a int, b int, c int);
set enable_hashagg to off;
explain select * from foo union select * from foo order by 1,3;
Thanks
Richard
