perf: use UNION ALL with DISTINCT for bookings query optimization by keithwillcode · Pull Request #27841 · calcom/cal.com

keithwillcode · 2026-02-10T19:01:07Z

What does this PR do?

Optimizes the slowest database query in the bookings list endpoint (getBookings) by changing UNION → UNION ALL and adding SELECT DISTINCT on the outer query. This was validated via query plan analysis to yield a ~40% execution time reduction.

Changes:

acc.union(query) → acc.unionAll(query) — stops the DB from deduplicating within each union step
Added .distinct() on the outer select — deduplicates once at the end instead
Changed fn.countAll() → fn.count("union_subquery.id").distinct() — ensures totalCount still returns the correct deduplicated count
Added integration tests (get.handler.integration-test.ts) covering the UNION query and totalCount correctness
Updated unit test mocks to include unionAll and distinct methods

Integration tests

Seven integration tests run getBookings against a real database with freshly created users, teams, bookings, and attendees:

Test	What it verifies
bookings created by user	User's own bookings appear in results
bookings where user is attendee	Bookings where user is an attendee (not creator) are returned
no duplicate bookings	Bookings matching multiple union branches are not duplicated
correct totalCount	All 4 expected booking IDs are verified present and `totalCount === bookings.length` (exact match, not `>=`)
booking4 counted once	A booking deliberately hitting multiple union branches (creator + attendee + team event) appears exactly once and totalCount matches
pagination ordering	`take/skip` pagination returns distinct pages in correct `startTime` order
cross-user visibility	user2 sees bookings they created and bookings where they are an attendee

Tests were verified passing on both main (with UNION) and this branch (with UNION ALL + DISTINCT).

Mandatory Tasks (DO NOT REMOVE)

I have self-reviewed the code (A decent size PR without self-review might be rejected).
I have updated the developer docs in /docs if this PR makes changes that would require a documentation change. N/A — no docs changes needed.
I confirm automated tests are in place that prove my fix is effective or that my feature works.

How should this be tested?

Integration tests (recommended):

TZ=UTC VITEST_MODE=integration yarn vitest run packages/trpc/server/routers/viewer/bookings/get.handler.integration-test.ts

Unit tests:

TZ=UTC yarn vitest run packages/trpc/server/routers/viewer/bookings/get.handler.test.ts

Manual SQL verification:
Run EXPLAIN ANALYZE on the generated SQL before and after on a database with a non-trivial number of bookings and confirm:

The generated SQL uses UNION ALL between subqueries (not UNION)
The outer query uses SELECT DISTINCT
The count query uses COUNT(DISTINCT "union_subquery"."id")
Results and pagination remain identical

Human Review Checklist

Verify fn.count("union_subquery.id").distinct() generates COUNT(DISTINCT "union_subquery"."id") in the compiled SQL (not e.g. COUNT(DISTINCT *))
Confirm DISTINCT + ORDER BY + LIMIT/OFFSET interaction on the outer query doesn't affect result ordering or pagination
Confirm totalCount value is unchanged (same deduplicated count as before)
Review integration test assertions — especially that totalCount === bookings.length is sufficient to catch count bugs (test uses take: 50 with only 4 bookings, so all fit in one page)
Verify booking4 test scenario actually hits multiple union branches (user is creator, user is attendee, team event type)

Link to Devin run: https://app.devin.ai/sessions/65321ec1ee2d42ddb7d38e3b47144f87
Requested by: @keithwillcode

Co-Authored-By: Devin AI <158243242+devin-ai-integration[bot]@users.noreply.github.com>

devin-ai-integration · 2026-02-10T19:01:11Z

🤖 Devin AI Engineer

I'll be helping with this pull request! Here's what you should know:

✅ I will automatically:

Address comments on this PR that start with 'DevinAI' or '@devin'.
Look at CI failures and help fix them

Note: I can only respond to comments from users who have write access to this repository.

⚙️ Control Options:

Disable automatic comment and CI monitoring

cubic-dev-ai

No issues found across 1 file

