Title: improvement(db): drop unusable date slot and redundant prefix indexes by waleedlatif1 · Pull Request #8332 · simstudioai/sim · GitHub
Open Graph Title: improvement(db): drop unusable date slot and redundant prefix indexes by waleedlatif1 · Pull Request #8332 · simstudioai/sim
X Title: improvement(db): drop unusable date slot and redundant prefix indexes by waleedlatif1 · Pull Request #8332 · simstudioai/sim
Description: Summary Drops 8 indexes on hot write tables in one DROP INDEX CONCURRENTLY migration (0386), following the 0239 pattern: COMMIT breakpoint, lock_timeout 0, idempotent replay {emb,doc}_date1_idx / date2_idx: tag filters compare col::date, which a plain timestamp btree can never match, so these are written on every chunk insert and document update and never serve a query Four left-prefix duplicates of an existing non-partial superset, which keeps serving equality lookups and FK cascades on the leading column: emb_kb_id_idx: covered by emb_kb_enabled_idx / emb_kb_model_idx emb_doc_id_idx: covered by emb_doc_chunk_idx / emb_doc_enabled_idx usage_log_workspace_id_idx: covered by usage_log_workspace_created_at_idx copilot_runs_execution_id_idx: covered by copilot_runs_execution_started_at_idx Kept on purpose: Every number and boolean tag-slot index. The vector leg's short-result rescue probe checks tag filters with an EXISTS over embedding that relies on them, and without them a selective number/boolean filter turns that probe into a table-wide scan The small copilot_runs chat/workspace indexes Type of Change Improvement Testing Knowledge search A/B (real retrieveKnowledgeSearch, 58 shapes, 732k-chunk multi-tenant fixture, EXPLAIN ANALYZE BUFFERS per statement): with the prefix and date drops, every shape keeps its plan and latency within noise, and results are identical. Plans move to the covering composites. The same run showed that dropping the number/boolean slot indexes regresses filtered vector search, so those are kept Every app query and FK cascade that used a dropped prefix index (including the live document → embedding cascade and batched document_id = ANY(...)) stays an index scan on the covering composite with the same leading column as Index Cond Fresh DB: migrate plus replay succeed, the 8 indexes are gone, and the kept slot indexes remain. drizzle-kit generate reports no drift, and check:migrations origin/staging passes Knowledge search suites (kb-block-search, filtered-search, workspace-kb-document-access, search-index-policy) pass identically before and after Checklist Code follows project style guidelines Self-reviewed my changes Tests added/updated and passing No new warnings introduced I confirm that I have read and agree to the terms outlined in the Contributor License Agreement (CLA)
Open Graph Description: Summary Drops 8 indexes on hot write tables in one DROP INDEX CONCURRENTLY migration (0386), following the 0239 pattern: COMMIT breakpoint, lock_timeout 0, idempotent replay {emb,doc}_date1_idx / ...
X Description: Summary Drops 8 indexes on hot write tables in one DROP INDEX CONCURRENTLY migration (0386), following the 0239 pattern: COMMIT breakpoint, lock_timeout 0, idempotent replay {emb,doc}_date1_idx / ...
Opengraph URL: https://github.com/simstudioai/sim/pull/8332
X: @github
Domain: github.com
| react-profiling | 0 |
| react-import-map | react,react-dom,react-dom/client,react-dom/profiling,react-is,react-reconciler,react/compiler-runtime,react/jsx-dev-runtime,react/jsx-runtime,scheduler@d5750093facf |
| route-pattern | /:user_id/:repository/pull/:id/files(.:format) |
| route-controller | pull_requests |
| route-action | files |
| fetch-nonce | v2:d45f68d9-273f-70b0-45c8-df3bbe61d930 |
| current-catalog-service-hash | ae870bc5e265a340912cde392f23dad3671a0a881730ffdadd82f2f57d81641b |
| request-id | AEF4:1FFEE4:8E54D9:CA1FB3:6AFB5085 |
| html-safe-nonce | 7c9d6474890649618dc32a398677e51d673cde866af5c986706c8ae7c867c179 |
| visitor-payload | eyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiJBRUY0OjFGRkVFNDo4RTU0RDk6Q0ExRkIzOjZBRkI1MDg1IiwidmlzaXRvcl9pZCI6IjYxNzczNzk1MjI5Mjk0NDcwNDYiLCJyZWdpb25fZWRnZSI6ImlhZCIsInJlZ2lvbl9yZW5kZXIiOiJpYWQifQ== |
| visitor-hmac | 297f52cb24509b131f02d9f714444277ac652ae90c521971d6e426405a0530c9 |
| hovercard-subject-tag | pull_request:4648631198 |
| github-keyboard-shortcuts | repository,pull-request-list,pull-request-conversation,pull-request-files-changed,copilot |
| google-site-verification | Apib7-x98H0j5cPqHWwSMm6dNU4GmODRoqxLiDzdx9I |
| octolytics-url | https://collector.github.com/github/collect |
| analytics-location | / |
| fb:app_id | 1401488693436528 |
| apple-itunes-app | app-id=1477376905, app-argument=https://github.com/simstudioai/sim/pull/8332/files |
| twitter:image | https://avatars.githubusercontent.com/u/40672544?s=400&v=4 |
| twitter:card | summary_large_image |
| og:image | https://avatars.githubusercontent.com/u/40672544?s=400&v=4 |
| og:image:alt | Summary Drops 8 indexes on hot write tables in one DROP INDEX CONCURRENTLY migration (0386), following the 0239 pattern: COMMIT breakpoint, lock_timeout 0, idempotent replay {emb,doc}_date1_idx / ... |
| og:site_name | GitHub |
| og:type | object |
| hostname | github.com |
| expected-hostname | github.com |
| None | 7122bb414b542dba14d71a3f0b9f6fd2abce715b9f5912d082800563e6c806d1 |
| turbo-cache-control | no-preview |
| diff-view | unified |
| go-import | github.com/simstudioai/sim git https://github.com/simstudioai/sim.git |
| octolytics-dimension-user_id | 199344406 |
| octolytics-dimension-user_login | simstudioai |
| octolytics-dimension-repository_id | 912559512 |
| octolytics-dimension-repository_nwo | simstudioai/sim |
| octolytics-dimension-repository_public | true |
| octolytics-dimension-repository_is_fork | false |
| octolytics-dimension-repository_network_root_id | 912559512 |
| octolytics-dimension-repository_network_root_nwo | simstudioai/sim |
| turbo-body-classes | logged-out env-production page-responsive full-width |
| disable-turbo | true |
| browser-stats-url | https://api.github.com/_private/browser/stats |
| browser-errors-url | https://api.github.com/_private/browser/errors |
| release | cbf66c024bb6d10e6955baa7413c54cf13a201bb |
| ui-target | full |
| theme-color | #1e2327 |
| color-scheme | light dark |
Links:
Viewport: width=device-width