On Thu, Apr 16, 2020 at 04:44:10PM +0800, Richard Guo wrote:
>On Mon, Apr 13, 2020 at 8:09 AM Tomas Vondra <tomas.vondra@2ndquadrant.com>
>wrote:
>
>>
>> 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.
>
Right.
>
>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;
>
Yep, that's a much simpler query / plan. Thanks.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services