aminghadersohi opened a new pull request, #44349:
URL: https://github.com/apache/superset/pull/44349
### SUMMARY
Builds on #44262 (open, not yet merged — this branch includes its commit as a
prerequisite baseline, so the diff below contains both). #44262 rewrote the
purge-audit "repeat block" candidacy check from a correlated nested-EXISTS
predicate to a window-function form, computing five `LAG(...) OVER (...)`
columns (timestamp, count, coded-count, min/max reason) over the batch's
blocked-timestamp groups. All five share an identical
`PARTITION BY (entity_type, entity_uuid) ORDER BY ts` spec.
PostgreSQL recognizes the repeated window spec and evaluates it once, but
MySQL 8 does not merge the five inline specs — each materializes its own
temporary table, so the query pays for roughly five sequential passes over
the batch's timestamp groups instead of one.
This PR replaces the five inline `OVER (...)` clauses with one SQL-level
named `WINDOW w AS (...)` clause, referenced by all five `LAG` columns
(SQLAlchemy Core has no construct for a named `WINDOW` clause, so this is a
small raw-text fragment — the query otherwise remains fully within Core).
MySQL now evaluates the window once; PostgreSQL's plan is unaffected since
the join structure and predicates are unchanged.
An alternative was also tried and rejected: `LAG` only the previous
timestamp (one window column) and fetch its aggregates via an equality join
back to a second instance of the groups derived table. Measured on
PostgreSQL with a single-entity batch scope, the planner underestimates that
second derived table's row count and picks a Nested Loop over an unindexed
`Materialize` of it — an O(batch × history) comparison, ~10x slower than the
baseline in testing. This reproduces the same failure shape as a prior
groups-to-groups join regression from #44262's own history, just via a
different join path, so it was dropped in favor of the named-`WINDOW` form,
which keeps the already-safe join structure entirely unchanged.
### TESTING INSTRUCTIONS
- `pytest tests/unit_tests/commands/deletion_retention/test_prune_audit.py`
— 66 passed, unchanged from #44262 (no test edits in this PR).
- `pytest tests/integration_tests/deletion_retention/prune_audit_tests.py`
— 35 passed (34 passed + 1 skipped on SQLite, which is expected: that test
is MySQL/PostgreSQL-only), including the 40-seed randomized equivalence
test, run against SQLite, a real MySQL 8 container, and a real
PostgreSQL 17 container.
- Ad-hoc timing comparison (not an in-repo script; a one-off harness run
against local Docker containers, five reps, median reported), seeding one
entity with 6,000 blocked rows, `ANALYZE`, MySQL forced to
`REPEATABLE READ`, timing the locked-recheck SELECT shape used by
`_delete_batch`:
- MySQL 8, batch 500: five-pass baseline ~170–236ms → one-pass ~85–127ms
(meets the ≤100ms target).
- MySQL 8, batch 50: ~156–175ms → ~67–94ms.
- PostgreSQL 17, batch 500: ~382–427ms → ~375–415ms (no regression;
within run-to-run noise).
- PostgreSQL 17, batch 50: ~67–71ms → ~68–69ms (no regression).
These PostgreSQL absolute numbers are higher than in prior discussion on
this predicate, which likely reflects this measurement environment/seed
shape rather than this change — the baseline (`git stash` this PR's commit,
keep #44262's) shows the same elevated numbers on the same setup, so the
before/after comparison is apples-to-apples even though the absolute
figures differ from the earlier report.
### ADDITIONAL INFORMATION
- [ ] Has associated issue:
- [ ] Required feature flags:
- [ ] Changes UI
- [ ] Includes DB Migration (follow approval process in
[SIP-59](https://github.com/apache/superset/issues/13351))
- [ ] Migration is atomic, supports rollback & is backwards-compatible
- [ ] Confirm DB migration upgrade and downgrade tested
- [ ] Runtime estimates and downtime expectations provided
- [ ] Introduces new feature or API
- [ ] Removes existing feature or API
--
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]