Co-Authored-By: Devin AI <158243242+devin-ai-integration[bot]@users.noreply.github.com>

github-actions · 2026-02-10T19:47:06Z

E2E results are ready!

Workflow #104082.1 latest results

keithwillcode · 2026-02-10T19:48:01Z

@paragon-evolve

packages/trpc/server/routers/viewer/bookings/get.handler.ts

cubic-dev-ai

1 issue found across 1 file (changes from recent commits).

Prompt for AI agents (all issues)


Check if these issues are valid — if so, understand the root cause of each and fix them.


<file name="packages/trpc/server/routers/viewer/bookings/get.handler.ts">

<violation number="1">
P1: `countAll()` will over-count duplicates from the UNION ALL subquery. Use `COUNT(DISTINCT union_subquery.id)` so totalCount matches the deduplicated list.</violation>
</file>

_{Reply with feedback, questions, or to request a fix. Tag @cubic-dev-ai to re-run a review.}

packages/trpc/server/routers/viewer/bookings/get.handler.ts

github-actions · 2026-02-10T19:57:27Z

Devin AI is addressing Cubic AI's review feedback

A Devin session has been created to address the issues identified by Cubic AI.

View Devin Session

Co-Authored-By: Devin AI <158243242+devin-ai-integration[bot]@users.noreply.github.com>

paragon-review · 2026-02-10T20:03:55Z

Paragon: tests updated

2 updated tests generated for this PR.

Updated Tests

getBookings - integration tests for UNION ALL with DISTINCT optimization — Integration tests for the UNION ALL + DISTINCT bookings query optimization. Tests: user-created bookings returned, attendee bookings returned, no duplicates when user matches multiple union branches, totalCount matches actual unique count, booking4 (multi-branch match) counted exactly once, pagination ordering correctness, cross-user booking visibility.
getBookings - PBAC Permission Checks - updated mocks + UNION ALL optimization verification — Adding new test to existing file

Details

Updated Tests

getBookings - integration tests for UNION ALL with DISTINCT optimization (unit)
getBookings - PBAC Permission Checks - updated mocks + UNION ALL optimization verification (unit)

…ntegration-test.ts Generated by Paragon from proposal for PR #27841

…est.ts Generated by Paragon from proposal for PR #27841

keithwillcode · 2026-02-10T20:38:28Z

Tests Added by Paragon

The following test files have been added to this PR:

packages/trpc/server/routers/viewer/bookings/get.handler.integration-test.ts
packages/trpc/server/routers/viewer/bookings/get.handler.test.ts

These tests were generated from an approved test proposal.

Generated with Paragon

volnei

🚀

volnei

🚀

perf: use UNION ALL with DISTINCT for bookings query optimization

9dfb92f

Co-Authored-By: Devin AI <158243242+devin-ai-integration[bot]@users.noreply.github.com>

pull-request-size bot added the size/XS label Feb 10, 2026

cubic-dev-ai bot reviewed Feb 10, 2026

View reviewed changes

test: add unionAll and distinct to kysely test mocks

3640605

Co-Authored-By: Devin AI <158243242+devin-ai-integration[bot]@users.noreply.github.com>

pull-request-size bot added size/S and removed size/XS labels Feb 10, 2026

keithwillcode self-assigned this Feb 10, 2026

keithwillcode added this to the v6.2 milestone Feb 10, 2026

keithwillcode added performance area: performance, page load, slow, slow endpoints, loading screen, unresponsive ready-for-e2e labels Feb 10, 2026

test: add integration tests for getBookings query

c4f9811

Co-Authored-By: Devin AI <158243242+devin-ai-integration[bot]@users.noreply.github.com>

pull-request-size bot added size/L and removed size/S labels Feb 10, 2026

keithwillcode and others added 3 commits February 10, 2026 19:22

test: add totalCount deduplication test for multi-branch union scenario

c792804

Co-Authored-By: Devin AI <158243242+devin-ai-integration[bot]@users.noreply.github.com>

test: fix totalCount assertion to be exact instead of gte

f017e41

Co-Authored-By: Devin AI <158243242+devin-ai-integration[bot]@users.noreply.github.com>

