Re: sqlsmith crash incremental sort

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