Skip to content

Run PRAGMA optimize after each static import #1971

Open

Description

@aaronbrethorst

Maglev never runs ANALYZE or PRAGMA optimize after a static import, so SQLite chooses join orders with no statistics. On a feed where one service_id covers most trips (such as NJ Transit), multi-join queries can start from the wrong table. Example: GetBlocksForBlockTripIndexIDs can start from active_bte through idx_block_trip_entry_service_id and read most active entries before it narrows to the route.

  1. Run PRAGMA optimize (or ANALYZE) once after each import. Measure how long it takes on NJ Transit and King County Metro, and compare the plans and timings of the slow endpoints (trips-for-route, block, trip-details) before and after.
  2. After that, look again at a composite index on block_trip_entry (block_id, service_id). Both queries that match on block_id also filter on service_id: GetBlocksForBlockTripIndexIDs and GetBlockTripIndexIDsForBlocks. On RABA with no statistics, GetBlockTripIndexIDsForBlocks plans through idx_block_trip_entry_service_id and does not use the block_id index from Index block_trip_entry by block_id #1688. Whether the composite index helps depends on the statistics from step 1.

Found in review of #1688.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Labels

enhancementNew feature or request

Type

No type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions