mikebridge commented on code in PR #43490: URL: https://github.com/apache/superset/pull/43490#discussion_r4008044043
########## superset/commands/deletion_retention/prune_audit.py: ########## @@ -0,0 +1,726 @@ +# Licensed to the Apache Software Foundation (ASF) under one +# or more contributor license agreements. See the NOTICE file +# distributed with this work for additional information +# regarding copyright ownership. The ASF licenses this file +# to you under the Apache License, Version 2.0 (the +# "License"); you may not use this file except in compliance +# with the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, +# software distributed under the License is distributed on an +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +# KIND, either express or implied. See the License for the +# specific language governing permissions and limitations +# under the License. +"""Deletion-only pruning of the ``purge_audit_log`` table. + +Bounds the audit history's growth without ever weakening its evidentiary +value. Three delete categories, applied in priority order under one shared +per-run batch budget: + +1. **Blocked duplicates** — within an entity's *current* blockage streak + (its ``blocked`` rows newer than the entity's newest streak-breaking + row), only the first row of each run of consecutive same-reason rows + survives: the streak's earliest row and the first row after every + change of block reason. Later same-reason repeats are removed + regardless of age. The survivors carry the "blocked since" fact and + the reason history — for coded reasons, the same rows the audit writer's + own suppression rule retains (reason-less legacy runs are additionally + collapsed to their earliest here) — and are never deleted while the + streak is current. +2. **Operational expiry** — ``blocked`` rows of *resolved* streaks and + ``failed`` rows older than ``PURGE_AUDIT_OPERATIONAL_RETENTION_DAYS`` + age out. +3. **Evidence expiry** — ``confirmed`` / ``target_absent`` rows are + untouchable unless ``PURGE_AUDIT_EVIDENCE_RETENTION_DAYS`` is + explicitly set (the operator's compliance assertion), and then only + rows older than that window. + +``pending`` rows belong to :func:`audit.reconcile_pending` and rows with +future timestamps (clock skew) are excluded from every category *and* from +streak classification, so a skewed writer cannot reclassify a live streak. + +Three invariants keep the survivor safe without a distributed lock, which +matters because runs can overlap and both ``reconcile_pending`` and the +purge path finalize rows concurrently. A duplicate is only ever deleted +when no concurrent transition could turn it into a survivor first: + +* **A boundary is never removed while it still bounds anything.** Evidence + expiry refuses to delete a row while an older ``blocked`` or ``pending`` + row for the same entity survives. Boundaries therefore never *recede*, + which would otherwise promote resolved rows into a current streak. +* **A blocked row whose classification is unstable is never deleted.** + Finalizing a ``pending`` row resolves it *in place*, keeping its original + timestamp, so an unresolved attempt is a boundary that may appear + mid-history at any moment — and the blocked row after it would become its + streak's survivor. Blocked rows preceded by an unresolved attempt are + therefore skipped by **both** the duplicate and the operational category. + Age does not stabilize the classification: an old blocked row inside a + live streak is exactly the "blocked for years" case FR-009 protects. + Without this, a boundary moving *forward* would demote the current + survivor and promote the next row into its place — possibly a row already + selected for deletion. +* **Deletes are conditional and counted from rowcounts.** A row whose + status changed since selection is not matched, so overlapping runs can + neither double-remove nor double-report. + +Boundaries moving forward past *all* of an entity's blocked rows leave no +survivor to promote, so a selected duplicate may be removed slightly ahead +of its retention window in that case. It was redundant either way and the +streak's earliest row is untouched. + +Timestamps order rows. Ties are possible — legacy second-precision rows, +two writers within one clock tick — and are resolved on the preserving +side: a ``pending`` row tied with a blocked row counts as preceding it (the +block is deferred until the attempt resolves); a blocked row tied with a +boundary sits on the boundary's *resolved* side (it ages out instead of +seeding a new current streak, and the boundary is not removed before it); +and tied same-reason blocked rows are all retained. + +Candidate selection is embedded in each ``DELETE`` statement. The derived-table +wrapper keeps that shape legal on MySQL while ensuring that a pending or +recovered row committed before the delete is evaluated participates in the +survivor and boundary predicates. No stale list of candidate ids crosses a +transaction boundary. + +Audit creation/recovery and every pruning batch take the same singleton +database write lock before assigning a timestamp or evaluating candidates. +The lock is held through commit, so an audit row cannot become visible in an +already-processed logical past. Automatic pruning still ships disabled by +default so operators explicitly choose their retention policy. +""" + +from __future__ import annotations + +import logging +from collections.abc import Callable +from dataclasses import dataclass, field +from datetime import datetime, timedelta +from functools import partial +from typing import Any, Literal, NamedTuple, TypeAlias + +import sqlalchemy as sa +from flask import current_app + +from superset import db +from superset.commands.deletion_retention.audit import ( + acquire_coordination_lock, + TRIGGER_FORCE, + utc_now, +) +from superset.models.purge_audit_log import ( + PurgeAuditLog, + STATUS_BLOCKED, + STATUS_CONFIRMED, + STATUS_FAILED, + STATUS_PENDING, + STATUS_TARGET_ABSENT, +) + +logger: logging.Logger = logging.getLogger(__name__) + +#: Operational records: noise-prone outcomes whose compliance value decays — +#: a blocked or failed purge leaves the object in place (FR-001). +OPERATIONAL_STATUSES: frozenset[str] = frozenset({STATUS_BLOCKED, STATUS_FAILED}) +#: Protected evidence: the only surviving trace of a destroyed object. +PROTECTED_STATUSES: frozenset[str] = frozenset({STATUS_CONFIRMED, STATUS_TARGET_ABSENT}) +#: The outcomes that end a blockage streak — proof the object is gone. +#: +#: ``failed`` is deliberately absent. A failed purge is an infrastructure +#: outcome (the cascade raised), not evidence the blockage cleared: the +#: policy that blocked the entity is untouched, so the blockage continues +#: across it. Treating ``failed`` as a boundary would let one transient +#: error demote the "blocked since" survivor to an ageing duplicate and +#: restate the blockage as beginning after the failure — losing exactly the +#: fact FR-003 exists to preserve. ``pending`` is provisional and likewise +#: neither joins nor breaks streaks. +_STREAK_BREAKING_STATUSES: frozenset[str] = frozenset( + {STATUS_CONFIRMED, STATUS_TARGET_ABSENT} +) + +#: Rows deleted per statement, matching the purge task's batch convention. +BATCH_SIZE: int = 500 +#: One shared budget for the whole run across all three categories; the +#: remaining backlog carries over to the next scheduled run (FR-004/SC-004). +MAX_BATCHES_PER_RUN: int = 10 + +OPERATIONAL_RETENTION_KEY: str = "PURGE_AUDIT_OPERATIONAL_RETENTION_DAYS" +EVIDENCE_RETENTION_KEY: str = "PURGE_AUDIT_EVIDENCE_RETENTION_DAYS" + + +class ResolvedWindow(NamedTuple): + """A retention window resolved from config. + + Distinguishes the three outcomes a caller must tell apart: a usable + window, the deliberate "off" default, and operator error. Collapsing + the last two into a bare ``None`` would force every caller to re-read + config to find out which it got. + """ + + days: int | None + invalid_key: str | None = None + + +def _validated_window(key: str, value: Any) -> ResolvedWindow: + """Validate a configured day count, failing closed on anything odd. + + An invalid value disables its category for the run rather than widening + removal (FR-005/SC-005). + """ + # bool is an int subclass and floats would silently truncate — both are + # config mistakes, not day counts, on a knob that deletes rows. + if not isinstance(value, bool) and not isinstance(value, float): + try: + days: int = int(value) + except (TypeError, ValueError): + days = 0 + if days > 0: + return ResolvedWindow(days) + logger.warning( + "prune_audit: invalid %s=%r; skipping this category for the run " + "(pruning never widens on bad configuration)", + key, + value, + ) + return ResolvedWindow(None, key) + + +def resolve_operational_retention_days() -> ResolvedWindow: + """The operational retention window, or a disabled window when invalid.""" + return _validated_window( + OPERATIONAL_RETENTION_KEY, current_app.config.get(OPERATIONAL_RETENTION_KEY) + ) + + +def resolve_evidence_retention_days() -> ResolvedWindow: + """The evidence expiration window; disabled unless explicitly opted in. + + Unset is the documented "never expire evidence" default (FR-006), not an + error, so it produces a disabled window with no warning. + """ + value: Any = current_app.config.get(EVIDENCE_RETENTION_KEY) + if value is None: + return ResolvedWindow(None) + return _validated_window(EVIDENCE_RETENTION_KEY, value) + + +@dataclass +class PruneRunResult: + """Per-category removal counts and run disposition for one pruning run.""" + + blocked_duplicates: int = 0 + operational_expired: int = 0 + evidence_expired: int = 0 + #: True when the shared batch budget ran out before every category's + #: candidates were drained; the remainder converges on later runs. + carried_over: bool = False + invalid_config_keys: list[str] = field(default_factory=list) + + @property + def total_removed(self) -> int: + """Total rows removed across every category.""" + return ( + self.blocked_duplicates + self.operational_expired + self.evidence_expired + ) + + def as_dict(self) -> dict[str, Any]: + """The task-return / log-line shape of this result.""" + return { + "removed": { + "blocked_duplicates": self.blocked_duplicates, + "operational_expired": self.operational_expired, + "evidence_expired": self.evidence_expired, + }, + "carried_over": self.carried_over, + "invalid_config_keys": list(self.invalid_config_keys), + } + + +def _streak_boundary_subquery(now: datetime) -> sa.Subquery: + """Per entity, the ``created_on`` of its newest streak-breaking row. + + Entities absent from this subquery have never been proven destroyed — + all their blocked rows form one current streak. Future-dated rows are + excluded so a skewed writer clock cannot push the boundary ahead of a + live streak and make its rows look resolved. + """ + table: sa.Table = PurgeAuditLog.__table__ + return ( + sa.select( + table.c.entity_type.label("entity_type"), + table.c.entity_uuid.label("entity_uuid"), + sa.func.max(table.c.created_on).label("boundary"), + ) + .where(table.c.status.in_(_STREAK_BREAKING_STATUSES)) + .where(table.c.entity_uuid.is_not(None)) + .where(table.c.created_on <= now) + .group_by(table.c.entity_type, table.c.entity_uuid) + .subquery("streak_boundary") + ) + + +def _with_boundary(table: sa.Table, boundary: sa.Subquery) -> sa.Join: + """Outer-join each row to its entity's streak boundary (NULL if none).""" + return table.outerjoin( + boundary, + sa.and_( + table.c.entity_type == boundary.c.entity_type, + table.c.entity_uuid == boundary.c.entity_uuid, + ), + ) + + +def _in_current_streak( + row: sa.FromClause, boundary: sa.Subquery +) -> sa.ColumnElement[bool]: + """Whether ``row`` is newer than its entity's boundary (or there is none). + + Strictly newer: a row tied with the boundary sits on its resolved side. + A boundary proves the object was gone at that instant, and a block that + cannot be ordered after the destruction must not seed a new "current" + streak — that would mint a survivor exempt from age-out forever for an + object that no longer exists. + """ + return sa.or_(boundary.c.boundary.is_(None), row.c.created_on > boundary.c.boundary) + + +def _repeats_an_earlier_block( + table: sa.Table, boundary: sa.Subquery +) -> sa.ColumnElement[bool]: + """Whether a same-reason current-streak block precedes this row with no + change of reason in between. + + This is the audit writer's suppression rule + (:func:`audit.finalize_retention_blocked`) applied retroactively: a + block repeating the reason of the block just before it adds nothing, + while the first block after a reason change is the only durable record + of the new cause and is retained. The comparison is NULL-safe, so + consecutive reason-less (pre-feature) rows count as one run, deduped to + the run's earliest, and the first coded block ends that run. Note this + is *stricter* than the writer for reason-less rows: the writer never + suppresses a reason-less block (``_suppress_redundant_block`` bails on a + missing code), so here the pruner additionally collapses legacy + pre-feature duplicates — the streak's earliest "blocked since" row is + still always kept. Tied same-reason rows are not "earlier" than each + other, so all of them are kept. + + A ``force`` row is exempt: it is never reported as a repeat (see the + return), so it is kept out of the duplicate category and marked a + survivor while its streak is current. It is also exempt from operational + age-out (see :func:`_operational_candidates`), so an operator force-purge + block is retained permanently — never pruned by either category. + """ + earlier: sa.FromClause = table.alias("earlier_block") + between: sa.FromClause = table.alias("reason_change") + reason_changed_between: sa.ColumnElement[bool] = sa.exists( + sa.select(sa.literal(1)) + .select_from(between) + .where( + sa.and_( + between.c.status == STATUS_BLOCKED, + between.c.entity_type == table.c.entity_type, + between.c.entity_uuid == table.c.entity_uuid, + # Inclusive bounds: a differing-reason block sharing an + # exact timestamp with either endpoint still breaks the run, + # so a reason-transition row tied with a neighbour is + # preserved as a run head rather than pruned as a repeat + # (the same preserving-side tie rule the pending and evidence + # guards use). Inclusive bounds only ever add boundaries — + # i.e. only ever preserve more, never delete more. + between.c.created_on >= earlier.c.created_on, + between.c.created_on <= table.c.created_on, + between.c.reason.is_distinct_from(table.c.reason), + ) + ) + .correlate(table, earlier) + ) + repeats: sa.ColumnElement[bool] = sa.exists( + sa.select(sa.literal(1)) + .select_from(earlier) + .where( + sa.and_( + earlier.c.status == STATUS_BLOCKED, + earlier.c.entity_type == table.c.entity_type, + earlier.c.entity_uuid == table.c.entity_uuid, + _in_current_streak(earlier, boundary), + earlier.c.created_on < table.c.created_on, + earlier.c.reason.is_not_distinct_from(table.c.reason), + sa.not_(reason_changed_between), + ) + ) + .correlate(table, boundary) + ) + # A ``force`` attempt is an operator action the writer never suppresses: + # audit.py's ``_suppress_redundant_block`` only collapses consecutive + # scheduled same-reason blocks, so the pruner does not collapse a force row + # either — it is never reported as a repeat. This keeps it out of the + # duplicate category and (via ``sa.not_`` in the operational category) marks + # it a survivor while its streak is current. It is also exempt from + # operational age-out (``_operational_candidates`` excludes force blocks), so + # a force block is retained permanently — full immortality for operator + # force-purge blocks. A force row may still be the *earlier* anchor a later + # scheduled repeat collapses into — only the force row itself is protected + # from duplicate removal. + return sa.and_(table.c.trigger != TRIGGER_FORCE, repeats) + + +def _preceded_by_unresolved_attempt(table: sa.Table) -> sa.ColumnElement[bool]: + """Whether an unresolved (``pending``) attempt precedes this row. + + A ``pending`` row is the only thing that can insert a streak boundary + into *history*: every other write lands at ``now``, newer than every + existing row, whereas reconciliation and the purge path finalize a + pending row **in place**, keeping its original ``created_on``. So a + pending row sitting between two blocked rows is a boundary that may + appear at any moment, and the blocked row after it would become the new + streak's survivor — the very row pruning must never delete. + + A tied timestamp counts as preceding. Which of the two writes landed + first is unknowable from the row, the block's classification (current + duplicate, or resolved-streak row on an age window) changes with the + attempt's outcome, and deferring it until then costs nothing. + """ + pending: sa.FromClause = table.alias("unresolved_attempt") + return sa.exists( + sa.select(sa.literal(1)) + .select_from(pending) + .where( + sa.and_( + pending.c.status == STATUS_PENDING, + pending.c.entity_type == table.c.entity_type, + pending.c.entity_uuid == table.c.entity_uuid, + pending.c.created_on <= table.c.created_on, + ) + ) + ) + + +def _duplicate_candidates(now: datetime, limit: int) -> sa.sql.Select: + """Select current-streak blocked rows that repeat the block before them. + + Age-independent by design (FR-003): a repeat is prunable the moment the + streak holds an earlier same-reason block, regardless of the retention + window. Trigger does not gate streak membership — ``scheduled`` and + ``force`` blocked rows share streaks — but a ``force`` row is itself never + collapsed as a repeat (see :func:`_repeats_an_earlier_block`). Reason *is* + a discriminator: the first block after a reason change survives alongside + the streak's earliest row. + + Rows preceded by an unresolved attempt are skipped: their classification + is not stable, because that attempt can finalize into a boundary and + promote them to survivor between selection and deletion. Such rows are + collected once the attempt resolves. Note that pruning does not depend on + that happening promptly — reconciliation runs from the purge task, which + a deployment may have disabled or left in dry-run — so a long-lived + pending row defers its successors indefinitely rather than risking them. + """ + table: sa.Table = PurgeAuditLog.__table__ + boundary: sa.Subquery = _streak_boundary_subquery(now) + return ( + sa.select(table.c.id) + .select_from(_with_boundary(table, boundary)) + .where(table.c.status == STATUS_BLOCKED) + .where(table.c.entity_uuid.is_not(None)) + .where(table.c.created_on <= now) + .where(_in_current_streak(table, boundary)) + .where(_repeats_an_earlier_block(table, boundary)) + .where(sa.not_(_preceded_by_unresolved_attempt(table))) + .order_by(table.c.created_on) + .limit(limit) Review Comment: You were right, and I measured it rather than argue it. Setup: one entity with a 6,000-row `blocked` history (5 resolved streaks + a current one, three reasons cycling in runs of 10), the real discovery → real coordination lock → real re-check, with a concurrent writer timing its own `acquire_coordination_lock`; statistics refreshed. Per 500-id batch the lock is held **~6.4 s on PostgreSQL 16** (measured acquire-to-release, median of 5) and **~50 s on MySQL 8 under REPEATABLE READ** (that one is the re-check's `EXPLAIN ANALYZE` actual time — my harness crashed before that run's acquire-to-release loop; the 50- and 100-id MySQL figures below are measured samples). REPEATABLE READ is production's effective level — the READ COMMITTED pinning in `set_db_default_isolation` is a no-op — and the writer waits the whole time. The plans below are exactly your diagnosis: `earlier_block` is probed per candidate with `reason` as a heap filter (4,875 rows removed per loop) and the nes ted `reason_change` EXISTS runs 811,370 times — O(history²) per candidate, under the lock. Two things that did not work, so you don't have to ask: 1. Adding `reason` to the pruning index: no material change (5.5 s vs 6.4 s). `IS NOT DISTINCT FROM` is not btree-seekable, so the comparison stays a filter even index-only, and the cost is the nested-EXISTS count, not heap fetches. 2. Rewriting the predicate around the immediately-preceding block: provably equivalent (all 34 behavioural tests unchanged, candidate sets row-for-row identical on the seed, a 40-seed randomised equivalence test) and ~300× faster on Postgres — but MySQL re-evaluates each reference to the correlated scalar with a full per-entity scan, and the locked re-check went from ~50 s to >10 min. Parked, not shipped. What this PR does: `PURGE_AUDIT_PRUNING_BATCH_SIZE`, default **50** (a non-boolean integer in [1, 500], fail-closed — anything else present skips the run and reports the key). It caps candidates per batch, not lock time — the locked predicate still walks each candidate's history — but on the same seed a batch of 50 holds the lock **~0.15 s on Postgres / ~1.2 s on MySQL**, versus ~0.9 s / ~7.7 s at 100 and ~6.4 s / ~50 s at 500, with the workload-dependent trade-off (more, shorter lock acquisitions; proportionally slower drain per run) documented in `config.py` and UPDATING. The rollback recipe also now requires waiting for every active pruning run to exit, not just running batches, before a writer is rolled back. The real fix is computing the preceding block once per entity set with a window function rather than per correlated reference; that is sc-120493, with ≤100 ms lock-hold on both backends at batch 500 as the acceptance criterion, using this run's harness and seed. I f you'd rather this PR block on that, say so. <details> <summary>Plans for the locked re-check, batch 500, original predicate</summary> **PostgreSQL 16 — `EXPLAIN (ANALYZE, BUFFERS)`** ``` Nested Loop Semi Join (cost=196.22..51983.18 rows=25 width=16) (actual time=13.530..6626.568 rows=500 loops=1) Buffers: shared hit=2631386 -> Nested Loop Anti Join (cost=195.94..13041.32 rows=151 width=75) (actual time=0.586..6.785 rows=500 loops=1) Join Filter: ((unresolved_attempt.created_on <= purge_audit_log.created_on) AND ((unresolved_attempt.entity_type)::text = (purge_audit_log.entity_type)::text) AND ((unresolved_attempt.entity_uuid)::text = (purge_audit_log.entity_uuid)::text)) Buffers: shared hit=2994 -> Bitmap Heap Scan on purge_audit_log (cost=195.65..13029.65 rows=168 width=75) (actual time=0.583..6.041 rows=500 loops=1) Recheck Cond: (id = ANY ('{…500 ids…}'::uuid[])) Filter: ((entity_uuid IS NOT NULL) AND (created_on <= '2026-09-14 16:58:12.052344'::timestamp without time zone) AND ((trigger)::text <> 'force'::text) AND ((status)::text = 'blocked'::text) AND (((SubPlan 1) IS NULL) OR (created_on > (SubPlan 2)))) Heap Blocks: exact=11 Buffers: shared hit=2992 -> Bitmap Index Scan on purge_audit_log_pkey (cost=0.00..194.36 rows=500 width=0) (actual time=0.544..0.545 rows=500 loops=1) Index Cond: (id = ANY ('{…500 ids…}'::uuid[])) Buffers: shared hit=123 SubPlan 1 -> Aggregate (cost=12.62..12.63 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=500) Buffers: shared hit=1465 -> Index Only Scan using ix_purge_audit_log_pruning on purge_audit_log streak_break (cost=0.29..12.62 rows=1 width=8) (actual time=0.003..0.003 rows=5 loops=500) Index Cond: ((status = ANY ('{target_absent,confirmed}'::text[])) AND (entity_type = (purge_audit_log.entity_type)::text) AND (entity_uuid IS NOT NULL) AND (entity_uuid = (purge_audit_log.entity_uuid)::text) AND (created_on <= '2026-09-14 16:58:12.052344'::timestamp without time zone)) Heap Fetches: 464 Buffers: shared hit=1465 SubPlan 2 -> Aggregate (cost=12.62..12.63 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=464) Buffers: shared hit=1393 -> Index Only Scan using ix_purge_audit_log_pruning on purge_audit_log streak_break_1 (cost=0.29..12.62 rows=1 width=8) (actual time=0.002..0.003 rows=5 loops=464) Index Cond: ((status = ANY ('{target_absent,confirmed}'::text[])) AND (entity_type = (purge_audit_log.entity_type)::text) AND (entity_uuid IS NOT NULL) AND (entity_uuid = (purge_audit_log.entity_uuid)::text) AND (created_on <= '2026-09-14 16:58:12.052344'::timestamp without time zone)) Heap Fetches: 464 Buffers: shared hit=1393 -> Materialize (cost=0.29..8.31 rows=1 width=40) (actual time=0.000..0.000 rows=0 loops=500) Buffers: shared hit=2 -> Index Only Scan using ix_purge_audit_log_pruning on purge_audit_log unresolved_attempt (cost=0.29..8.30 rows=1 width=40) (actual time=0.002..0.002 rows=0 loops=1) Index Cond: (status = 'pending'::text) Heap Fetches: 0 Buffers: shared hit=2 -> Index Scan using ix_purge_audit_log_pruning on purge_audit_log earlier_block (cost=0.29..308.67 rows=1 width=59) (actual time=13.237..13.237 rows=1 loops=500) Index Cond: (((status)::text = 'blocked'::text) AND ((entity_type)::text = (purge_audit_log.entity_type)::text) AND ((entity_uuid)::text = (purge_audit_log.entity_uuid)::text) AND (created_on < purge_audit_log.created_on)) Filter: ((NOT ((reason)::text IS DISTINCT FROM (purge_audit_log.reason)::text)) AND (NOT EXISTS(SubPlan 5)) AND (((SubPlan 3) IS NULL) OR (created_on > (SubPlan 4)))) Rows Removed by Filter: 4875 Buffers: shared hit=2628392 SubPlan 5 -> Index Scan using ix_purge_audit_log_pruning on purge_audit_log reason_change (cost=0.29..8.31 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=811370) Index Cond: (((status)::text = 'blocked'::text) AND ((entity_type)::text = (purge_audit_log.entity_type)::text) AND ((entity_uuid)::text = (purge_audit_log.entity_uuid)::text) AND (created_on >= earlier_block.created_on) AND (created_on <= purge_audit_log.created_on)) Filter: ((reason)::text IS DISTINCT FROM (purge_audit_log.reason)::text) Rows Removed by Filter: 6 Buffers: shared hit=2557264 SubPlan 3 -> Aggregate (cost=12.62..12.63 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=500) Buffers: shared hit=1465 -> Index Only Scan using ix_purge_audit_log_pruning on purge_audit_log streak_break_2 (cost=0.29..12.62 rows=1 width=8) (actual time=0.008..0.008 rows=5 loops=500) Index Cond: ((status = ANY ('{target_absent,confirmed}'::text[])) AND (entity_type = (earlier_block.entity_type)::text) AND (entity_uuid IS NOT NULL) AND (entity_uuid = (earlier_block.entity_uuid)::text) AND (created_on <= '2026-09-14 16:58:12.052344'::timestamp without time zone)) Heap Fetches: 464 Buffers: shared hit=1465 SubPlan 4 -> Aggregate (cost=12.62..12.63 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=464) Buffers: shared hit=1393 -> Index Only Scan using ix_purge_audit_log_pruning on purge_audit_log streak_break_3 (cost=0.29..12.62 rows=1 width=8) (actual time=0.002..0.003 rows=5 loops=464) Index Cond: ((status = ANY ('{target_absent,confirmed}'::text[])) AND (entity_type = (earlier_block.entity_type)::text) AND (entity_uuid IS NOT NULL) AND (entity_uuid = (earlier_block.entity_uuid)::text) AND (created_on <= '2026-09-14 16:58:12.052344'::timestamp without time zone)) Heap Fetches: 464 Buffers: shared hit=1393 Planning: Buffers: shared hit=38 Planning Time: 0.818 ms Execution Time: 6626.977 ms ``` **MySQL 8.0.44 (REPEATABLE READ) — `EXPLAIN ANALYZE` (tree format)** ``` -> Nested loop antijoin (cost=265 rows=112) (actual time=105..49670 rows=500 loops=1) -> Filter: ((purge_audit_log.`status` = 'blocked') and (purge_audit_log.id in (…500 ids…)<?7?','(??;?DI????\'1?@','l????M7?M?e?n^"','e??RS?B??FU????Y','D?cI??~ؠ?%Ws','?j?eu?@?Q??Xw8?','?ވKnNJ??gHj?[ ','@\Z????H … -> Index range scan on purge_audit_log using PRIMARY over (id = 0x0035db15bc4149aabd531a28a3c66c13) OR (id = 0x0053b24e25644ba69bffa365fbe95b63) OR (498 more) (cost=225 rows=500) (actual time=0.0545..1.23 rows=5 … -> Select #2 (subquery in condition; dependent) -> Aggregate: max(streak_break.created_on) (cost=1.45 rows=1) (actual time=0.0105..0.0105 rows=1 loops=500) -> Filter: ((streak_break.entity_type = purge_audit_log.entity_type) and (streak_break.entity_uuid = purge_audit_log.entity_uuid) and (streak_break.entity_uuid is not null) and (streak_break.`status` in ( … -> Covering index range scan on streak_break using ix_purge_audit_log_pruning over (status = 'confirmed') OR (status = 'target_absent') (cost=1.44 rows=6) (actual time=0.00408..0.00575 rows=5 loops=5 … -> Select #3 (subquery in condition; dependent) -> Aggregate: max(streak_break.created_on) (cost=1.45 rows=1) (actual time=0.0103..0.0104 rows=1 loops=464) -> Filter: ((streak_break.entity_type = purge_audit_log.entity_type) and (streak_break.entity_uuid = purge_audit_log.entity_uuid) and (streak_break.entity_uuid is not null) and (streak_break.`status` in ( … -> Covering index range scan on streak_break using ix_purge_audit_log_pruning over (status = 'confirmed') OR (status = 'target_absent') (cost=1.44 rows=6) (actual time=0.00374..0.00538 rows=5 loops=4 … -> Select #4 (subquery in condition; dependent) -> Nested loop antijoin (cost=6.73 rows=1.26) (actual time=22.4..99.3 rows=4.76 loops=500) -> Filter: ((earlier_block.`status` = 'blocked') and (earlier_block.entity_type = purge_audit_log.entity_type) and (((select #5) is null) or (earlier_block.created_on > (select #6))) and (earlier_block.cr … -> Index lookup on earlier_block using ix_purge_audit_log_entity_uuid (entity_uuid=purge_audit_log.entity_uuid) (cost=6.29 rows=25.2) (actual time=0.0051..4.1 rows=5574 loops=500) -> Select #5 (subquery in condition; dependent) -> Aggregate: max(streak_break.created_on) (cost=1.45 rows=1) (actual time=0.00607..0.00609 rows=1 loops=2.78e+6) -> Filter: ((streak_break.entity_type = earlier_block.entity_type) and (streak_break.entity_uuid = earlier_block.entity_uuid) and (streak_break.entity_uuid is not null) and (streak_break.`stat … -> Covering index range scan on streak_break using ix_purge_audit_log_pruning over (status = 'confirmed') OR (status = 'target_absent') (cost=1.44 rows=6) (actual time=0.00216..0.00365 ro … -> Select #6 (subquery in condition; dependent) -> Aggregate: max(streak_break.created_on) (cost=1.45 rows=1) (actual time=0.00602..0.00603 rows=1 loops=2.78e+6) -> Filter: ((streak_break.entity_type = earlier_block.entity_type) and (streak_break.entity_uuid = earlier_block.entity_uuid) and (streak_break.entity_uuid is not null) and (streak_break.`stat … -> Covering index range scan on streak_break using ix_purge_audit_log_pruning over (status = 'confirmed') OR (status = 'target_absent') (cost=1.44 rows=6) (actual time=0.00212..0.0036 row … -> Filter: ((reason_change.`status` = 'blocked') and (reason_change.created_on >= earlier_block.created_on) and (reason_change.created_on <= purge_audit_log.created_on) and (not((reason_change.reason <=> … -> Index lookup on reason_change using ix_purge_audit_log_entity_uuid (entity_uuid=purge_audit_log.entity_uuid) (cost=1424 rows=25.2) (actual time=0.00383..0.222 rows=408 loops=39952) -> Filter: ((unresolved_attempt.entity_uuid = purge_audit_log.entity_uuid) and (unresolved_attempt.entity_type = purge_audit_log.entity_type) and (unresolved_attempt.created_on <= purge_audit_log.created_on)) (cost= … -> Index lookup on unresolved_attempt using ix_purge_audit_log_status_created_on (status='pending') (cost=0.251 rows=1) (actual time=0.00342..0.00342 rows=0 loops=500) ``` </details> -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
