DocumentDB 0.116 (MongoDB 7.0)

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from pg_settings where name in ('documentdb.enableDistinctScanForGroupFirst','documentdb.enableSortPushToAccumulatorWithPrefix')

name
setting
unit
category
short_desc
extra_desc
context
vartype
source
min_val
max_val
enumvals
boot_val
reset_val
sourcefile
sourceline
pending_restart

documentdb.enableDistinctScanForGroupFirst
off
null
Customized Options
Whether or not to enable the distinct custom scan wrapper for $group pipelines whose accumulators are exclusively $first.
null
user
bool
default
null
null
null
off
off
null
null
f

documentdb.enableSortPushToAccumulatorWithPrefix
on
null
Customized Options
Whether to push suffix sort keys into accumulator when group keys are a prefix of sort keys in $sortGroup.
null
user
bool
default
null
null
null
on
on
null
null
f

SELECT 2

SET search_path TO documentdb_api, documentdb_core, documentdb_api_catalog,  
  documentdb_api_internal, public; 

SET

-- data + ordered index  

SELECT count(documentdb_api.insert_one(  
  'perf116', 'sort_group',  
  format('{"_id":%s,"a":%s,"name":"name_%s","payload":"%s"}',  
         i, i % 100, i, repeat('x', 200))::documentdb_core.bson, NULL))  
FROM generate_series(1, 50000) i; 

SELECT documentdb_api_internal.create_indexes_non_concurrently(  
  'perf116',  
  '{"createIndexes":"sort_group","indexes":[{"key":{"a":1},"storageEngine":{"enableOrderedIndex":true},"name":"a_1"}]}',  
  true);  

count

50000

SELECT 1

create_indexes_non_concurrently

BSONHEX7e00000003726177006c0000000364656661756c7453686172640059000000106e756d496e64657865734265666f72650001000000106e756d496e6465786573416674657200020000000863726561746564436f6c6c656374696f6e4175746f6d61746963616c6c790000106f6b00010000000000106f6b000100000000

SELECT 1

VACUUM (ANALYZE);

VACUUM

SET enable_seqscan TO off;  
SET enable_bitmapscan TO off;  
SET enable_hashagg TO off;  
SET documentdb.enableGroupByCompoundIdIndexPushdown TO off;  

SET

SET

SET

SET

-- 1) DocumentDB 0.116 defaults: prefix pushdown ON, distinct scan OFF.
--    The explicit $sort is absorbed: no Sort node, but 50,000 rows are read.
SET documentdb.enableSortPushToAccumulatorWithPrefix TO on;
SET documentdb.enableDistinctScanForGroupFirst TO off;

EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS)
SELECT document FROM bson_aggregation_pipeline(
  'perf116',
  '{"aggregate":"sort_group","pipeline":[{"$sort":{"a":1}},{"$group":{"_id":"$a","firstVal":{"$first":"$name"}}}],"cursor":{},"hint":"a_1"}');

SET

SET

QUERY PLAN

