DROP TABLE btg;

-- The case, when scanning sort order correspond to aggregate sort order but

-- can not be found in the group-by list

CREATE TABLEt1 (c1 int PRIMARY KEY, c2 int);

CREATE UNIQUE INDEX ONt1(c2);

CREATE TABLEagg_sort_order (c1 int PRIMARY KEY, c2 int);

CREATE UNIQUE INDEX ONagg_sort_order(c2);

explain (costs off)

SELECT array_agg(c1 ORDER BY c2),c2

FROMt1 WHERE c2 < 100 GROUP BY c1 ORDER BY 2;

QUERY PLAN

--------------------------------------------------------

FROMagg_sort_order WHERE c2 < 100 GROUP BY c1 ORDER BY 2;

QUERY PLAN

--------------------------------------------------------------------

Sort

Sort Key: c2

-> GroupAggregate

Group Key: c1

-> Sort

Sort Key: c1, c2

-> Bitmap Heap Scan ont1

-> Bitmap Heap Scan onagg_sort_order

Recheck Cond: (c2 < 100)

-> Bitmap Index Scan ont1_c2_idx

-> Bitmap Index Scan onagg_sort_order_c2_idx

Index Cond: (c2 < 100)

(10 rows)

DROP TABLEt1 CASCADE;

DROP TABLEagg_sort_order CASCADE;

-- Check, that GROUP-BY reordering optimization can operate with pathkeys, built

-- by planner itself. For example, by MergeJoin.

SET enable_hashjoin = off;

CREATETABLEt1 (c1intPRIMARY KEY, c2int);

CREATEUNIQUE INDEXONt1(c2);

CREATETABLEagg_sort_order (c1intPRIMARY KEY, c2int);

CREATEUNIQUE INDEXONagg_sort_order(c2);

explain (costs off)

SELECT array_agg(c1ORDER BY c2),c2

FROMt1WHERE c2<100GROUP BY c1ORDER BY2;

DROPTABLEt1 CASCADE;

FROMagg_sort_orderWHERE c2<100GROUP BY c1ORDER BY2;

DROPTABLEagg_sort_order CASCADE;