mirror of
https://github.com/lrnselfreliance/wrolpi.git
synced 2026-10-25 18:40:06 -04:00
3967a49989
Cold archive/video browsing took 18-90 seconds on a Pi: file_group rows carry their indexed texts inline (SQLite has no out-of-line TOAST like Postgres), so any plan that reads file_group rows to filter or sort drags gigabytes of text pages through USB. - Browse archives with file_group as the outer table (CROSS JOIN) so SQLite walks the date index and probes archive's covering index; driving from archive probed every file_group row for the sort key. - Tie-break orders with fg.id instead of LOWER(fg.primary_path); the rowid is present in every index, the path expression forced full-row reads (and a full scan on the videos browse). - Add a (mimetype, effective_datetime) covering index so the videos mimetype filter + date order resolve index-only. - PRAGMA optimize after a global refresh; the planner had no sqlite_stat1 at all after the rebuild. Warm timings on 10.0.0.9 (124k archives / 24k videos): archive browse 0.4s -> 0.22s index-only, videos browse 0.38s -> 0.07s; cold I/O drops from ~124k random fat-page probes to a few MB of contiguous index. Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
27 lines
915 B
Python
27 lines
915 B
Python
"""Covering index for browse pages.
|
|
|
|
The videos/files browse queries filter on mimetype and order by effective_datetime; without a
|
|
covering index SQLite scans every file_group row (which carries the indexed texts inline), which
|
|
takes ~18 seconds cold on a Pi. With (mimetype, effective_datetime) the filter, count, and sort
|
|
resolve index-only.
|
|
|
|
Revision ID: 2026_07_14_0900
|
|
Revises: 2026_07_10_1350
|
|
"""
|
|
from alembic import op
|
|
|
|
# revision identifiers, used by Alembic.
|
|
revision = '2026_07_14_0900'
|
|
down_revision = '2026_07_10_1350'
|
|
branch_labels = None
|
|
depends_on = None
|
|
|
|
|
|
def upgrade():
|
|
# IF NOT EXISTS: QA boxes may have had the index created manually before this migration.
|
|
op.execute('CREATE INDEX IF NOT EXISTS file_group_mimetype_effective_idx '
|
|
'ON file_group (mimetype, effective_datetime)')
|
|
|
|
|
|
def downgrade():
|
|
op.execute('DROP INDEX IF EXISTS file_group_mimetype_effective_idx')
|