GroupAggregate (actual rows=100.00 loops=1)

  Output: bson_repath_and_build('_id'::text, (bson_expression_get(document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77007c2ffeeea001000000'::bson)), 'firstVal'::text, bsonfirstwithexpr(document, 'BSONHEX11000000020006000000246e616d650000'::bson, 'BSONHEX12000000096e6f77007c2ffeeea001000000'::bson, NULL::text)), (bson_expression_get(document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77007c2ffeeea001000000'::bson))

  Group Key: bson_expression_get(collection.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77007c2ffeeea001000000'::bson)

  Buffers: shared hit=50023

  ->  Custom Scan (DocumentDBApiExplainQueryScan) (actual rows=50000.00 loops=1)

        Output: bson_expression_get(document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77007c2ffeeea001000000'::bson), document

        namespaceName: perf116.sort_group

        indexName: a_1

        indexKey: {"a": 1}

        isMultiKey: false

        indexBounds: ["a": (MinKey, MaxKey)]

        innerScanLoops: 100 loops

        scanType: ordered

        scanKeyDetails: key 1: [(isInequality: true, estimatedEntryCount: 50000)]

        _id_: (startup cost=0.415, total cost=1379.415, selectivity=1, correlation=0.750, estimated index pages loaded=100.00%, estimated total index entries=50000, boundary selectivity=1, num boundaries=0, estimated data pages loaded=0.00%)

        Buffers: shared hit=50023

        ->  Index Scan using a_1 on documentdb_data.documents_2 collection (actual rows=50000.00 loops=1)

              Output: document

              Index Cond: (collection.document @<> 'BSONHEX1e00000003610016000000106f7264657242795363616e00010000000000'::bson)

              Order By: (collection.document |-<> 'BSONHEX0c0000001061000100000000'::bson)

              Index Searches: 0

              Buffers: shared hit=50023

Planning:

  Buffers: shared hit=491 read=7

EXPLAIN

-- 2) Prefix pushdown OFF, distinct scan ON: the explicit Sort remains
--    and spills to disk (~13.8 MB in this fiddle).
SET documentdb.enableSortPushToAccumulatorWithPrefix TO off;
SET documentdb.enableDistinctScanForGroupFirst TO on;

EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS)
SELECT document FROM bson_aggregation_pipeline(
  'perf116',
  '{"aggregate":"sort_group","pipeline":[{"$sort":{"a":1}},{"$group":{"_id":"$a","firstVal":{"$first":"$name"}}}],"cursor":{},"hint":"a_1"}');

SET

SET

QUERY PLAN

GroupAggregate (actual rows=100.00 loops=1)

  Output: bson_repath_and_build('_id'::text, (bson_expression_get(agg_stage_0.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77001330feeea001000000'::bson)), 'firstVal'::text, bson_expression_get(bsonfirst(agg_stage_0.document, '{BSONHEX0c0000001061000100000000}'::bson[]), 'BSONHEX11000000020006000000246e616d650000'::bson, true, 'BSONHEX12000000096e6f77001330feeea001000000'::bson)), (bson_expression_get(agg_stage_0.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77001330feeea001000000'::bson))

  Group Key: (bson_expression_get(agg_stage_0.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77001330feeea001000000'::bson))

  Buffers: shared hit=50023, temp read=1725 written=1728

  ->  Sort (actual rows=50000.00 loops=1)

        Output: (bson_expression_get(agg_stage_0.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77001330feeea001000000'::bson)), agg_stage_0.document

        Sort Key: (bson_expression_get(agg_stage_0.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77001330feeea001000000'::bson))

        Sort Method: external merge  Disk: 13800kB

        Buffers: shared hit=50023, temp read=1725 written=1728

        ->  Subquery Scan on agg_stage_0 (actual rows=50000.00 loops=1)

              Output: bson_expression_get(agg_stage_0.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77001330feeea001000000'::bson), agg_stage_0.document

              Buffers: shared hit=50023

              ->  Custom Scan (DocumentDBApiExplainQueryScan) (actual rows=50000.00 loops=1)

                    Output: collection.document, bson_orderby(collection.document, 'BSONHEX0c0000001061000100000000'::bson)

                    namespaceName: perf116.sort_group

                    indexName: a_1

                    indexKey: {"a": 1}

                    isMultiKey: false

                    indexBounds: ["a": (MinKey, MaxKey)]

                    innerScanLoops: 100 loops

                    scanType: ordered

                    scanKeyDetails: key 1: [(isInequality: true, estimatedEntryCount: 50000)]

                    _id_: (startup cost=0.415, total cost=1379.415, selectivity=1, correlation=0.750, estimated index pages loaded=100.00%, estimated total index entries=50000, boundary selectivity=1, num boundaries=0, estimated data pages loaded=0.00%)

                    Buffers: shared hit=50023

                    ->  Index Scan using a_1 on documentdb_data.documents_2 collection (actual rows=50000.00 loops=1)

                          Output: collection.document

                          Index Cond: (collection.document @<> 'BSONHEX1e00000003610016000000106f7264657242795363616e00010000000000'::bson)

                          Order By: (collection.document |-<> 'BSONHEX0c0000001061000100000000'::bson)

                          Index Searches: 0

                          Buffers: shared hit=50023

Planning:

  Buffers: shared hit=25 read=2

EXPLAIN

-- 3) Both settings ON (the 0.117 defaults): the distinct scan applies,
--    reading 100 index entries (shared hit=28).
SET documentdb.enableSortPushToAccumulatorWithPrefix TO on;
SET documentdb.enableDistinctScanForGroupFirst TO on;

EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS)
SELECT document FROM bson_aggregation_pipeline(
  'perf116',
  '{"aggregate":"sort_group","pipeline":[{"$sort":{"a":1}},{"$group":{"_id":"$a","firstVal":{"$first":"$name"}}}],"cursor":{},"hint":"a_1"}');

SET

SET

QUERY PLAN

GroupAggregate (actual rows=100.00 loops=1)

  Output: bson_repath_and_build('_id'::text, (bson_expression_get(document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77005f31feeea001000000'::bson)), 'firstVal'::text, bsonfirstwithexpr(document, 'BSONHEX11000000020006000000246e616d650000'::bson, 'BSONHEX12000000096e6f77005f31feeea001000000'::bson, NULL::text)), (bson_expression_get(document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77005f31feeea001000000'::bson))

  Group Key: bson_expression_get(collection.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77005f31feeea001000000'::bson)

  Buffers: shared hit=28

  ->  Custom Scan (DocumentDBApiExplainQueryScan) (actual rows=100.00 loops=1)

        Output: bson_expression_get(document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77005f31feeea001000000'::bson), document

        namespaceName: perf116.sort_group

        indexName: a_1

        indexKey: {"a": 1}

        isMultiKey: false

        indexBounds: ["a": (MinKey, MaxKey)]

        innerScanLoops: 100 loops

        scanType: ordered

        scanKeyDetails: key 1: [(isInequality: true, estimatedEntryCount: 50000)]

        _id_: (startup cost=0.415, total cost=1379.415, selectivity=1, correlation=0.750, estimated index pages loaded=100.00%, estimated total index entries=50000, boundary selectivity=1, num boundaries=0, estimated data pages loaded=0.00%)

        Buffers: shared hit=28

        ->  Custom Scan (DocumentDBApiDistinctQueryScan) (actual rows=100.00 loops=1)

              Output: document

              Buffers: shared hit=28

              ->  Index Scan using a_1 on documentdb_data.documents_2 collection (actual rows=100.00 loops=1)

                    Output: document

                    Index Cond: (collection.document @<> 'BSONHEX1e00000003610016000000106f7264657242795363616e00010000000000'::bson)

                    Order By: (collection.document |-<> 'BSONHEX0c0000001061000100000000'::bson)

                    Index Searches: 0

                    Buffers: shared hit=28

Planning:

  Buffers: shared hit=16

EXPLAIN

-- 4) No-$sort control: prefix pushdown OFF, distinct scan ON; expect 100 entries.
SET documentdb.enableSortPushToAccumulatorWithPrefix TO off;
SET documentdb.enableDistinctScanForGroupFirst TO on;

EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS)
SELECT document FROM bson_aggregation_pipeline(
  'perf116',
  '{"aggregate":"sort_group","pipeline":[{"$group":{"_id":"$a","firstVal":{"$first":"$name"}}}],"cursor":{},"hint":"a_1"}');

SET

SET

QUERY PLAN

GroupAggregate (actual rows=100.00 loops=1)

  Output: bson_repath_and_build('_id'::text, (bson_expression_get(document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77006d31feeea001000000'::bson)), 'firstVal'::text, bsonfirstwithexpr(document, 'BSONHEX11000000020006000000246e616d650000'::bson, 'BSONHEX12000000096e6f77006d31feeea001000000'::bson, NULL::text)), (bson_expression_get(document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77006d31feeea001000000'::bson))

  Group Key: bson_expression_get(collection.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77006d31feeea001000000'::bson)

  Buffers: shared hit=28

  ->  Custom Scan (DocumentDBApiExplainQueryScan) (actual rows=100.00 loops=1)

        Output: bson_expression_get(document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77006d31feeea001000000'::bson), document

        namespaceName: perf116.sort_group

        indexName: a_1

        indexKey: {"a": 1}

        isMultiKey: false

        indexBounds: ["a": (MinKey, MaxKey)]

        innerScanLoops: 100 loops

        scanType: ordered

        scanKeyDetails: key 1: [(isInequality: true, estimatedEntryCount: 50000)]

        _id_: (startup cost=0.415, total cost=1379.415, selectivity=1, correlation=0.750, estimated index pages loaded=100.00%, estimated total index entries=50000, boundary selectivity=1, num boundaries=0, estimated data pages loaded=0.00%)

        Buffers: shared hit=28

        ->  Custom Scan (DocumentDBApiDistinctQueryScan) (actual rows=100.00 loops=1)

              Output: document

              Buffers: shared hit=28

              ->  Index Scan using a_1 on documentdb_data.documents_2 collection (actual rows=100.00 loops=1)

                    Output: document

                    Index Cond: (collection.document @<> 'BSONHEX1e00000003610016000000106f7264657242795363616e00010000000000'::bson)

                    Order By: (collection.document |-<> 'BSONHEX0c0000001061000100000000'::bson)

                    Index Searches: 0

                    Buffers: shared hit=28

Planning:

  Buffers: shared hit=10

EXPLAIN

-- 5) HashAggregate comparison: hash aggregation is allowed; prefix pushdown is OFF.
--    The pipeline still has $sort, but this plan does not need a Sort node.
RESET enable_hashagg;

SET documentdb.enableSortPushToAccumulatorWithPrefix TO off;
SET documentdb.enableDistinctScanForGroupFirst TO on;

EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS)
SELECT document FROM bson_aggregation_pipeline(
  'perf116',
  '{"aggregate":"sort_group","pipeline":[{"$sort":{"a":1}},{"$group":{"_id":"$a","firstVal":{"$first":"$name"}}}],"cursor":{},"hint":"a_1"}');

RESET

SET

SET

QUERY PLAN

HashAggregate (actual rows=100.00 loops=1)

  Output: bson_repath_and_build('_id'::text, (bson_expression_get(agg_stage_0.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77007a31feeea001000000'::bson)), 'firstVal'::text, bson_expression_get(bsonfirst(agg_stage_0.document, '{BSONHEX0c0000001061000100000000}'::bson[]), 'BSONHEX11000000020006000000246e616d650000'::bson, true, 'BSONHEX12000000096e6f77007a31feeea001000000'::bson)), (bson_expression_get(agg_stage_0.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77007a31feeea001000000'::bson))

  Group Key: bson_expression_get(agg_stage_0.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77007a31feeea001000000'::bson)

  Batches: 1  Memory Usage: 217kB

  Buffers: shared hit=50023

  ->  Subquery Scan on agg_stage_0 (actual rows=50000.00 loops=1)

        Output: bson_expression_get(agg_stage_0.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77007a31feeea001000000'::bson), agg_stage_0.document

        Buffers: shared hit=50023

        ->  Custom Scan (DocumentDBApiExplainQueryScan) (actual rows=50000.00 loops=1)

              Output: collection.document, bson_orderby(collection.document, 'BSONHEX0c0000001061000100000000'::bson)

              namespaceName: perf116.sort_group

              indexName: a_1

              indexKey: {"a": 1}

              isMultiKey: false

              indexBounds: ["a": (MinKey, MaxKey)]

              innerScanLoops: 100 loops

              scanType: ordered

              scanKeyDetails: key 1: [(isInequality: true, estimatedEntryCount: 50000)]

              _id_: (startup cost=0.415, total cost=1379.415, selectivity=1, correlation=0.750, estimated index pages loaded=100.00%, estimated total index entries=50000, boundary selectivity=1, num boundaries=0, estimated data pages loaded=0.00%)

              Buffers: shared hit=50023

              ->  Index Scan using a_1 on documentdb_data.documents_2 collection (actual rows=50000.00 loops=1)

                    Output: collection.document

                    Index Cond: (collection.document @<> 'BSONHEX1e00000003610016000000106f7264657242795363616e00010000000000'::bson)

                    Order By: (collection.document |-<> 'BSONHEX0c0000001061000100000000'::bson)

                    Index Searches: 0

                    Buffers: shared hit=50023

Planning:

  Buffers: shared hit=10

EXPLAIN
name	setting	unit	category	short_desc	extra_desc	context	vartype	source	min_val	max_val	enumvals	boot_val	reset_val	sourcefile	sourceline	pending_restart
documentdb.enableDistinctScanForGroupFirst	off	null	Customized Options	Whether or not to enable the distinct custom scan wrapper for $group pipelines whose accumulators are exclusively $first.	null	user	bool	default	null	null	null	off	off	null	null	f
documentdb.enableSortPushToAccumulatorWithPrefix	on	null	Customized Options	Whether to push suffix sort keys into accumulator when group keys are a prefix of sort keys in $sortGroup.	null	user	bool	default	null	null	null	on	on	null	null	f