b80feb7176
Add 10 indexes across prices, etf_prices, financials, valuations, holdings, transactions, signals, portfolio_snapshots, and etfs tables. Fix N+1 query in list_backtests by eager-loading backtest results with joinedload. Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
50 lines
2.2 KiB
Python
50 lines
2.2 KiB
Python
"""add performance indexes
|
|
|
|
Revision ID: b7c8d9e0f1a2
|
|
Revises: 606a5011f84f
|
|
Create Date: 2026-03-18 22:00:00.000000
|
|
|
|
"""
|
|
from typing import Sequence, Union
|
|
|
|
from alembic import op
|
|
import sqlalchemy as sa
|
|
|
|
|
|
# revision identifiers, used by Alembic.
|
|
revision: str = 'b7c8d9e0f1a2'
|
|
down_revision: Union[str, None] = '606a5011f84f'
|
|
branch_labels: Union[str, Sequence[str], None] = None
|
|
depends_on: Union[str, Sequence[str], None] = None
|
|
|
|
|
|
def upgrade() -> None:
|
|
# Tier 1: backtest/strategy performance
|
|
op.create_index('idx_prices_ticker_date', 'prices', ['ticker', sa.text('date DESC')])
|
|
op.create_index('idx_etf_prices_ticker_date', 'etf_prices', ['ticker', sa.text('date DESC')])
|
|
op.create_index('idx_financials_ticker_base_date', 'financials', ['ticker', sa.text('base_date DESC')])
|
|
op.create_index('idx_valuations_ticker_base_date', 'valuations', ['ticker', sa.text('base_date DESC')])
|
|
|
|
# Tier 2: portfolio queries
|
|
op.create_index('idx_holdings_portfolio_id', 'holdings', ['portfolio_id'])
|
|
op.create_index('idx_transactions_portfolio_id_executed_at', 'transactions', ['portfolio_id', sa.text('executed_at DESC')])
|
|
op.create_index('idx_signals_date_status', 'signals', [sa.text('date DESC'), 'status'])
|
|
op.create_index('idx_snapshots_portfolio_date', 'portfolio_snapshots', ['portfolio_id', sa.text('snapshot_date DESC')])
|
|
|
|
# Tier 3: ETF filters
|
|
op.create_index('idx_etf_asset_class', 'etfs', ['asset_class'])
|
|
op.create_index('idx_etf_price_date', 'etf_prices', [sa.text('date DESC')])
|
|
|
|
|
|
def downgrade() -> None:
|
|
op.drop_index('idx_etf_price_date', table_name='etf_prices')
|
|
op.drop_index('idx_etf_asset_class', table_name='etfs')
|
|
op.drop_index('idx_snapshots_portfolio_date', table_name='portfolio_snapshots')
|
|
op.drop_index('idx_signals_date_status', table_name='signals')
|
|
op.drop_index('idx_transactions_portfolio_id_executed_at', table_name='transactions')
|
|
op.drop_index('idx_holdings_portfolio_id', table_name='holdings')
|
|
op.drop_index('idx_valuations_ticker_base_date', table_name='valuations')
|
|
op.drop_index('idx_financials_ticker_base_date', table_name='financials')
|
|
op.drop_index('idx_etf_prices_ticker_date', table_name='etf_prices')
|
|
op.drop_index('idx_prices_ticker_date', table_name='prices')
|