Merge branch 'main' into devin/1770749704-union-all-distinct

b2806e4

keithwillcode commented Feb 10, 2026

View reviewed changes

packages/trpc/server/routers/viewer/bookings/get.handler.ts Show resolved Hide resolved

Apply suggestion from @keithwillcode

c925b6a

cubic-dev-ai bot reviewed Feb 10, 2026

View reviewed changes

packages/trpc/server/routers/viewer/bookings/get.handler.ts Show resolved Hide resolved

perf: restore COUNT(DISTINCT id) for totalCount query

eb9eb2d

Co-Authored-By: Devin AI <158243242+devin-ai-integration[bot]@users.noreply.github.com>

keithwillcode added 2 commits February 10, 2026 17:38

Add tests: packages/trpc/server/routers/viewer/bookings/get.handler.i…

969963b

…ntegration-test.ts Generated by Paragon from proposal for PR #27841

Add tests: packages/trpc/server/routers/viewer/bookings/get.handler.t…

e596a4e

…est.ts Generated by Paragon from proposal for PR #27841

keithwillcode added 2 commits February 10, 2026 19:07

Merge branch 'main' into devin/1770749704-union-all-distinct

a564414

Merge branch 'main' into devin/1770749704-union-all-distinct

c939444

keithwillcode marked this pull request as ready for review February 11, 2026 13:58

keithwillcode requested a review from a team as a code owner February 11, 2026 13:58

keithwillcode enabled auto-merge (squash) February 11, 2026 13:58

graphite-app bot added core area: core, team members only foundation labels Feb 11, 2026

volnei reviewed Feb 11, 2026

View reviewed changes

volnei approved these changes Feb 11, 2026

View reviewed changes

keithwillcode merged commit bc8ebb9 into main Feb 11, 2026
59 checks passed

keithwillcode deleted the devin/1770749704-union-all-distinct branch February 11, 2026 14:33

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

perf: use UNION ALL with DISTINCT for bookings query optimization#27841

perf: use UNION ALL with DISTINCT for bookings query optimization#27841
keithwillcode merged 12 commits intomainfrom
devin/1770749704-union-all-distinct

keithwillcode commented Feb 10, 2026 •

edited

Loading

Uh oh!

devin-ai-integration bot commented Feb 10, 2026

Uh oh!

cubic-dev-ai bot left a comment

Uh oh!

github-actions bot commented Feb 10, 2026 •

edited

Loading

Uh oh!

keithwillcode commented Feb 10, 2026

Uh oh!

Uh oh!

cubic-dev-ai bot left a comment

Uh oh!

Uh oh!

github-actions bot commented Feb 10, 2026

Uh oh!

paragon-review bot commented Feb 10, 2026

Updated Tests

Uh oh!

keithwillcode commented Feb 10, 2026

Uh oh!

volnei left a comment

Uh oh!

volnei left a comment

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

Conversation

keithwillcode commented Feb 10, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

What does this PR do?

Integration tests

Mandatory Tasks (DO NOT REMOVE)

How should this be tested?

Human Review Checklist

Uh oh!

devin-ai-integration bot commented Feb 10, 2026

🤖 Devin AI Engineer

Uh oh!

cubic-dev-ai bot left a comment

Choose a reason for hiding this comment

Uh oh!

github-actions bot commented Feb 10, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

E2E results are ready!

Uh oh!

keithwillcode commented Feb 10, 2026

Uh oh!

Uh oh!

cubic-dev-ai bot left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

github-actions bot commented Feb 10, 2026

Devin AI is addressing Cubic AI's review feedback

Uh oh!

paragon-review bot commented Feb 10, 2026

Paragon: tests updated

Updated Tests

Uh oh!

keithwillcode commented Feb 10, 2026

Tests Added by Paragon

Uh oh!

volnei left a comment

Choose a reason for hiding this comment

Uh oh!

volnei left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

keithwillcode commented Feb 10, 2026 •

edited

Loading

github-actions bot commented Feb 10, 2026 •

edited

Loading