MySQL is very good at serving application workloads, but things get more complicated when somebody decides to run a large report on the same server. A scan can push hot pages out of the buffer pool. A long-running read can hold back purge. A GROUP BY over millions of rows can start writing temporary tables … Continued
The post DBTrail: Analytical Reports on Your MySQL Data, Without Running Them on MySQL – Part 1 appeared first on Percona.
On behalf of the entire product and engineering team, I’m pleased to announce that Percona ClusterSync for MongoDB (PCSM) 1.0.0 is now generally available with all its features inside. It is production-ready, fully supported, and already proven on real multi-terabyte migrations. If you want to move off MongoDB Atlas, MongoDB Enterprise, or MongoDB Community to … Continued
The post Percona ClusterSync for MongoDB 1.0.0 is Generally Available appeared first on Percona.
Welcome back to Village News, our curated roundup of MySQL and database news.
If you want to get these updates, just subscribe to the blog.
Enjoy!
Note: Aggregated MySQL news can be found at Planet for MySQL Community and Planet MySQL (Oracle curated)
The Problem The application is slow, the Slack channel is on fire, and somebody asks the oldest question in our job: What is running on the database right now? Query Analytics (QAN) in PMM is great for the history. It shows which queries used the most time in the last hour or the last 12 … Continued
The post What Is Running on My MongoDB Right Now? Real-Time Analytics in PMM appeared first on Percona.
Microsoft announced the public preview of the pg_duckdb extension for Azure Database for PostgreSQL Flexible Server at Ignite on November 18, 2025. In August 2026, Amazon announced an agreement to acquire DuckLabs, the company behind DuckDB, but the DuckDB open-source project itself remains independent. On September 30, AWS announced that Amazon Aurora PostgreSQL can use embedded DuckDB to query Apache Iceberg and Parquet data in data lakes alongside operational data. Let's compare.
Amazon Aurora PostgreSQL can now query Parquet and Iceberg in S3 through the aurora_analytics extension, and AWS says DuckDB is embedded. I wanted to prove that rather than take it on faith. The method is simple: get an execution plan out of Aurora, then get one out of a known-DuckDB engine (the open-source pg_duckdb) for the same query over the same file, and compare. Here is every step.
aurora_analytics needs two things: an Aurora PostgreSQL cluster on a supported version (17.11+ or 18.6+), and an IAM role attached to the cluster with the AuroraAnalytics feature so Aurora can read your S3 bucket. I used a CloudFormation stack The essential pieces are:
EngineVersion=17.11;aurora_analytics.enabled = 1;rds.amazonaws.com, with S3 read on the data bucket (and Glue read if you want Iceberg), attached to the cluster via AssociatedRoles with FeatureName: AuroraAnalytics;I deployed it with CloudFormation:
aws cloudformation deploy --stack-name aurora-analytics ...
aws cloudformation describe-stacks --stack-name aurora-analytics --query 'Stacks[0].Outputs' --output table
I uploaded the Parquet file and connected to Aurora:
aws s3 cp title.parquet s3://my-bucket/job/title.parquet
psql "host=<cluster-endpoint> port=5432 dbname=analytics user=dbadmin sslmode=require"
I enabled the extension and defined a foreign table over the file. The empty column list makes Aurora infer the schema from the Parquet footer:
CREATE EXTENSION aurora_analytics;
CREATE FOREIGN TABLE title_ft ()
SERVER aurora_analytics_server
OPTIONS (location 's3://my-bucket/job/title.parquet', format 'parquet');
SELECT count(*) FROM title_ft;
Here is the query I compared, and its plan:
EXPLAIN (ANALYZE, VERBOSE)
SELECT production_year, count(*)
FROM title_ft
WHERE kind_id = 1
GROUP BY production_year
ORDER BY 2 DESC
;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Custom Scan (actual time=65.848..65.919 rows=21 loops=1)
Output: aurora_analytics.production_year, aurora_analytics.count
Pushdown SQL: SELECT production_year, count(*) AS count FROM (SELECT kind_id::INTEGER AS kind_id, production_year::INTEGER AS production_year FROM system.main.read_parquet($aurora_analytics_parameter_1) AS title_ft) title_ft WHERE (kind_id = 1::INTEGER) GROUP BY production_year ORDER BY (count(*)) DESC
Total CPU Time: 0.0 ms
Effective Parallelism: 0.7
Total Thread Wait Time: 0.0 ms
-> PROJECTION (actual time=51.637..51.637 rows=21 loops=1)
Projections: __internal_decompress_integral_integer(#0, 1920), #1
-> ORDER_BY (actual time=51.614..51.614 rows=21 loops=1)
Order By: count_star() DESC
-> PROJECTION (actual time=46.880..46.880 rows=21 loops=1)
Projections: __internal_compress_integral_utinyint(#0, 1920), #1
-> PROJECTION (actual time=46.873..46.873 rows=21 loops=1)
Projections: __internal_decompress_integral_integer(#0, 1920), #1
-> PERFECT_HASH_GROUP_BY (actual time=46.844..46.844 rows=21 loops=1)
Groups: #0
Aggregates: count_star()
-> PROJECTION (actual time=16.582..43.982 rows=100000 loops=1)
Projections: production_year
-> PROJECTION (actual time=16.571..43.982 rows=100000 loops=1)
Projections: __internal_compress_integral_utinyint(#0, 1920)
-> READ_PARQUET (actual time=14.609..43.981 rows=100000 loops=1)
Table: title_ft
Filters: kind_id=1
Projections: production_year
Total Files Read: 1
Rows Removed by Filter: 400000
Query Identifier: 5750582199016602255
Planning Time: 369.237 ms
Execution Time: 81.265 ms
Analytics Cache Hit Bytes: 93kB
Analytics Remote Read Bytes: 0kB
S3 HEAD Request Count: 0
S3 GET Request Count: 0
This is already suggestive: READ_PARQUET, PERFECT_HASH_GROUP_BY, count_star(), and a Pushdown SQL line that reads FROM system.main.read_parquet(...). Those are DuckDB's names. But let's prove it.
The cover image of this post shows the same with the Microsoft PostgreSQL extension for VSCode on Kiro.
pg_duckdb is the open-source DuckDB-in-PostgreSQL extension. I run it locally:
docker run -d --name pgduckdb -e POSTGRES_PASSWORD=duckdb -e POSTGRES_DB=lab \
-p 55432:5432 pgduckdb/pgduckdb:17-v1.1.1
# put the SAME file where the container can read it
docker cp title.parquet pgduckdb:/tmp/title.parquet
docker exec -it pgduckdb psql -U postgres -d lab
I execute the same query with the pg_duckdb functions:
CREATE EXTENSION pg_duckdb;
SET duckdb.force_execution = true;
EXPLAIN
SELECT r['production_year'] AS production_year, count(*) AS count
FROM read_parquet('/tmp/title.parquet') r
WHERE r['kind_id'] = 1
GROUP BY r['production_year']
ORDER BY count DESC
;
pg_duckdb draws the plan as a box tree:
Here are the operators:
Custom Scan (DuckDBScan)
DuckDB Execution Plan:
PROJECTION __internal_decompress_integral_integer(#0, 1920), #1
ORDER_BY count_star() DESC
PROJECTION __internal_compress_integral_utinyint(#0, 1920), #1
PERFECT_HASH_GROUP_BY Groups: #0 Aggregates: count_star()
PROJECTION production_year
PROJECTION __internal_compress_integral_utinyint(#0, 1920)
READ_PARQUET
Function: READ_PARQUET
Projections: production_year
Filters: kind_id=1
~100,000 rows
We recognize the same functions, presented differently.
I put them next to each other:
Aurora aurora_analytics
|
pg_duckdb (Docker) | |
|---|---|---|
| Wrapper node |
Custom Scan (provider aurora_analytics) |
Custom Scan (DuckDBScan) |
| Scan |
READ_PARQUET Filters: kind_id=1, Projections: production_year |
READ_PARQUET Filters: kind_id=1, Projections: production_year |
| Group by |
PERFECT_HASH_GROUP_BY / count_star()
|
PERFECT_HASH_GROUP_BY / count_star()
|
| Sort | ORDER_BY count_star() DESC |
ORDER_BY count_star() DESC |
| Compression op | __internal_compress_integral_utinyint(#0, 1920) |
__internal_compress_integral_utinyint(#0, 1920) |
| Decompression op | __internal_decompress_integral_integer(#0, 1920) |
__internal_decompress_integral_integer(#0, 1920) |
They are the same plan. The detail that removes all doubt is the compression operator: both engines, independently, encoded production_year with __internal_compress_integral_utinyint(#0, 1920). That is DuckDB's frame-of-reference integer compression, and 1920 is the minimum production_year in the file — the base value DuckDB subtracts so the column fits in an unsigned byte. A separate implementation would not reproduce DuckDB's internal operator names and independently pick the same base constant from the
data. Aurora’s plan exposes DuckDB operators, indicating that aurora_analytics uses DuckDB or DuckDB-derived components under its Foreign Data Wrapper.
It's the same engine, but opposite exposure. We can look at what each extension registers.
With pg_duckdb the whole DuckDB surface is SQL-visible:
postgres=# \dx+ pg_duckdb
Objects in extension "pg_duckdb"
Object description
--------------------------------------------------------------------
access method duckdb
cast from duckdb.unresolved_type to bigint
cast from duckdb.unresolved_type to bigint[]
...
The full output shows 366 objects, including 235 function entries (overloads counted separately), 1 foreign-data wrapper (duckdb), and 23 type entries (including arrays). The remaining 107 objects include casts, operators, triggers, tables, and other objects.
With aurora_analytics the engine is sealed behind an FDW:
postgres=# \dx+ aurora_analytics
Objects in extension "aurora_analytics"
Object description
--------------------------------------------------------------------
foreign-data wrapper aurora_analytics_fdw
function aurora_analytics_fdw_handler()
function aurora_analytics_fdw_validator(text[],oid)
server aurora_analytics_server
In Aurora the DuckDB SQL dialect is not reachable — read_parquet() as a function, list/struct literals, QUALIFY, approx_count_distinct, ::HUGEINT are all rejected by PostgreSQL’s SQL layer, because only the foreign-table scan is handed to DuckDB. With pg_duckdb you can call the engine directly (duckdb.query('…')).
So, it's the same DuckDB underneath, but without exposing its functions. in summary:
| pg_duckdb | aurora_analytics | |
|---|---|---|
| Where it runs | Any PostgreSQL (open source) | Aurora PostgreSQL only (17.11+/18.6+) |
| Engine | DuckDB | DuckDB, embedded behind a foreign-data wrapper |
| Lake interface |
read_parquet('…') r with r['col']
|
CREATE FOREIGN TABLE … OPTIONS(format 'parquet'), schema inferred |
| Direct DuckDB SQL | Yes — duckdb.query(), DuckDB dialect, extensions |
No — engine sealed; only foreign-table scans |
| Accelerates your own PostgreSQL tables | Yes — SET duckdb.force_execution = true
|
No — foreign (lake) tables only |
| Write back to the lake | Yes — COPY (…) TO 's3://…' / az://
|
No — read-only |
| Join live tables with the lake | Yes | Yes |
| Iceberg / Delta |
iceberg_scan / delta_scan (DuckDB extensions) |
Iceberg via Glue catalog; no Delta |
| Credentials | DuckDB secret (connection string / keys) | IAM role attached to the cluster |
| Memory / spill knob | duckdb.max_memory |
aurora_analytics.query_mem |
| Observability |
EXPLAIN, duckdb.log_pg_explain
|
EXPLAIN, aurora_analytics_stat_statements()
|
OFFSET is simple to implement, but the database still needs to read and discard rows before reaching the requested page. Keyset pagination improves this by beginning after the last row from the previous page. Since different databases have various query planner optimizations and index access methods, the only way to ensure correctness is to examine the execution plan.
The usual example, which I take from Vlad Mihalcea's blog post, orders posts by date and uses the identifier as a unique tie-breaker:
ORDER BY created_on DESC, id DESC
The next page should begin after the last row, and the most effective way to specify this in SQL is through row comparison:
WHERE (created_on, id) < (:last_created_on, :last_id)
ORDER BY created_on DESC, id DESC
FETCH FIRST 50 ROWS ONLY
There are two details that make the difference between a direct index seek and an index scan with a filter:
The second point is easy to miss. A query might look flawless and produce correct results, yet still omit the second index column because it compares a bigint with a numeric. Whether you're a developer or an agent, always verify this by checking the execution plan.
This is the table and index used in the original example:
CREATE TABLE post (
created_on timestamp(6),
id bigint NOT NULL,
title varchar(255),
PRIMARY KEY (id)
);
CREATE INDEX idx_post_created_on_id
ON post (created_on DESC, id DESC);
The complete reproductions are available for PostgreSQL 18 and PostgreSQL 15 which I used in a Linkedin discussion.
SQL can compare rows. The SQL standard defines the comparison predicate, including greater than, with row value constructors:
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT id, created_on
FROM post
WHERE ( created_on , id )
< ( timestamp '2019-10-02 21:00:00' , 4951::bigint )
ORDER BY created_on DESC, id DESC
LIMIT 50;
PostgreSQL puts the complete row comparison in the Index Cond:
Limit (actual rows=50.00 loops=1)
Buffers: shared hit=39
-> Index Only Scan using idx_post_created_on_id on post
(actual rows=50.00 loops=1)
Index Cond: (ROW(created_on, id) <
ROW('2019-10-02 21:00:00'::timestamp without time zone,
'4951'::bigint))
Heap Fetches: 50
Index Searches: 1
Buffers: shared hit=39
Planning:
Buffers: shared hit=60 read=4
Planning Time: 1.925 ms
Execution Time: 0.308 ms
This is what I want for pagination. The B-tree navigates to the composite value and reads the next 50 entries in index order. No sort or filter is needed. It seeks directly to the index entry of the first row to fetch, and it reads only what is necessary for the result.
The id is not optional here. Many posts can have the same timestamp. Pagination needs a total and stable order, so the last ordering column must make the key unique.
A row comparison is equivalent, for non-null values, to:
created_on < :last_created_on
OR (
created_on = :last_created_on
AND id < :last_id
)
This query returns the same rows:
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT id, created_on
FROM post
WHERE created_on < timestamp '2019-10-02 21:00:00'
OR (
created_on = timestamp '2019-10-02 21:00:00'
AND id < 4951::bigint
)
ORDER BY created_on DESC, id DESC
LIMIT 50;
But PostgreSQL keeps the expression as a filter, after scanning all rows:
Limit (actual rows=50.00 loops=1)
Buffers: shared hit=39
-> Index Only Scan using idx_post_created_on_id on post
(actual rows=50.00 loops=1)
Filter: ((created_on <
'2019-10-02 21:00:00'::timestamp without time zone)
OR ((created_on =
'2019-10-02 21:00:00'::timestamp without time zone)
AND (id < '4951'::bigint)))
Heap Fetches: 50
Index Searches: 1
Buffers: shared hit=39
Planning:
Buffers: shared hit=3
Planning Time: 0.133 ms
Execution Time: 0.049 ms
This small test does not show a performance problem because the first entries happen to qualify. The important difference is the plan operation: Filter is not Index Cond.
With many rows sharing the cursor timestamp, or with a cursor deep in the index, this form may scan and reject many entries before returning 50 rows. The row comparator gives PostgreSQL the composite boundary directly.
Do not replace it with one of these:
-- Too restrictive
created_on < :created_on AND id < :id
-- Too broad
created_on < :created_on OR id < :id
-- Also wrong: it loses older rows having a larger id
created_on <= :created_on AND id < :id
Lexicographic order needs the equality prefix:
created_on < :created_on
OR (created_on = :created_on AND id < :id)
This is logically equivalent to the row comparison, but database query planners rarely recognize x < y OR x = y as a single bound.
Now I change only one value:
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT id, created_on
FROM post
WHERE ( created_on , id )
< ( timestamp '2019-10-02 21:00:00' , 4951::numeric )
ORDER BY created_on DESC, id DESC -------------
LIMIT 50;
The column is bigint, but the value is numeric. Here is the PostgreSQL plan:
Limit (actual rows=50.00 loops=1)
Buffers: shared hit=39
-> Index Only Scan using idx_post_created_on_id on post
(actual rows=50.00 loops=1)
Index Cond: (created_on <=
'2019-10-02 21:00:00'::timestamp without time zone)
Filter: (ROW(created_on, (id)::numeric) <
ROW('2019-10-02 21:00:00'::timestamp without time zone,
'4951'::numeric))
Heap Fetches: 50
Index Searches: 1
Buffers: shared hit=39
Planning:
Buffers: shared hit=9
Planning Time: 0.288 ms
Execution Time: 0.107 ms
The significant lines are:
Index Cond: (created_on <= ...)
Filter: (ROW(created_on, (id)::numeric) < ...)
The index stores id as bigint, but the comparison casts the indexed column to numeric. The second column is no longer usable as the bigint B-tree boundary.
PostgreSQL still extracts a safe condition from the leading column:
created_on <= :last_created_on
It must be <=, not <, because rows at the same timestamp can qualify when their identifier is lower. PostgreSQL then evaluates the original row expression as a filter to preserve the correct result.
The optimizer source calls this a lossy version of the row comparison. It means that the index condition identifies a superset of the required rows and the complete condition must be rechecked.
The fix is only a cast, but it must be on the value:
WHERE (created_on, id)
< (:last_created_on::timestamp, :last_id::bigint)
Do not cast the indexed column (except if it is indexed with an expression-based index):
-- Avoid this
WHERE (created_on, id::numeric)
< (:last_created_on, :last_id::numeric)
For a column defined as bigint, the application should bind the cursor as int8/bigint, not as numeric or BigDecimal when that changes the PostgreSQL parameter type.
A prepared statement makes the contract explicit:
PREPARE next_page(timestamp, bigint) AS
SELECT id, created_on
FROM post
WHERE (created_on, id) < ($1, $2)
ORDER BY created_on DESC, id DESC
LIMIT 50;
A prepared statement or function is the best way to ensure the execution plan uses the right data type, regardless of what is passed.
The previous fiddle helps you inspect the plan, but its data doesn't magnify the difference. This creates one million rows, with 500,000 rows sharing each timestamp:
DROP TABLE IF EXISTS post;
CREATE UNLOGGED TABLE post (
id bigint PRIMARY KEY,
created_on timestamp NOT NULL,
title text
);
INSERT INTO post
SELECT g,
timestamp '2024-01-01'
- ((g - 1) / 500000) * interval '1 day',
'post ' || g
FROM generate_series(1, 1000000) AS g;
CREATE INDEX post_seek_idx
ON post (created_on DESC, id DESC);
VACUUM (ANALYZE) post;
Run the three alternatives separately:
-- Exact composite boundary
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT id, created_on
FROM post
WHERE (created_on, id)
< (timestamp '2024-01-01', 100::bigint)
ORDER BY created_on DESC, id DESC
LIMIT 50;
-- Only a leading-column boundary because bigint is compared with numeric
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT id, created_on
FROM post
WHERE (created_on, id)
< (timestamp '2024-01-01', 100::numeric)
ORDER BY created_on DESC, id DESC
LIMIT 50;
-- Exact logical expansion, but commonly retained as a filter
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT id, created_on
FROM post
WHERE created_on < timestamp '2024-01-01'
OR (
created_on = timestamp '2024-01-01'
AND id < 100::bigint
)
ORDER BY created_on DESC, id DESC
LIMIT 50;
The first query can position near (2024-01-01,99). The other two may start near the beginning of the 500,000 entries for 2024-01-01 and filter almost all of them.
Always run this with your distribution and parameters. A query is not efficient because it contains Index Scan. Inspect Index Cond, Filter, rows removed, buffers, and the number of entries actually read.
The short answer depends on whether “support” means syntax or a direct composite index boundary.
| Database | Ordered row syntax | Composite-index behavior for pagination |
|---|---|---|
| PostgreSQL | ✅ | Yes , with compatible B-tree operators and matching datatypes |
| YugabyteDB | ✅ | The original PostgreSQL-style pattern is still not an exact DocDB seek. A new hash-aware form exists |
| MySQL | ✅ | Yes when the row is the leftmost index prefix. Important limitation when it follows separate key predicates |
| MongoDB | ❌ | The exact $or can use two index scans combined with SORT_MERGE
|
| Oracle | ❌ | Use scalar predicates. Typically a leading range plus filter, or bounded UNION ALL branches |
| SQL Server | ❌ | The exact OR can become multiple ranges in one ordered Index Seek
|
Accepting the syntax is not a sufficient test because the goal of this pagination filter is to read only what is necessary. The execution plan is the evidence. Here are some detailed tests
I wrote about this in Efficient pagination in YugabyteDB & PostgreSQL.
The index was:
CREATE UNIQUE INDEX demo1_key_ts_id
ON demo1(key, ts DESC, id DESC);
In YugabyteDB, the first unspecified index column is hash-sharded, so this is effectively:
(key HASH, ts DESC, id DESC)
The elegant PostgreSQL query was:
WHERE key = $1
AND (ts, id) < ($2, $3)
ORDER BY ts DESC, id DESC
LIMIT $4
On YugabyteDB 2.8, the complete historical plan showed why Index Cond was not sufficient evidence:
Limit (actual time=26446.024..26748.760 rows=1000 loops=1)
-> Nested Loop
-> Index Scan using demo1_key_ts_id on demo1
Index Cond:
((key = 1)
AND
(ROW(ts, id) <
ROW('2022-01-01 00:00:01+00'::timestamp with time zone,
'000fffff-ffff-ffff-ffff-ffffffffffff'::uuid)))
Rows Removed by Index Recheck: 998000
Planning Time: 1.159 ms
Execution Time: 26751.767 ms
The storage layer had not started at the complete row value. PostgreSQL rechecked and rejected 998,000 entries.
The workaround was to provide a scalar bound for the first range column:
WHERE key = $1
AND ts <= $2
AND (
ts < $2
OR (ts = $2 AND id < $3)
)
ORDER BY ts DESC, id DESC
LIMIT $4
The historical plan became:
Limit (actual time=18.769..305.666 rows=1000 loops=1)
-> Nested Loop
-> Index Scan using demo1_key_ts_id on demo1
Index Cond:
((key = 1)
AND
(ts <=
'2022-01-01 00:00:01+00'::timestamp with time zone))
Filter:
((ts <
'2022-01-01 00:00:01+00'::timestamp with time zone)
OR
((ts =
'2022-01-01 00:00:01+00'::timestamp with time zone)
AND
(id <
'000fffff-ffff-ffff-ffff-ffffffffffff'::uuid)))
Planning Time: 1.039 ms
Execution Time: 309.985 ms
What about now?
YugabyteDB 2026.1 added a useful row boundary for hash indexes, but it is a different key shape. It starts with the hash code and includes a contiguous key prefix:
CREATE TABLE hc_rc_t1 (
h int,
r1 int,
r2 int,
v text,
PRIMARY KEY (h HASH, r1 ASC, r2 ASC)
);
INSERT INTO hc_rc_t1
SELECT i % 5, i, i * 10, 'val' || i
FROM generate_series(1, 50) AS i;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT *
FROM hc_rc_t1
WHERE (yb_hash_code(h), h, r1, r2)
> (yb_hash_code(1), 1, 10, 100)
LIMIT 5;
The released 2026.1.2 regression plan is:
Limit (actual rows=5 loops=1)
-> Index Scan using hc_rc_t1_pkey on hc_rc_t1
(actual rows=5 loops=1)
Index Cond:
(ROW(yb_hash_code(h), h, r1, r2)
> ROW(4624, 1, 10, 100))
Without the leading hash code:
SELECT *
FROM hc_rc_t1
WHERE (h, r1, r2) > (1, 10, 100)
LIMIT 5;
the released test expects:
Limit (actual rows=5 loops=1)
-> Seq Scan on hc_rc_t1
Filter: (ROW(h, r1, r2) > ROW(1, 10, 100))
Rows Removed by Filter: 2
This does not fix the original portable predicate:
key = :key AND (ts, id) < (:ts, :id)
Issue #11794 remains open, and a July 2026 issue still describes those row-comparison bounds as loose bounds requiring recheck. For the original index and ordering, I would still use the decomposed predicate and verify it with:
EXPLAIN (ANALYZE, DIST, COSTS OFF)
In YugabyteDB, check Storage Index Rows Scanned and Rows Removed by Index Recheck, not only Index Cond.
MySQL 8.4 supports the same lexicographic syntax:
CREATE TABLE post (
id bigint NOT NULL PRIMARY KEY,
created_on datetime(6) NOT NULL,
payload varchar(100),
INDEX post_seek_i (created_on DESC, id DESC)
) ENGINE = InnoDB;
EXPLAIN ANALYZE
SELECT id, created_on
FROM post
WHERE (created_on, id)
< ('2026-01-01 12:00:00', 50000)
ORDER BY created_on DESC, id DESC
LIMIT 50;
With the row constructor covering the leftmost index prefix, the desired plan shape is:
Limit: 50 row(s)
-> Index range scan on post using post_seek_i
But MySQL documents an important exception. With:
CREATE INDEX post_tenant_seek_i
ON post (tenant_id, created_on DESC, id DESC);
WHERE tenant_id = ?
AND (created_on, id) < (?, ?)
the row starts at the second index part. MySQL may use only tenant_id. Its documented example reports:
type: ref
key: PRIMARY
key_len: 4
Extra: Using where
Expanding the row comparison:
WHERE tenant_id = ?
AND (
created_on < ?
OR (created_on = ? AND id < ?)
)
allows the documented example to use all three key parts:
type: range
key: PRIMARY
key_len: 12
Extra: Using where
So MySQL supports index range access for row comparison, but do not generalize that to every position in a composite index. Check access_type, used_key_parts, actual rows, and using_filesort.
Reference: MySQL row-constructor optimization.
MongoDB has no equivalent of:
(created_on, id) < (:created_on, :id)
For this index:
db.events.createIndex(
{created_on: -1, _id: -1},
{name: "created_on_-1__id_-1"}
);
use the exact lexicographic expansion:
const T = ISODate("2026-01-01T12:01:00Z");
const I = ObjectId("000000000000000000000004");
const after = {
$or: [
{created_on: {$lt: T}},
{create... (truncated)
Thread Pool in Percona Server and MySQL (Part 1) A new version of Community MySQL Server July release contains two versions 26.7.0 and 9.7.2. It is an important milestone because it finally brings a Thread Pooling feature to the community. The official MySQL Server Thread Pool plugin existed for a long time, but was available … Continued
The post Thread Pool in Percona Server and MySQL (Part 1) appeared first on Percona.
The FETCH FIRST ... ROWS ONLY clause arrived in the SQL standard with SQL:2008, and Oracle Database implemented it in 12cR1, released in June 2013.
Before that, there were two common ways to write a Top-N query, both requiring a subquery:
ROWNUM outside:
SELECT *
FROM (
SELECT ...
FROM ...
ORDER BY ...
)
WHERE ROWNUM <= 42;
ROW_NUMBER(), then filter its result outside:
SELECT *
FROM (
SELECT ...,
ROW_NUMBER() OVER (ORDER BY ...) AS rn
FROM ...
)
WHERE rn <= 42;
The first subquery is necessary because the ordering must happen before the ROWNUM filter. The second is necessary because the analytic function cannot be evaluated in the same query block’s WHERE clause.
With FETCH FIRST, we can express the intention directly:
SELECT ...
FROM ...
ORDER BY ...
FETCH FIRST 42 ROWS ONLY;
However, a simpler SQL statement does not necessarily mean a simpler implementation.
Like many additions to Oracle’s SQL syntax, FETCH FIRST was implemented through a transformation to existing constructs. Oracle initially chose the analytic ROW_NUMBER() solution.
This was the familiar rewrite in 12c, 18c, 19c, 21c, and early 23c releases. In the recent 23ai and 26ai releases tested here, the simple FETCH FIRST n ROWS ONLY case is rewritten using ROWNUM.
“AI” is not responsible for the change. Oracle 18c and 19c belong to the 12cR2 release family, with names reflecting the release year. Similarly, 26ai is still in the 23 release family, and “ai” replaced “c” with the 23.4 release.
Beyond the marketing names, the relevant change is fix control 35915968:
SQL> SELECT bugno, description, optimizer_feature_enable
FROM v$system_fix_control
WHERE bugno = 35915968;
BUGNO DESCRIPTION OPTIMIZER_FEATURE_ENABLE
---------- ----------------------------------------------------- ------------------------
35915968 fetch first transformation using rownum 23.1.0
The OPTIMIZER_FEATURE_ENABLE column tells us the optimizer compatibility setting associated with the control.
There is another clue in ORACLE_HOME. In the 26ai installation used for this investigation, rdbms/admin/bundlefcp_DBBP.xml lists this control under the 23.4.0.0.0 bundle:
<bug id="35915968">
<fix_control default_value="1">35915968</fix_control>
</bug>
This is more useful than simply saying “23ai changed it.”
Choosing ROW_NUMBER() rather than ROWNUM already had a consequence in 12cR1: the optimizer did not apply the same first-k-row optimization.
I blogged about this in 2014: ROWNUM vs ROW_NUMBER() and 12c fetch first.
With ROWNUM <= 10, Oracle knew that it needed only the first ten rows and could cost an ordered index access accordingly. With the analytic rewrite, it could cost the access as if many more rows were needed, making a full scan and sort look preferable.
My recommendation was to add FIRST_ROWS(n), not the old FIRST_ROWS hint.
This was improved in 19c:
SQL> SELECT bugno, description, optimizer_feature_enable
FROM v$system_fix_control
WHERE bugno = 22174392;
BUGNO DESCRIPTION OPTIMIZER_FEATURE_ENABLE
---------- ---------------------------------------------------------------- ------------------------
22174392 first k row optimization for window function rownum predicate 19.1.0
I blogged about it in 2020: 19c: scalable Top-N queries without further hints to the query planner.
The improvement is visible in the execution plan as WINDOW NOSORT STOPKEY, the stopkey replacing the simple analytic filter WINDOW SORT PUSHED RANK, so that Oracle could cost the analytic Top-N query with the first-k-row objective and choose that access without the additional hint.
When an index supplies the required order, Oracle can stop early. Without an ordered access path, a sort may still be necessary.
The first-k-row is not the only difference between ROWNUM and the analytic function. ROWNUM was also acts as a non-mergeable view barrier, forcing the optimizer to treat the query block as an isolated inline view.
For the simple row limit tested here, Oracle 26ai now goes back to the other implementation:
SQL> EXPLAIN PLAN FOR
SELECT *
FROM dual
ORDER BY dummy
FETCH FIRST 42 ROWS ONLY;
SQL> SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'BASIC +PREDICATE'));
------------------------------------
| Id | Operation | Name |
------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | COUNT STOPKEY | |
| 2 | VIEW | |
| 3 | TABLE ACCESS FULL| DUAL |
------------------------------------
Predicate Information:
----------------------
1 - filter(ROWNUM<=42)
The transformation is visible with DBMS_UTILITY.EXPAND_SQL_TEXT:
WITH
FUNCTION expand_my_sql(p_sql IN VARCHAR2) RETURN CLOB IS
v_output CLOB;
BEGIN
DBMS_UTILITY.EXPAND_SQL_TEXT(
input_sql_text => p_sql,
output_sql_text => v_output
);
RETURN v_output;
END;
SELECT expand_my_sql(
'SELECT * FROM DUAL ORDER BY DUMMY FETCH FIRST 42 ROWS ONLY'
) AS expanded_sql
/
Formatted for readability, the output is:
SELECT "A1"."DUMMY" "DUMMY"
FROM (
SELECT "A2"."DUMMY" "DUMMY"
FROM "SYS"."DUAL" "A2"
ORDER BY "A2"."DUMMY"
) "A1"
WHERE ROWNUM <= 42
We can restore the earlier transformation on the same database by disabling the change:
ALTER SESSION SET "_fix_control" = '35915968:0';
The expanded SQL becomes:
SELECT "A1"."DUMMY" "DUMMY"
FROM (
SELECT "A2"."DUMMY" "DUMMY",
"A2"."DUMMY" "rowlimit_$_0",
ROW_NUMBER() OVER (
ORDER BY "A2"."DUMMY"
) "rowlimit_$$_rownumber"
FROM "SYS"."DUAL" "A2"
) "A1"
WHERE "A1"."rowlimit_$$_rownumber" <= 42
ORDER BY "A1"."rowlimit_$_0"
And the plan uses the analytic operation again:
---------------------------------------
| Id | Operation | Name |
---------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | VIEW | |
|* 2 | WINDOW NOSORT STOPKEY| |
| 3 | TABLE ACCESS FULL | DUAL |
---------------------------------------
Predicate Information:
----------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=42)
2 - filter(ROW_NUMBER() OVER (ORDER BY "DUAL"."DUMMY")<=42)
More than a decade after introducing FETCH FIRST, Oracle is using the legacy ROWNUM solution for this case. It benefits from the existing first-k-row optimization and gives the optimizer a well-established row-limit boundary.
This is not a claim that all row-limiting clauses now use ROWNUM. OFFSET, percentages, and WITH TIES have additional semantics. In the same 26ai binary, another control, 35969400, can also select a native ROW LIMIT operation.
But for the case discussed here, going back to ROWNUM also avoids a wrong-results bug.
A Stack Overflow question showed Oracle returning a row for this query:
SELECT 1 AS one_row
FROM dual
WHERE EXISTS (SELECT * FROM view_abcd)
AND NOT EXISTS (SELECT * FROM view_abcd)
;
This is a logical contradiction. EXISTS returns true or false depending on whether its subquery returns any rows, regardless of the values in those rows. If one predicate is true, the other must be false.
The view combines OUTER APPLY, an inner ANSI JOIN, and a correlated FETCH FIRST 1 ROW ONLY. The question reports the problem on 19c and 21c.
On Oracle 23ai Free 23.9.0.25.07, the default result is correct. Disabling 35915968 makes it return the wrong row: db<>fiddle.
I also reproduced it on 26ai Free 23.26.3.0.0 with:
ALTER SESSION SET optimizer_features_enable = '21.1.0';
That was a starting point, not the solution. I wanted a finer setting than changing the whole optimizer compatibility level.
I used Copilot for this investigation. Not just to ask “what is the answer?”, but to run the experiments I would normally run myself.
Here are some of my prompts:
“Please find the bug published by Oracle.”
“Is it ANSI join? There’s a long history of ANSI joins implemented as transformations and not working.”
“Reproduce and use Pathfinder to find the exact parameter or bug control to avoid it.”
“Maybe you have run without the latest patches.”
“I’m able to reproduce the bug on 26ai with
optimizer_features_enable='21.1.0', but I would like a finer setting.”
The agent ran Mauro Pagano’s Pathfinder with OFE 21.1 as the baseline, testing 2,811 cases. I described this method in the past (I ‘fixed’ execution plan regression with optimizer_features_enable, what to do next?).
Two controls avoided the wrong result: 35915968:1 and 35969400:1. The description of the first immediately connected the result to the FETCH FIRST rewrite.
Then I prompted Copilot for the next step:
“Look at the execution plans.”
“Is it a combination of
FETCH FIRSTtransformation, ANSI join, and merged views?”
This is where AI assistance is useful to me. My experience guides the investigation. The agent handles the repetitive experiments, collects the output, and helps compare it. The results still have to explain the behavior. A plausible answer is not enough.
The failing execution plan is reduced to an unconditional FAST DUAL. The correct plan retains the filter, the outer-join branches, and the COUNT STOPKEY operations.
The expanded SQL and execution plans show the difference. The paired 10053 traces add something more interesting: the analytic and ROWNUM rewrites lead to different view-merging decisions.
In the analytic path, Oracle keeps the window-function query block:
SVM: SVM bypassed on view SEL$4(#0): Window functions in this view.
It also retains an additional correlated lateral-view layer:
SVM: SVM bypassed on view SEL$F23444D6(#0): Lateral view with left correlation.
In the ROWNUM path, Oracle merges the simple inner query into the block containing the row limit:
CVM: Merging SPJ view SEL$4 (#0) into SEL$6 (#0)
The resulting scalar subquery is simpler:
SELECT "D"."DUMMY"
FROM "SYS"."DUAL" "D"
WHERE ROWNUM <= 1
AND "D"."DUMMY" = "A"."DUMMY"
It also merges a layer that remained separate in the analytic path:
CVM: Merging SPJ view SEL$F23444D6 (#0) into SEL$3 (#0)
So the explanation is not simply “ROWNUM prevents view merging.” The correct path actually performs more merges at these points. It removes unnecessary wrapper blocks while retaining the scalar subquery’s row-limit boundary.
Both paths still have restrictions around the null-augmented outer-joined lateral view. The analytic path adds another combination of boundaries and correlations, and that combination ends in the wrong executable plan.
This identifies the problematic path. It does not identify the exact line of Oracle code responsible, nor prove that 35915968 was originally created to fix this particular report. What we have demonstrated is that enabling this transformation avoids the wrong result.
I like standard SQL syntax. FETCH FIRST expresses the intention more clearly than a manually nested ROWNUM query. I've seen too many of those queries with the wrong placement of ORDER BY.
But syntax support and native implementation are different things.
Oracle’s ANSI joins are translated into internal structures, including lateral views and legacy outer-join representations. The row-limit clause was translated into an analytic query. Each transformation may be reasonable on its own. Their combinations are where the complexity grows.
There is a long history of ANSI-related wrong-results bugs. Jonathan Lewis has documented examples involving ANSI joins and NATURAL JOIN. I've see it striking again with materialized views and assertions.
Transformations are essential to query optimization. The problem is not that they exist. It is that adding a feature through several layers of rewriting also adds interactions that must preserve the original semantics.
Here, the legacy ROWNUM implementation has two advantages: first-k-row optimization and a row-limit boundary that Oracle already knows how to handle. Finally, the newer syntax stayed but the machinery underneath returned to the simpler implementation.
And AI can help us investigate that machinery. Not by replacing database knowledge with a confident explanation, but by making it easier to run the experiments that turn an explanation into evidence. You don't know to download Pathfinder and run it yourself when an AI agent can reproduce everything on a docker container.
You may wonder how PostgreSQL dealt with the addition of FETCH FIRST to the SQL standard. Unlike Oracle’s initial implementation, PostgreSQL did not rewrite it into an analytic ROW_NUMBER() query. FETCH FIRST was implemented as an alternative syntax for LIMIT/OFFSET which already existed, and EXPLAIN shows a Limit node. The planner also knows the requested row count when costing paths: if an index supplies the required order, it can choose that path and stop once enough rows have been produced. Otherwise, it may still need to scan or sort more data.
At the top level, FETCH FIRST does not add a query block. Inside a subquery, though, the row limit prevents that subquery from being pulled up, since moving the limit could change which rows it returns. So PostgreSQL has a native stop-after-N boundary, conceptually like Oracle’s COUNT STOPKEY—not the same operator, but the same basic idea. The engines converge on that approach. PostgreSQL used it from the start, while Oracle initially implemented FETCH FIRST through ROW_NUMBER() analytic filter and later changed the rewrite for the simple row-limit case.
Here's a detailed look at scalar $group index pushdown, an optimization introduced with DocumentDB 0.117-0 on September 10, 2026. It also highlights an advantage over MongoDB: the PostgreSQL query planner ensures optimal performance without requiring additional hints in the query.
DocumentDB is a fully open-source PostgreSQL extension that brings the MongoDB API to the SQL table. It offers MongoDB users an alternative by integrating with PostgreSQL, transforming MongoDB operators into efficient PostgreSQL access paths. Microsoft leads the development, continuously enhancing the extension based on valuable feedback from enterprise customers on Azure DocumentDB.
To demonstrate the optimization, I load 50,000 documents with 100 distinct a values and 200-byte payloads. I create an index on {a: 1} then calculate {$group: {_id: null, total: {$sum: "$a"}}}. The query has no hint, so the optimizer must decide whether it can answer the accumulator from the a_1 index.
MongoDB, DocumentDB 0.116, and DocumentDB 0.117 process the same documents, indexes, and unhinted pipelines. Each DocumentDB gateway execution is divided into setup, VACUUM (ANALYZE), and query phases. I compare rows, heap fetches, and buffers rather than elapsed time across different containers.
The feature is present in 0.117 but disabled by default. I enable only documentdb.enable_scalar_aggregate_index_pushdown. All other settings stay the same.
I initially perform the unhinted aggregation on MongoDB Atlas 8.0:
db = db.getSiblingDB("perf117");
db.scalar_group.drop();
db.scalar_group.insertMany(Array.from({length: 50000}, (_, index) => ({
_id: index + 1,
a: (index + 1) % 100,
payload: "x".repeat(200)
})));
db.scalar_group.createIndex({a: 1});
const pipeline = [
{$group: {_id: null, total: {$sum: "$a"}}}
];
print(EJSON.stringify({
result: db.scalar_group.aggregate(pipeline).toArray(),
explain: db.scalar_group.explain("executionStats").aggregate(pipeline)
}, null, 2));
{
"result": [
{
"_id": null,
"total": 2475000
}
],
"explain": {
"explainVersion": "2",
"queryPlanner": {
"namespace": "perf117.scalar_group",
"parsedQuery": {},
"indexFilterSet": false,
"queryHash": "7234A6DE",
"planCacheShapeHash": "7234A6DE",
"planCacheKey": "2A6F009E",
"optimizationTimeMillis": 0,
"optimizedPipeline": true,
"maxIndexedOrSolutionsReached": false,
"maxIndexedAndSolutionsReached": false,
"maxScansToExplodeReached": false,
"prunedSimilarIndexes": false,
"winningPlan": {
"isCached": false,
"queryPlan": {
"stage": "GROUP",
"planNodeId": 3,
"inputStage": {
"stage": "COLLSCAN",
"planNodeId": 1,
"filter": {},
"direction": "forward"
}
},
"slotBasedPlan": {
"slots": "$$RESULT=s8 env: { }",
"stages": "[3] project [s8 = newBsonObj(\"_id\", s6, \"total\", s7)] \n[3] project [s6 = null, s7 = doubleDoubleSumFinalize(s5)] \n[3] group [] [s5 = aggDoubleDoubleSum(s1)] spillSlots[s4] mergingExprs[aggMergeDoubleDoubleSums(s4)] \n[1] scan s2 s3 none none none none none none lowPriority [s1 = a] @\"a05943ac-772f-4c76-b128-e6e3bfdd0158\" true false "
}
},
"rejectedPlans": []
},
"executionStats": {
"executionSuccess": true,
"nReturned": 1,
"executionTimeMillis": 12,
"totalKeysExamined": 0,
"totalDocsExamined": 50000,
"executionStages": {
"stage": "project",
"planNodeId": 3,
"nReturned": 1,
"executionTimeMillisEstimate": 4,
"opens": 1,
"closes": 1,
"saveState": 0,
"restoreState": 0,
"isEOF": 1,
"projections": {
"8": "newBsonObj(\"_id\", s6, \"total\", s7) "
},
"inputStage": {
"stage": "project",
"planNodeId": 3,
"nReturned": 1,
"executionTimeMillisEstimate": 4,
"opens": 1,
"closes": 1,
"saveState": 0,
"restoreState": 0,
"isEOF": 1,
"projections": {
"6": "null ",
"7": "doubleDoubleSumFinalize(s5) "
},
"inputStage": {
"stage": "group",
"planNodeId": 3,
"nReturned": 1,
"executionTimeMillisEstimate": 4,
"opens": 1,
"closes": 1,
"saveState": 0,
"restoreState": 0,
"isEOF": 1,
"groupBySlots": [],
"expressions": {
"5": "aggDoubleDoubleSum(s1) ",
"initExprs": {
"5": null
}
},
"mergingExprs": {
"4": "aggMergeDoubleDoubleSums(s4) "
},
"usedDisk": false,
"spills": 0,
"spilledBytes": 0,
"spilledRecords": 0,
"spilledDataStorageSize": 0,
"inputStage": {
"stage": "scan",
"planNodeId": 1,
"nReturned": 50000,
"executionTimeMillisEstimate": 4,
"opens": 1,
"closes": 1,
"saveState": 0,
"restoreState": 0,
"isEOF": 1,
"numReads": 50000,
"recordSlot": 2,
"recordIdSlot": 3,
"scanFieldNames": [
"a"
],
"scanFieldSlots": [
1
]
}
}
}
}
},
"queryShapeHash": "F1620CAE8E90491891C0FB6F23ECD9556823F83467253A897C540C2A88AC66E5",
"command": {
"aggregate": "scalar_group",
"pipeline": [
{
"$group": {
"_id": null,
"total": {
"$sum": "$a"
}
}
}
],
"cursor": {},
"$db": "perf117"
},
"serverInfo": {
"host": "a004f7434a57",
"port": 27017,
"version": "8.0.28",
"gitVersion": "cd6fc9b3b7cf87ff2bbca0af67382ac407fc682a"
},
"serverParameters": {
"internalQueryFacetBufferSizeBytes": 104857600,
"internalQueryFacetMaxOutputDocSizeBytes": 104857600,
"internalLookupStageIntermediateDocumentMaxSizeBytes": 104857600,
"internalDocumentSourceGroupMaxMemoryBytes": 104857600,
"internalQueryMaxBlockingSortMemoryUsageBytes": 104857600,
"internalQueryProhibitBlockingMergeOnMongoS": 0,
"internalQueryMaxAddToSetBytes": 104857600,
"internalDocumentSourceSetWindowFieldsMaxMemoryBytes": 104857600,
"internalQueryFrameworkControl": "trySbeRestricted",
"internalQueryPlannerIgnoreIndexWithCollationForRegex": 1
},
"ok": 1
}
}
MongoDB performs a COLLSCAN for this unhinted scalar aggregation, scanning all 50,000 documents without using any index keys, resulting in one group. The aggregation does not spill, but the documents containing payload data are still read.
MongoDB creates index-access plans only when the index helps with filtering, sorting, or a DISTINCT_SCAN rewrite, which isn't the case here. To force an IXSCAN for this query, you need to specify the index explicitly using { hint: { a: 1 } } (this bypasses the query planner so you must ensure that the result remains unaffected, especially if using a partial index):
db.scalar_group.explain(
"executionStats"
).aggregate(
pipeline, { hint: { a: 1 } }
).queryPlanner.winningPlan
;
{
isCached: false,
queryPlan: {
stage: 'GROUP',
planNodeId: 3,
inputStage: {
stage: 'PROJECTION_COVERED',
planNodeId: 2,
transformBy: { a: true, _id: false },
inputStage: {
stage: 'IXSCAN',
planNodeId: 1,
keyPattern: { a: 1 },
indexName: 'a_1',
isMultiKey: false,
multiKeyPaths: { a: [] },
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: 'forward',
indexBounds: { a: [ '[MinKey, MaxKey]' ] }
}
}
},
slotBasedPlan: {
slots: '$$RESULT=s7 env: { }',
stages: '[3] project [s7 = newBsonObj("_id", s5, "total", s6)] \n' +
'[3] project [s5 = null, s6 = doubleDoubleSumFinalize(s4)] \n' +
'[3] group [] [s4 = aggDoubleDoubleSum(s1)] spillSlots[s3] mergingExprs[aggMergeDoubleDoubleSums(s3)] \n' +
'[1] ixseek KS(0A0104) KS(F0FE04) none s2 none none lowPriority [s1 = 0] @"10eeeb21-169c-4c72-bbb4-4a674cbf37db" @"a_1" true '
}
}
With the hint, MongoDB can use an index-only scan (IXSCAN + PROJECTION_COVERED), reading only 50,000 index entries.
I load and index the same data through the DocumentDB 0.116 gateway (MongoDB-compatible endpoint):
db = db.getSiblingDB("perf117");
db.scalar_group.drop();
db.scalar_group.insertMany(Array.from({length: 50000}, (_, index) => ({
_id: index + 1,
a: (index + 1) % 100,
payload: "x".repeat(200)
})));
db.scalar_group.createIndex(
{a: 1},
{storageEngine: {enableOrderedIndex: true}}
);
print(EJSON.stringify({
insertedDocuments: db.scalar_group.countDocuments(),
indexes: db.scalar_group.getIndexes().map(index => index.name)
}, null, 2));
{
"insertedDocuments": 50000,
"indexes": [
"_id_",
"a_1"
]
}
I run VACUUM (ANALYZE) in PostgreSQL to simulate the auto-vacuum job in a reproducible way:
VACUUM (ANALYZE)
;
Then I run the unchanged scalar aggregation:
db = db.getSiblingDB("perf117");
const pipeline = [
{$group: {_id: null, total: {$sum: "$a"}}}
];
print(EJSON.stringify({
result: db.scalar_group.aggregate(pipeline).toArray(),
explain: db.scalar_group.explain("executionStats").aggregate(pipeline)
}, null, 2));
{
"result": [
{
"_id": null,
"total": 2475000
}
],
"explain": {
"explainVersion": 2,
"command": "db.runCommand({explain: { 'aggregate': 'scalar_group', 'pipeline': [{ '$group': { '_id': null, 'total': { '$sum': '$a' } } }], 'cursor': {} }})",
"explainCommandPlanningTimeMillis": 4.189,
"explainCommandExecTimeMillis": 159.291,
"stages": [
{
"$cursor": {
"queryPlanner": {
"winningPlan": {
"stage": "COLLSCAN",
"startupCost": 0,
"totalCost": 2477,
"estimatedTotalKeysExamined": 50000
}
},
"executionStats": {
"nReturned": 50000,
"executionTimeMillis": 67.959,
"executionStartAtTimeMillis": 0.004,
"totalDocsExamined": 50000,
"totalKeysExamined": 50000,
"executionStages": {
"stage": "COLLSCAN",
"nReturned": 50000,
"executionTimeMillis": 67.959,
"executionStartAtTimeMillis": 0.004,
"totalDocsExamined": 50000,
"totalKeysExamined": 50000,
"numBlocksFromCache": 1852
}
}
}
},
{
"$root": {
"queryPlanner": {
"winningPlan": {
"stage": "GENERIC_AGGREGATE",
"startupCost": 0,
"totalCost": 2602.02,
"aggStrategy": "Sorted",
"estimatedTotalKeysExamined": 1
}
},
"executionStats": {
"nReturned": 1,
"executionTimeMillis": 159.235,
"executionStartAtTimeMillis": 159.231,
"totalDocsExamined": 1,
"totalKeysExamined": 1,
"executionStages": {
"stage": "GENERIC_AGGREGATE",
"nReturned": 1,
"executionTimeMillis": 159.235,
"executionStartAtTimeMillis": 159.231,
"totalDocsExamined": 1,
"totalKeysExamined": 1,
"numBlocksFromCache": 1852
}
}
}
}
],
"ok": 1
}
}
DocumentDB 0.116 also performs a collection scan through the gateway. It examines 50,000 documents and reports 1,852 shared-buffer hits.
I run the same setup and pipeline through the native DocumentDB API in the PostgreSQL endpoint, but explicitly disable Seq Scan to show why the Index Scan is more expensive:
\pset pager off
\set ON_ERROR_STOP on
SET search_path TO documentdb_api, documentdb_core, documentdb_api_catalog,
documentdb_api_internal, public;
SELECT extversion AS documentdb_version
FROM pg_extension
WHERE extname = 'documentdb';
SELECT documentdb_api.drop_collection('perf117', 'scalar_group');
SELECT count(documentdb_api.insert_one(
'perf117',
'scalar_group',
format(
'{"_id":%s,"a":%s,"payload":"%s"}',
i, i % 100, repeat('x', 200)
)::documentdb_core.bson,
NULL
))
FROM generate_series(1, 50000) i;
SELECT documentdb_api_internal.create_indexes_non_concurrently(
'perf117',
'{"createIndexes":"scalar_group","indexes":[{"key":{"a":1},"storageEngine":{"enableOrderedIndex":true},"name":"a_1"}]}',
true
);
SELECT collection_id
FROM documentdb_api_catalog.collections
WHERE database_name = 'perf117' AND collection_name = 'scalar_group'
\gset
VACUUM (ANALYZE) documentdb_data.documents_:collection_id;
SET enable_seqscan TO off;
SET enable_bitmapscan TO off;
EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS)
SELECT document
FROM bson_aggregation_pipeline(
'perf117',
'{"aggregate":"scalar_group","pipeline":[{"$group":{"_id":null,"total":{"$sum":"$a"}}}],"cursor":{}}'
);
...
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (actual rows=1 loops=1)
Output: bson_repath_and_build('_id'::text, 'BSONHEX070000000a0000'::bson, 'total'::text, bsonsumwithexpr(document, 'BSONHEX0e00000002000300000024610000'::bson, 'BSONHEX12000000096e6f7700876814cda001000000'::bson, NUL... (truncated)
Percona ClusterSync for MongoDB (PCSM) synchronizes data from a source MongoDB cluster (including Atlas) to a target MongoDB cluster in two stages. It first creates a consistent starting point and clones the existing collections and indexes. At the same time, it captures source changes through MongoDB change streams. Once the initial copy is complete, PCSM … Continued
The post Tuning Percona ClusterSync for MongoDB Performance appeared first on Percona.
Welcome back to Village News, our curated roundup of MySQL and database news.
If you want to get these updates, just subscribe to the blog.
Enjoy!
Note: Aggregated MySQL news can be found at Planet for MySQL Community and Planet MySQL (Oracle curated)
Editor’s Note: This article was originally authored by Inel Pandzic. Migrating data between MongoDB clusters is rarely a five-minute job. A large initial clone followed by days of change-stream replication is normal, and for all that time, Percona ClusterSync for MongoDB (PCSM) is a critical piece of your infrastructure. Until now, it was also a … Continued
The post Percona ClusterSync for MongoDB Goes Highly Available: Active-Standby Failover appeared first on Percona.