xh123637 opened a new pull request, #46046: URL: https://github.com/apache/superset/pull/46046
<!--- Please write the PR title following the conventions at https://www.conventionalcommits.org/en/v1.0.0/ Example: fix(dashboard): load charts correctly --> ### SUMMARY The Activity Log page lists logs ordered by `dttm` desc with no user filter (`LogMixin.base_order = ("dttm", "desc")`), but the only index on the `logs` table is the composite `(user_id, dttm)` from `cdcf3d64daf4`, which cannot serve an ordering on its trailing column alone. Every visit to the Activity Log page is therefore a full table scan, which is heavy on production databases where the table easily reaches a few million rows even with log pruning enabled. This PR adds a dedicated index on `logs.dttm` via a new Alembic migration (`bccfd8e7dfa2`, building on the current single head `b8f3a2c91d64`), plus a unit test covering upgrade and downgrade. Fixes #46043 ### BEFORE/AFTER SCREENSHOTS OR ANIMATED GIF Not applicable (no UI change). ### TESTING INSTRUCTIONS 1. On a database with a populated `logs` table, run `EXPLAIN QUERY PLAN SELECT * FROM logs ORDER BY dttm DESC LIMIT 25` (SQLite) or the equivalent `EXPLAIN` on Postgres/MySQL before the migration: the query performs a full table scan plus a sort. 2. Run `superset db upgrade` and repeat the query: it is served via the new `ix_logs_dttm` index with no sort step. 3. Run `superset db downgrade -1` (or the Alembic downgrade to `b8f3a2c91d64`) and confirm the index is removed. 4. Run the new unit test: `pytest tests/unit_tests/migrations/test_add_dttm_index_to_logs.py` (upgrade creates `ix_logs_dttm` on `["dttm"]`, downgrade drops it). Also run `pytest tests/unit_tests/migrations/test_single_migration_head.py` to confirm the migration chain still has exactly one head. Verified in a standalone SQLite harness (the full dev environment was not available here): new tests 2/2 pass, `ruff check` and `ruff format` clean, query plan goes from `SCAN logs` + temp B-tree to serving the `ORDER BY` via `ix_logs_dttm`, upgrade is idempotent, and downgrade removes the index. ### ADDITIONAL INFORMATION - [x] Has associated issue: #46043 - [ ] Required feature flags: - [ ] Changes UI - [x] Includes DB Migration (follow approval process in [SIP-59](https://github.com/apache/superset/issues/13351)) - [x] Migration is atomic, supports rollback & is backwards-compatible - [x] Confirm DB migration upgrade and downgrade tested - [ ] Runtime estimates and downtime expectations provided - [ ] Introduces new feature or API - [ ] Removes existing feature or API Runtime note: the migration is a single additive `CREATE INDEX` and is fully backwards-compatible; creation time scales with the size of the `logs` table, and on Postgres it takes a write lock for the duration of the build, so large deployments may want to schedule it during a quiet window. -- 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]
