Title: perf(file-search): probe each backfill page's revisions by primary key by waleedlatif1 · Pull Request #9445 · simstudioai/sim · GitHub
Open Graph Title: perf(file-search): probe each backfill page's revisions by primary key by waleedlatif1 · Pull Request #9445 · simstudioai/sim
X Title: perf(file-search): probe each backfill page's revisions by primary key by waleedlatif1 · Pull Request #9445 · simstudioai/sim
Description: Summary The file-search backfill walk checked each file for a revision with a correlated EXISTS in its select list. PostgreSQL plans that as an alternative subplan and chooses the hashed form from the scan's row estimate, before the page LIMIT applies, so every page sequentially scanned and hashed the whole workspace_file_search_revision table to answer a thousand lookups The page now LEFT JOINs the revision on its primary key (file_id = workspace_files.id) and reads hasRevision as the joined key being non-null. Under the limit the planner nests the join over the existing ordered keyset scan: one primary-key probe per file returned file_id is the revision's primary key, so the join cannot duplicate a file; the keyset predicate, ORDER BY and LIMIT are byte-for-byte unchanged, as are the lock-and-reread of unindexed files, the insert, the owner wakeups and the cursor update Plain EXPLAIN and replica EXPLAIN (ANALYZE, BUFFERS) of both forms with a real mid-walk cursor: old plan keeps the keyset index scan plus a hashed SubPlan over a Seq Scan of the revision table (~500ms per page); new plan is Limit -> Nested Loop Left Join -> keyset Index Scan + Index Only Scan on workspace_file_search_revision_pkey (~12ms warm), same plan for the first page and a cursor near the end of the walk Type of Change Other: Performance Testing New dispatcher.integration.ts case walks a second backfill page over a settled table (no planner overrides) and asserts the revision table is read only through workspace_file_search_revision_pkey with Index Cond: (file_id = workspace_files.id); it fails on the previous query with exactly the hashed Seq Scan subplan Existing cases still cover hasRevision both ways and the walk semantics: only files missing a revision are seeded and locked, every live file is walked exactly once across pages, and the cursor seeks the keyset index lib/workspace-files/search/ integration (109 passed) and unit (178 passed) suites against Postgres 17, bun run type-check (apps/sim), bun run lint, bun run check:audits, bun run check:test-types, block-registry check Checklist Code follows project style guidelines Self-reviewed my changes Tests added/updated and passing (new tests pass the test-audit authoring gate) 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 The file-search backfill walk checked each file for a revision with a correlated EXISTS in its select list. PostgreSQL plans that as an alternative subplan and chooses the hashed form from...
X Description: Summary The file-search backfill walk checked each file for a revision with a correlated EXISTS in its select list. PostgreSQL plans that as an alternative subplan and chooses the hashed form from...
Opengraph URL: https://github.com/simstudioai/sim/pull/9445
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:d93fd021-1d15-b001-6c89-32efe04a5bd2 |
| current-catalog-service-hash | ae870bc5e265a340912cde392f23dad3671a0a881730ffdadd82f2f57d81641b |
| request-id | C646:32180C:2787B8:3AA8AD:6AFB217F |
| html-safe-nonce | 5c88182194b3f63f199527dac47fa81cf378cfade79d5be9794995d5a2c7c09b |
| visitor-payload | eyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiJDNjQ2OjMyMTgwQzoyNzg3Qjg6M0FBOEFEOjZBRkIyMTdGIiwidmlzaXRvcl9pZCI6IjU4ODcyODI3MTI5MDQ2NzE2MTUiLCJyZWdpb25fZWRnZSI6ImlhZCIsInJlZ2lvbl9yZW5kZXIiOiJpYWQifQ== |
| visitor-hmac | be1905be96e80e284a474c3c79f0507f0f2138c1683876ccb33aee1f692051d2 |
| hovercard-subject-tag | pull_request:5073238784 |
| 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/9445/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 The file-search backfill walk checked each file for a revision with a correlated EXISTS in its select list. PostgreSQL plans that as an alternative subplan and chooses the hashed form from... |
| og:site_name | GitHub |
| og:type | object |
| hostname | github.com |
| expected-hostname | github.com |
| None | f9219899e0d0ce0b87aeba2c640c955f83631457e5baff5df7a36ce599d2635b |
| 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 | dd3b60f1fd9c5f10a3dcde41ae35682f53e85cee |
| ui-target | full |
| theme-color | #1e2327 |
| color-scheme | light dark |
Links:
Viewport: width=device-width