I wrote:
>> FWIW, this reproduces the bug for me in v13 and v14, but not v15 or HEAD.
I decided I'd wasted entirely too much time trying to find a suitable
test case, so I pushed the bug fix with no new regression test.
Meanwhile, back at the question of whether db0d67db2 is buggy,
it looks like that reduces to whether it was intentional that that
made a large change in estimated sort costs. What I'm getting from
v15 and HEAD, as I said earlier, is:
Sort (cost=2946.79..3096.79 rows=60000 width=36)
Sort Key: (((unnest('{}'::jsonb[])) ->> 'timestamp'::text))
-> Result (cost=90.08..1446.79 rows=60000 width=36)
-> ProjectSet (cost=90.08..396.79 rows=60000 width=36)
-> Hash Join (cost=90.08..96.19 rows=60 width=0)
Hash Cond: (groups.group_id = results.group_id)
-> Seq Scan on groups (cost=0.00..5.66 rows=166 width=4)
-> Hash (cost=89.33..89.33 rows=60 width=4)
-> Index Only Scan using results_pkey on results (cost=0.28..89.33 rows=60 width=4)
Index Cond: (search_id = 3336)
After applying this patch, what I get from v13 and v14 is
Sort (cost=6208.59..6358.59 rows=60000 width=36)
Sort Key: (((unnest('{}'::jsonb[])) ->> 'timestamp'::text))
-> Result (cost=90.08..1446.79 rows=60000 width=36)
-> ProjectSet (cost=90.08..396.79 rows=60000 width=36)
-> Hash Join (cost=90.08..96.19 rows=60 width=0)
Hash Cond: (groups.group_id = results.group_id)
-> Seq Scan on groups (cost=0.00..5.66 rows=166 width=4)
-> Hash (cost=89.33..89.33 rows=60 width=4)
-> Index Only Scan using results_pkey on results (cost=0.28..89.33 rows=60 width=4)
Index Cond: (search_id = 3336)
So this plan is identical except for the sort costs, which seem to
be about half of what they were in the older branches. If that was
intentional, why didn't the commit message mention it? It's not
exactly a minor change, and enable_group_by_reordering doesn't
seem to have any effect on it.
regards, tom lane