The analytics page was borderline unusably slow, and now itās not. How did we achieve that?
Non-technical TL;DR: We added indexes to the database. The difference this makes is comparable to the difference it makes for you to find a specific bit of information in a book with vs without an index.
Technical TL;DR: We added indexes to the database.
Preliminaries
The data which analytics are run on are stored in a relational database, specifically Postgres.
Relational databases (henceforth referred to as just databases) use something called indexes to enable it to search for requested data within the database. Loosely speaking, an index is a way to trade CPU time for storage space (the cost of an index is mainly in the space required to store it, but there are some other more obscure costs.)
An index you may already be familiar with is the primary key (otherwise known as just ID). This is why it is always fast to look up rows by their IDs.
Analysing the analytics databaseās performance
When investigating poor query performance in a database, itās important to identify the root (or at least primary) cause of the issue. In general, there are many things which could be causing slowness.
EXPLAIN
Most database engines (that Iām aware of) provide commands which are not part of the SQL standard to assist with exactly the issue weāre facing. In Postgres, itās called EXPLAIN. EXPLAIN can be placed before any operation that is run on the database, and instead of actually running the operation, the database engine will tell you how it would run the operation (at least in theory, there are some factors which can lead to an operation being performed differently to what was said in the output of EXPLAIN. Those factors are outside the scope of this post.)
The output of EXPLAIN is called a query plan. By inspecting the query plan we can identify what the problematic parts of an operation are.
Before we get to looking at the query plans, letās have a look at what the slowest query was.
Slowest query
The slowest queries (as identified through GCPās query insights tool) had the following structure:
WITH "uid0_PartyEndedEvent_metrics" AS (
SELECT
SUM(
CASE
WHEN "direction" = 'inbound' THEN 1
ELSE 0
END
) AS "uid0-partyEnded_count_in_total"
FROM
"PartyEndedEvent"
WHERE
"orgId" = 'orgId60'
AND timezone('UTC', "timestamp") >= to_timestamp('1723136100')
AND timezone('UTC', "timestamp") <= to_timestamp('1723147800')
AND "uid" = '60uid0'
),
"uid1_PartyEndedEvent_metrics" AS (
SELECT
SUM(
CASE
WHEN "direction" = 'inbound' THEN 1
ELSE 0
END
) AS "uid1-partyEnded_count_in_total"
FROM
"PartyEndedEvent"
WHERE
"orgId" = 'orgId60'
AND timezone('UTC', "timestamp") >= to_timestamp('1723136100')
AND timezone('UTC', "timestamp") <= to_timestamp('1723147800')
AND "uid" = '60uid1'
)
-- ...All the way down to the last org member...
SELECT
"uid0-partyEnded_count_in_total",
"uid1-partyEnded_count_in_total"
-- ...One for each org member...
FROM
"uid0_PartyEndedEvent_metrics"
CROSS JOIN "uid1_PartyEndedEvent_metrics"
CROSS JOIN "uid2_PartyEndedEvent_metrics"
-- ...You get the idea
This is the query for viewing the total number of inbound calls answered in a given timeframe, broken down by each org member (which happens to be the default metric on the analytics page).
As can be seen in the excerpt, we are repeating very similar operations for every member of an org. This fact by itself is not necessarily an issue, but if the operation being repeated has an inefficiency, we are multiplying it by the number of org members, meaning a small number could end up being less small.
Note: The UIDs and org IDs you can see in the query above are not real, and are related to the test data I generated when experimenting with my local database. Itās not important for what Iām writing about.
Query plan analysis
Now that we have a query handy, letās look at a query plan. We can get one by putting EXPLAIN in front of the WITH (EXPLAIN takes some other options if you want as well, I also passed BUFFERS FALSE to ādisableā caches and FORMAT YAML to make the query plan easier to read).
The query plan for an org with 31 users (expand at own risk š±)
- Plan:
Node Type: "Nested Loop"
Parallel Aware: false
Async Capable: false
Join Type: "Inner"
Startup Cost: 10217073.67
Total Cost: 10217074.59
Plan Rows: 1
Plan Width: 248
Inner Unique: false
Plans:
- Node Type: "Nested Loop"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Join Type: "Inner"
Startup Cost: 9887490.65
Total Cost: 9887491.54
Plan Rows: 1
Plan Width: 240
Inner Unique: false
Plans:
- Node Type: "Nested Loop"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Join Type: "Inner"
Startup Cost: 9557907.63
Total Cost: 9557908.49
Plan Rows: 1
Plan Width: 232
Inner Unique: false
Plans:
- Node Type: "Nested Loop"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Join Type: "Inner"
Startup Cost: 9228324.61
Total Cost: 9228325.44
Plan Rows: 1
Plan Width: 224
Inner Unique: false
Plans:
- Node Type: "Nested Loop"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Join Type: "Inner"
Startup Cost: 8898741.58
Total Cost: 8898742.38
Plan Rows: 1
Plan Width: 216
Inner Unique: false
Plans:
- Node Type: "Nested Loop"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Join Type: "Inner"
Startup Cost: 8569158.56
Total Cost: 8569159.33
Plan Rows: 1
Plan Width: 208
Inner Unique: false
Plans:
- Node Type: "Nested Loop"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Join Type: "Inner"
Startup Cost: 8239575.54
Total Cost: 8239576.28
Plan Rows: 1
Plan Width: 200
Inner Unique: false
Plans:
- Node Type: "Nested Loop"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Join Type: "Inner"
Startup Cost: 7909992.52
Total Cost: 7909993.23
Plan Rows: 1
Plan Width: 192
Inner Unique: false
Plans:
- Node Type: "Nested Loop"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Join Type: "Inner"
Startup Cost: 7580409.50
Total Cost: 7580410.18
Plan Rows: 1
Plan Width: 184
Inner Unique: false
Plans:
- Node Type: "Nested Loop"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Join Type: "Inner"
Startup Cost: 7250826.48
Total Cost: 7250827.13
Plan Rows: 1
Plan Width: 176
Inner Unique: false
Plans:
- Node Type: "Nested Loop"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Join Type: "Inner"
Startup Cost: 6921243.45
Total Cost: 6921244.07
Plan Rows: 1
Plan Width: 168
Inner Unique: false
Plans:
- Node Type: "Nested Loop"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Join Type: "Inner"
Startup Cost: 6591660.43
Total Cost: 6591661.02
Plan Rows: 1
Plan Width: 160
Inner Unique: false
Plans:
- Node Type: "Nested Loop"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Join Type: "Inner"
Startup Cost: 6262077.41
Total Cost: 6262077.97
Plan Rows: 1
Plan Width: 152
Inner Unique: false
Plans:
- Node Type: "Nested Loop"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Join Type: "Inner"
Startup Cost: 5932494.39
Total Cost: 5932494.92
Plan Rows: 1
Plan Width: 144
Inner Unique: false
Plans:
- Node Type: "Nested Loop"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Join Type: "Inner"
Startup Cost: 5602911.37
Total Cost: 5602911.87
Plan Rows: 1
Plan Width: 136
Inner Unique: false
Plans:
- Node Type: "Nested Loop"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Join Type: "Inner"
Startup Cost: 5273328.35
Total Cost: 5273328.82
Plan Rows: 1
Plan Width: 128
Inner Unique: false
Plans:
- Node Type: "Nested Loop"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Join Type: "Inner"
Startup Cost: 4943745.32
Total Cost: 4943745.76
Plan Rows: 1
Plan Width: 120
Inner Unique: false
Plans:
- Node Type: "Nested Loop"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Join Type: "Inner"
Startup Cost: 4614162.30
Total Cost: 4614162.71
Plan Rows: 1
Plan Width: 112
Inner Unique: false
Plans:
- Node Type: "Nested Loop"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Join Type: "Inner"
Startup Cost: 4284579.28
Total Cost: 4284579.66
Plan Rows: 1
Plan Width: 104
Inner Unique: false
Plans:
- Node Type: "Nested Loop"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Join Type: "Inner"
Startup Cost: 3954996.26
Total Cost: 3954996.61
Plan Rows: 1
Plan Width: 96
Inner Unique: false
Plans:
- Node Type: "Nested Loop"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Join Type: "Inner"
Startup Cost: 3625413.24
Total Cost: 3625413.56
Plan Rows: 1
Plan Width: 88
Inner Unique: false
Plans:
- Node Type: "Nested Loop"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Join Type: "Inner"
Startup Cost: 3295830.22
Total Cost: 3295830.51
Plan Rows: 1
Plan Width: 80
Inner Unique: false
Plans:
- Node Type: "Nested Loop"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Join Type: "Inner"
Startup Cost: 2966247.19
Total Cost: 2966247.45
Plan Rows: 1
Plan Width: 72
Inner Unique: false
Plans:
- Node Type: "Nested Loop"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Join Type: "Inner"
Startup Cost: 2636664.17
Total Cost: 2636664.40
Plan Rows: 1
Plan Width: 64
Inner Unique: false
Plans:
- Node Type: "Nested Loop"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Join Type: "Inner"
Startup Cost: 2307081.15
Total Cost: 2307081.35
Plan Rows: 1
Plan Width: 56
Inner Unique: false
Plans:
- Node Type: "Nested Loop"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Join Type: "Inner"
Startup Cost: 1977498.13
Total Cost: 1977498.30
Plan Rows: 1
Plan Width: 48
Inner Unique: false
Plans:
- Node Type: "Nested Loop"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Join Type: "Inner"
Startup Cost: 1647915.11
Total Cost: 1647915.25
Plan Rows: 1
Plan Width: 40
Inner Unique: false
Plans:
- Node Type: "Nested Loop"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Join Type: "Inner"
Startup Cost: 1318332.09
Total Cost: 1318332.20
Plan Rows: 1
Plan Width: 32
Inner Unique: false
Plans:
- Node Type: "Nested Loop"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Join Type: "Inner"
Startup Cost: 988749.06
Total Cost: 988749.15
Plan Rows: 1
Plan Width: 24
Inner Unique: false
Plans:
- Node Type: "Nested Loop"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Join Type: "Inner"
Startup Cost: 659166.04
Total Cost: 659166.09
Plan Rows: 1
Plan Width: 16
Inner Unique: false
Plans:
- Node Type: "Aggregate"
Strategy: "Plain"
Partial Mode: "Simple"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Startup Cost: 329583.02
Total Cost: 329583.03
Plan Rows: 1
Plan Width: 8
Plans:
- Node Type: "Gather"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Startup Cost: 1000.00
Total Cost: 329583.02
Plan Rows: 1
Plan Width: 4
Workers Planned: 2
Single Copy: false
Plans:
- Node Type: "Seq Scan"
Parent Relationship: "Outer"
Parallel Aware: true
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent"
Startup Cost: 0.00
Total Cost: 328582.92
Plan Rows: 1
Plan Width: 4
Filter: '(("orgId" = ''orgId50''::text) AND (uid = ''50uid0''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
- Node Type: "Aggregate"
Strategy: "Plain"
Partial Mode: "Simple"
Parent Relationship: "Inner"
Parallel Aware: false
Async Capable: false
Startup Cost: 329583.02
Total Cost: 329583.03
Plan Rows: 1
Plan Width: 8
Plans:
- Node Type: "Gather"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Startup Cost: 1000.00
Total Cost: 329583.02
Plan Rows: 1
Plan Width: 4
Workers Planned: 2
Single Copy: false
Plans:
- Node Type: "Seq Scan"
Parent Relationship: "Outer"
Parallel Aware: true
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent_1"
Startup Cost: 0.00
Total Cost: 328582.92
Plan Rows: 1
Plan Width: 4
Filter: '(("orgId" = ''orgId50''::text) AND (uid = ''50uid1''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
- Node Type: "Aggregate"
Strategy: "Plain"
Partial Mode: "Simple"
Parent Relationship: "Inner"
Parallel Aware: false
Async Capable: false
Startup Cost: 329583.02
Total Cost: 329583.03
Plan Rows: 1
Plan Width: 8
Plans:
- Node Type: "Gather"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Startup Cost: 1000.00
Total Cost: 329583.02
Plan Rows: 1
Plan Width: 4
Workers Planned: 2
Single Copy: false
Plans:
- Node Type: "Seq Scan"
Parent Relationship: "Outer"
Parallel Aware: true
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent_2"
Startup Cost: 0.00
Total Cost: 328582.92
Plan Rows: 1
Plan Width: 4
Filter: '(("orgId" = ''orgId50''::text) AND (uid = ''50uid2''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
- Node Type: "Aggregate"
Strategy: "Plain"
Partial Mode: "Simple"
Parent Relationship: "Inner"
Parallel Aware: false
Async Capable: false
Startup Cost: 329583.02
Total Cost: 329583.03
Plan Rows: 1
Plan Width: 8
Plans:
- Node Type: "Gather"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Startup Cost: 1000.00
Total Cost: 329583.02
Plan Rows: 1
Plan Width: 4
Workers Planned: 2
Single Copy: false
Plans:
- Node Type: "Seq Scan"
Parent Relationship: "Outer"
Parallel Aware: true
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent_3"
Startup Cost: 0.00
Total Cost: 328582.92
Plan Rows: 1
Plan Width: 4
Filter: '(("orgId" = ''orgId50''::text) AND (uid = ''50uid3''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
- Node Type: "Aggregate"
Strategy: "Plain"
Partial Mode: "Simple"
Parent Relationship: "Inner"
Parallel Aware: false
Async Capable: false
Startup Cost: 329583.02
Total Cost: 329583.03
Plan Rows: 1
Plan Width: 8
Plans:
- Node Type: "Gather"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Startup Cost: 1000.00
Total Cost: 329583.02
Plan Rows: 1
Plan Width: 4
Workers Planned: 2
Single Copy: false
Plans:
- Node Type: "Seq Scan"
Parent Relationship: "Outer"
Parallel Aware: true
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent_4"
Startup Cost: 0.00
Total Cost: 328582.92
Plan Rows: 1
Plan Width: 4
Filter: '(("orgId" = ''orgId50''::text) AND (uid = ''50uid4''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
- Node Type: "Aggregate"
Strategy: "Plain"
Partial Mode: "Simple"
Parent Relationship: "Inner"
Parallel Aware: false
Async Capable: false
Startup Cost: 329583.02
Total Cost: 329583.03
Plan Rows: 1
Plan Width: 8
Plans:
- Node Type: "Gather"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Startup Cost: 1000.00
Total Cost: 329583.02
Plan Rows: 1
Plan Width: 4
Workers Planned: 2
Single Copy: false
Plans:
- Node Type: "Seq Scan"
Parent Relationship: "Outer"
Parallel Aware: true
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent_5"
Startup Cost: 0.00
Total Cost: 328582.92
Plan Rows: 1
Plan Width: 4
Filter: '(("orgId" = ''orgId50''::text) AND (uid = ''50uid5''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
- Node Type: "Aggregate"
Strategy: "Plain"
Partial Mode: "Simple"
Parent Relationship: "Inner"
Parallel Aware: false
Async Capable: false
Startup Cost: 329583.02
Total Cost: 329583.03
Plan Rows: 1
Plan Width: 8
Plans:
- Node Type: "Gather"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Startup Cost: 1000.00
Total Cost: 329583.02
Plan Rows: 1
Plan Width: 4
Workers Planned: 2
Single Copy: false
Plans:
- Node Type: "Seq Scan"
Parent Relationship: "Outer"
Parallel Aware: true
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent_6"
Startup Cost: 0.00
Total Cost: 328582.92
Plan Rows: 1
Plan Width: 4
Filter: '(("orgId" = ''orgId50''::text) AND (uid = ''50uid6''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
- Node Type: "Aggregate"
Strategy: "Plain"
Partial Mode: "Simple"
Parent Relationship: "Inner"
Parallel Aware: false
Async Capable: false
Startup Cost: 329583.02
Total Cost: 329583.03
Plan Rows: 1
Plan Width: 8
Plans:
- Node Type: "Gather"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Startup Cost: 1000.00
Total Cost: 329583.02
Plan Rows: 1
Plan Width: 4
Workers Planned: 2
Single Copy: false
Plans:
- Node Type: "Seq Scan"
Parent Relationship: "Outer"
Parallel Aware: true
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent_7"
Startup Cost: 0.00
Total Cost: 328582.92
Plan Rows: 1
Plan Width: 4
Filter: '(("orgId" = ''orgId50''::text) AND (uid = ''50uid7''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
- Node Type: "Aggregate"
Strategy: "Plain"
Partial Mode: "Simple"
Parent Relationship: "Inner"
Parallel Aware: false
Async Capable: false
Startup Cost: 329583.02
Total Cost: 329583.03
Plan Rows: 1
Plan Width: 8
Plans:
- Node Type: "Gather"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Startup Cost: 1000.00
Total Cost: 329583.02
Plan Rows: 1
Plan Width: 4
Workers Planned: 2
Single Copy: false
Plans:
- Node Type: "Seq Scan"
Parent Relationship: "Outer"
Parallel Aware: true
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent_8"
Startup Cost: 0.00
Total Cost: 328582.92
Plan Rows: 1
Plan Width: 4
Filter: '(("orgId" = ''orgId50''::text) AND (uid = ''50uid8''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
- Node Type: "Aggregate"
Strategy: "Plain"
Partial Mode: "Simple"
Parent Relationship: "Inner"
Parallel Aware: false
Async Capable: false
Startup Cost: 329583.02
Total Cost: 329583.03
Plan Rows: 1
Plan Width: 8
Plans:
- Node Type: "Gather"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Startup Cost: 1000.00
Total Cost: 329583.02
Plan Rows: 1
Plan Width: 4
Workers Planned: 2
Single Copy: false
Plans:
- Node Type: "Seq Scan"
Parent Relationship: "Outer"
Parallel Aware: true
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent_9"
Startup Cost: 0.00
Total Cost: 328582.92
Plan Rows: 1
Plan Width: 4
Filter: '(("orgId" = ''orgId50''::text) AND (uid = ''50uid9''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
- Node Type: "Aggregate"
Strategy: "Plain"
Partial Mode: "Simple"
Parent Relationship: "Inner"
Parallel Aware: false
Async Capable: false
Startup Cost: 329583.02
Total Cost: 329583.03
Plan Rows: 1
Plan Width: 8
Plans:
- Node Type: "Gather"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Startup Cost: 1000.00
Total Cost: 329583.02
Plan Rows: 1
Plan Width: 4
Workers Planned: 2
Single Copy: false
Plans:
- Node Type: "Seq Scan"
Parent Relationship: "Outer"
Parallel Aware: true
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent_10"
Startup Cost: 0.00
Total Cost: 328582.92
Plan Rows: 1
Plan Width: 4
Filter: '(("orgId" = ''orgId50''::text) AND (uid = ''50uid10''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
- Node Type: "Aggregate"
Strategy: "Plain"
Partial Mode: "Simple"
Parent Relationship: "Inner"
Parallel Aware: false
Async Capable: false
Startup Cost: 329583.02
Total Cost: 329583.03
Plan Rows: 1
Plan Width: 8
Plans:
- Node Type: "Gather"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Startup Cost: 1000.00
Total Cost: 329583.02
Plan Rows: 1
Plan Width: 4
Workers Planned: 2
Single Copy: false
Plans:
- Node Type: "Seq Scan"
Parent Relationship: "Outer"
Parallel Aware: true
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent_11"
Startup Cost: 0.00
Total Cost: 328582.92
Plan Rows: 1
Plan Width: 4
Filter: '(("orgId" = ''orgId50''::text) AND (uid = ''50uid11''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
- Node Type: "Aggregate"
Strategy: "Plain"
Partial Mode: "Simple"
Parent Relationship: "Inner"
Parallel Aware: false
Async Capable: false
Startup Cost: 329583.02
Total Cost: 329583.03
Plan Rows: 1
Plan Width: 8
Plans:
- Node Type: "Gather"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Startup Cost: 1000.00
Total Cost: 329583.02
Plan Rows: 1
Plan Width: 4
Workers Planned: 2
Single Copy: false
Plans:
- Node Type: "Seq Scan"
Parent Relationship: "Outer"
Parallel Aware: true
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent_12"
Startup Cost: 0.00
Total Cost: 328582.92
Plan Rows: 1
Plan Width: 4
Filter: '(("orgId" = ''orgId50''::text) AND (uid = ''50uid12''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
- Node Type: "Aggregate"
Strategy: "Plain"
Partial Mode: "Simple"
Parent Relationship: "Inner"
Parallel Aware: false
Async Capable: false
Startup Cost: 329583.02
Total Cost: 329583.03
Plan Rows: 1
Plan Width: 8
Plans:
- Node Type: "Gather"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Startup Cost: 1000.00
Total Cost: 329583.02
Plan Rows: 1
Plan Width: 4
Workers Planned: 2
Single Copy: false
Plans:
- Node Type: "Seq Scan"
Parent Relationship: "Outer"
Parallel Aware: true
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent_13"
Startup Cost: 0.00
Total Cost: 328582.92
Plan Rows: 1
Plan Width: 4
Filter: '(("orgId" = ''orgId50''::text) AND (uid = ''50uid13''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
- Node Type: "Aggregate"
Strategy: "Plain"
Partial Mode: "Simple"
Parent Relationship: "Inner"
Parallel Aware: false
Async Capable: false
Startup Cost: 329583.02
Total Cost: 329583.03
Plan Rows: 1
Plan Width: 8
Plans:
- Node Type: "Gather"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Startup Cost: 1000.00
Total Cost: 329583.02
Plan Rows: 1
Plan Width: 4
Workers Planned: 2
Single Copy: false
Plans:
- Node Type: "Seq Scan"
Parent Relationship: "Outer"
Parallel Aware: true
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent_14"
Startup Cost: 0.00
Total Cost: 328582.92
Plan Rows: 1
Plan Width: 4
Filter: '(("orgId" = ''orgId50''::text) AND (uid = ''50uid14''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
- Node Type: "Aggregate"
Strategy: "Plain"
Partial Mode: "Simple"
Parent Relationship: "Inner"
Parallel Aware: false
Async Capable: false
Startup Cost: 329583.02
Total Cost: 329583.03
Plan Rows: 1
Plan Width: 8
Plans:
- Node Type: "Gather"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Startup Cost: 1000.00
Total Cost: 329583.02
Plan Rows: 1
Plan Width: 4
Workers Planned: 2
Single Copy: false
Plans:
- Node Type: "Seq Scan"
Parent Relationship: "Outer"
Parallel Aware: true
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent_15"
Startup Cost: 0.00
Total Cost: 328582.92
Plan Rows: 1
Plan Width: 4
Filter: '(("orgId" = ''orgId50''::text) AND (uid = ''50uid15''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
- Node Type: "Aggregate"
Strategy: "Plain"
Partial Mode: "Simple"
Parent Relationship: "Inner"
Parallel Aware: false
Async Capable: false
Startup Cost: 329583.02
Total Cost: 329583.03
Plan Rows: 1
Plan Width: 8
Plans:
- Node Type: "Gather"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Startup Cost: 1000.00
Total Cost: 329583.02
Plan Rows: 1
Plan Width: 4
Workers Planned: 2
Single Copy: false
Plans:
- Node Type: "Seq Scan"
Parent Relationship: "Outer"
Parallel Aware: true
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent_16"
Startup Cost: 0.00
Total Cost: 328582.92
Plan Rows: 1
Plan Width: 4
Filter: '(("orgId" = ''orgId50''::text) AND (uid = ''50uid16''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
- Node Type: "Aggregate"
Strategy: "Plain"
Partial Mode: "Simple"
Parent Relationship: "Inner"
Parallel Aware: false
Async Capable: false
Startup Cost: 329583.02
Total Cost: 329583.03
Plan Rows: 1
Plan Width: 8
Plans:
- Node Type: "Gather"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Startup Cost: 1000.00
Total Cost: 329583.02
Plan Rows: 1
Plan Width: 4
Workers Planned: 2
Single Copy: false
Plans:
- Node Type: "Seq Scan"
Parent Relationship: "Outer"
Parallel Aware: true
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent_17"
Startup Cost: 0.00
Total Cost: 328582.92
Plan Rows: 1
Plan Width: 4
Filter: '(("orgId" = ''orgId50''::text) AND (uid = ''50uid17''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
- Node Type: "Aggregate"
Strategy: "Plain"
Partial Mode: "Simple"
Parent Relationship: "Inner"
Parallel Aware: false
Async Capable: false
Startup Cost: 329583.02
Total Cost: 329583.03
Plan Rows: 1
Plan Width: 8
Plans:
- Node Type: "Gather"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Startup Cost: 1000.00
Total Cost: 329583.02
Plan Rows: 1
Plan Width: 4
Workers Planned: 2
Single Copy: false
Plans:
- Node Type: "Seq Scan"
Parent Relationship: "Outer"
Parallel Aware: true
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent_18"
Startup Cost: 0.00
Total Cost: 328582.92
Plan Rows: 1
Plan Width: 4
Filter: '(("orgId" = ''orgId50''::text) AND (uid = ''50uid18''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
- Node Type: "Aggregate"
Strategy: "Plain"
Partial Mode: "Simple"
Parent Relationship: "Inner"
Parallel Aware: false
Async Capable: false
Startup Cost: 329583.02
Total Cost: 329583.03
Plan Rows: 1
Plan Width: 8
Plans:
- Node Type: "Gather"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Startup Cost: 1000.00
Total Cost: 329583.02
Plan Rows: 1
Plan Width: 4
Workers Planned: 2
Single Copy: false
Plans:
- Node Type: "Seq Scan"
Parent Relationship: "Outer"
Parallel Aware: true
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent_19"
Startup Cost: 0.00
Total Cost: 328582.92
Plan Rows: 1
Plan Width: 4
Filter: '(("orgId" = ''orgId50''::text) AND (uid = ''50uid19''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
- Node Type: "Aggregate"
Strategy: "Plain"
Partial Mode: "Simple"
Parent Relationship: "Inner"
Parallel Aware: false
Async Capable: false
Startup Cost: 329583.02
Total Cost: 329583.03
Plan Rows: 1
Plan Width: 8
Plans:
- Node Type: "Gather"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Startup Cost: 1000.00
Total Cost: 329583.02
Plan Rows: 1
Plan Width: 4
Workers Planned: 2
Single Copy: false
Plans:
- Node Type: "Seq Scan"
Parent Relationship: "Outer"
Parallel Aware: true
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent_20"
Startup Cost: 0.00
Total Cost: 328582.92
Plan Rows: 1
Plan Width: 4
Filter: '(("orgId" = ''orgId50''::text) AND (uid = ''50uid20''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
- Node Type: "Aggregate"
Strategy: "Plain"
Partial Mode: "Simple"
Parent Relationship: "Inner"
Parallel Aware: false
Async Capable: false
Startup Cost: 329583.02
Total Cost: 329583.03
Plan Rows: 1
Plan Width: 8
Plans:
- Node Type: "Gather"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Startup Cost: 1000.00
Total Cost: 329583.02
Plan Rows: 1
Plan Width: 4
Workers Planned: 2
Single Copy: false
Plans:
- Node Type: "Seq Scan"
Parent Relationship: "Outer"
Parallel Aware: true
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent_21"
Startup Cost: 0.00
Total Cost: 328582.92
Plan Rows: 1
Plan Width: 4
Filter: '(("orgId" = ''orgId50''::text) AND (uid = ''50uid21''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
- Node Type: "Aggregate"
Strategy: "Plain"
Partial Mode: "Simple"
Parent Relationship: "Inner"
Parallel Aware: false
Async Capable: false
Startup Cost: 329583.02
Total Cost: 329583.03
Plan Rows: 1
Plan Width: 8
Plans:
- Node Type: "Gather"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Startup Cost: 1000.00
Total Cost: 329583.02
Plan Rows: 1
Plan Width: 4
Workers Planned: 2
Single Copy: false
Plans:
- Node Type: "Seq Scan"
Parent Relationship: "Outer"
Parallel Aware: true
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent_22"
Startup Cost: 0.00
Total Cost: 328582.92
Plan Rows: 1
Plan Width: 4
Filter: '(("orgId" = ''orgId50''::text) AND (uid = ''50uid22''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
- Node Type: "Aggregate"
Strategy: "Plain"
Partial Mode: "Simple"
Parent Relationship: "Inner"
Parallel Aware: false
Async Capable: false
Startup Cost: 329583.02
Total Cost: 329583.03
Plan Rows: 1
Plan Width: 8
Plans:
- Node Type: "Gather"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Startup Cost: 1000.00
Total Cost: 329583.02
Plan Rows: 1
Plan Width: 4
Workers Planned: 2
Single Copy: false
Plans:
- Node Type: "Seq Scan"
Parent Relationship: "Outer"
Parallel Aware: true
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent_23"
Startup Cost: 0.00
Total Cost: 328582.92
Plan Rows: 1
Plan Width: 4
Filter: '(("orgId" = ''orgId50''::text) AND (uid = ''50uid23''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
- Node Type: "Aggregate"
Strategy: "Plain"
Partial Mode: "Simple"
Parent Relationship: "Inner"
Parallel Aware: false
Async Capable: false
Startup Cost: 329583.02
Total Cost: 329583.03
Plan Rows: 1
Plan Width: 8
Plans:
- Node Type: "Gather"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Startup Cost: 1000.00
Total Cost: 329583.02
Plan Rows: 1
Plan Width: 4
Workers Planned: 2
Single Copy: false
Plans:
- Node Type: "Seq Scan"
Parent Relationship: "Outer"
Parallel Aware: true
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent_24"
Startup Cost: 0.00
Total Cost: 328582.92
Plan Rows: 1
Plan Width: 4
Filter: '(("orgId" = ''orgId50''::text) AND (uid = ''50uid24''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
- Node Type: "Aggregate"
Strategy: "Plain"
Partial Mode: "Simple"
Parent Relationship: "Inner"
Parallel Aware: false
Async Capable: false
Startup Cost: 329583.02
Total Cost: 329583.03
Plan Rows: 1
Plan Width: 8
Plans:
- Node Type: "Gather"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Startup Cost: 1000.00
Total Cost: 329583.02
Plan Rows: 1
Plan Width: 4
Workers Planned: 2
Single Copy: false
Plans:
- Node Type: "Seq Scan"
Parent Relationship: "Outer"
Parallel Aware: true
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent_25"
Startup Cost: 0.00
Total Cost: 328582.92
Plan Rows: 1
Plan Width: 4
Filter: '(("orgId" = ''orgId50''::text) AND (uid = ''50uid25''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
- Node Type: "Aggregate"
Strategy: "Plain"
Partial Mode: "Simple"
Parent Relationship: "Inner"
Parallel Aware: false
Async Capable: false
Startup Cost: 329583.02
Total Cost: 329583.03
Plan Rows: 1
Plan Width: 8
Plans:
- Node Type: "Gather"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Startup Cost: 1000.00
Total Cost: 329583.02
Plan Rows: 1
Plan Width: 4
Workers Planned: 2
Single Copy: false
Plans:
- Node Type: "Seq Scan"
Parent Relationship: "Outer"
Parallel Aware: true
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent_26"
Startup Cost: 0.00
Total Cost: 328582.92
Plan Rows: 1
Plan Width: 4
Filter: '(("orgId" = ''orgId50''::text) AND (uid = ''50uid26''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
- Node Type: "Aggregate"
Strategy: "Plain"
Partial Mode: "Simple"
Parent Relationship: "Inner"
Parallel Aware: false
Async Capable: false
Startup Cost: 329583.02
Total Cost: 329583.03
Plan Rows: 1
Plan Width: 8
Plans:
- Node Type: "Gather"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Startup Cost: 1000.00
Total Cost: 329583.02
Plan Rows: 1
Plan Width: 4
Workers Planned: 2
Single Copy: false
Plans:
- Node Type: "Seq Scan"
Parent Relationship: "Outer"
Parallel Aware: true
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent_27"
Startup Cost: 0.00
Total Cost: 328582.92
Plan Rows: 1
Plan Width: 4
Filter: '(("orgId" = ''orgId50''::text) AND (uid = ''50uid27''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
- Node Type: "Aggregate"
Strategy: "Plain"
Partial Mode: "Simple"
Parent Relationship: "Inner"
Parallel Aware: false
Async Capable: false
Startup Cost: 329583.02
Total Cost: 329583.03
Plan Rows: 1
Plan Width: 8
Plans:
- Node Type: "Gather"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Startup Cost: 1000.00
Total Cost: 329583.02
Plan Rows: 1
Plan Width: 4
Workers Planned: 2
Single Copy: false
Plans:
- Node Type: "Seq Scan"
Parent Relationship: "Outer"
Parallel Aware: true
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent_28"
Startup Cost: 0.00
Total Cost: 328582.92
Plan Rows: 1
Plan Width: 4
Filter: '(("orgId" = ''orgId50''::text) AND (uid = ''50uid28''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
- Node Type: "Aggregate"
Strategy: "Plain"
Partial Mode: "Simple"
Parent Relationship: "Inner"
Parallel Aware: false
Async Capable: false
Startup Cost: 329583.02
Total Cost: 329583.03
Plan Rows: 1
Plan Width: 8
Plans:
- Node Type: "Gather"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Startup Cost: 1000.00
Total Cost: 329583.02
Plan Rows: 1
Plan Width: 4
Workers Planned: 2
Single Copy: false
Plans:
- Node Type: "Seq Scan"
Parent Relationship: "Outer"
Parallel Aware: true
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent_29"
Startup Cost: 0.00
Total Cost: 328582.92
Plan Rows: 1
Plan Width: 4
Filter: '(("orgId" = ''orgId50''::text) AND (uid = ''50uid29''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
- Node Type: "Aggregate"
Strategy: "Plain"
Partial Mode: "Simple"
Parent Relationship: "Inner"
Parallel Aware: false
Async Capable: false
Startup Cost: 329583.02
Total Cost: 329583.03
Plan Rows: 1
Plan Width: 8
Plans:
- Node Type: "Gather"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Startup Cost: 1000.00
Total Cost: 329583.02
Plan Rows: 1
Plan Width: 4
Workers Planned: 2
Single Copy: false
Plans:
- Node Type: "Seq Scan"
Parent Relationship: "Outer"
Parallel Aware: true
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent_30"
Startup Cost: 0.00
Total Cost: 328582.92
Plan Rows: 1
Plan Width: 4
Filter: '(("orgId" = ''orgId50''::text) AND (uid = ''50uid30''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
If you scrolled far enough in the query plan, you will have reached a node that looks like this:
Node Type: "Seq Scan"
Parent Relationship: "Outer"
Parallel Aware: true
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent_30"
Startup Cost: 0.00
Total Cost: 328582.92
Plan Rows: 1
Plan Width: 4
Filter: '(("orgId" = ''orgId50''::text) AND (uid = ''50uid30''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
The key part there is "Seq Scan". This is bad! Seq Scan is short for sequential scan and tells you that it looked through every row in the table to get the requested data. The database engine has to look through every row, every time because there is no way for it to know that it has gotten every row that matches the filter.
One sequential scan is bad enough, but as indicated earlier, one inefficiency repeated for every member in an org adds up!
Solution
Now that we know the database is performing potentially many sequential scans, what can we do about it?
In general when we see sequential scans, the first thought is whether adding an index would help the situation. In the slowest query, we can see that every āpartā of the query is filtering by the same orgId. This is a strong hint that we should consider adding an index on orgId.
Testing the hypothesis
We think adding an orgId index may help the query performance. How can we test that hypothesis?
We can set up a database on our local machine, put a ton of test data in it, and test the query with and without indexes (being sure to slightly change the IDs in the query each time to ensure weāre not getting cached results).
When I was initially running these experiments, I wasnāt sure how much data we actually have in production, so I guessed (it turns out I overestimated a bit, but thatās okay). Hereās the script I ran to āseedā the database with 9.331M rows (301 orgs with 31 users, each with 1000 call records):
DO
$$
BEGIN
FOR orgid IN 0..300 LOOP
FOR userid IN 0..30 LOOP
FOR callid IN 1..1000 LOOP
INSERT INTO "PartyEndedEvent" (id, timestamp, "orgId", "callId", internal, "messageId", tags, uid, direction, duration) VALUES
(gen_random_uuid(), NOW(), CONCAT('orgId', orgid::TEXT), gen_random_uuid(), random() < 0.5, gen_random_uuid(), ARRAY[]::TEXT[], CONCAT(orgid::text, 'uid', userid::text), (CASE WHEN random() < 0.5 THEN 'inbound' ELSE 'outbound' END)::"Direction", floor(random()*(180-90)+90));
END LOOP;
END LOOP;
END LOOP;
END;
$$;
Note that it is famously difficult to get production-grade data into a test database (without just copying it, but thatās poor form), so this rough estimate was going to have to do.
Running the full version of the slow query (with 31 users) on this data took around 25-30 seconds. This is roughly the same performance we were seeing in production, so I knew I was onto something.
After repeating that experiment (with different IDs) a few times to be sure of the time it was taking, I added an orgId index to the table:
CREATE INDEX "PartyEndedEvent_orgId_idx" ON "PartyEndedEvent"("orgId");
Running the same query again, it took around ~100ms. Double checked, triple checked - adding the orgId index led to a speed-up by a factor of ~250. Great!
But we do a lot of searching by UID, too, maybe we could look at adding an index on that tooā¦
CREATE INDEX "PartyEndedEvent_uid_idx" ON "PartyEndedEvent"("uid");
This got the query times down to around ~50ms (500x improvement on the original times).
The updated query plan also confirms the hypothesis. I wonāt include the full plan here (it is very similar to the one above), but we see that instead of sequential scans, we have the following:
- Node Type: "Bitmap Heap Scan"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Relation Name: "PartyEndedEvent"
Alias: "PartyEndedEvent"
Startup Cost: 331.93
Total Cost: 85003.44
Plan Rows: 1
Plan Width: 4
Recheck Cond: '("orgId" = ''orgId50''::text)'
Filter: '((uid = ''50uid0''::text) AND (timezone(''UTC''::text, "timestamp") >= ''2024-08-09 04:55:00+12''::timestamp with time zone) AND (timezone(''UTC''::text, "timestamp") <= ''2024-08-09 08:10:00+12''::timestamp with time zone))'
Plans:
- Node Type: "Bitmap Index Scan"
Parent Relationship: "Outer"
Parallel Aware: false
Async Capable: false
Index Name: "PartyEndedEvent_orgId_idx"
Startup Cost: 0.00
Total Cost: 331.93
Plan Rows: 30332
Plan Width: 0
Index Cond: '("orgId" = ''orgId50''::text)'
No need to explain what these nodes mean, but we can see that an Index Scan is indeed taking place on the orgId column, so we know that the database engine is leveraging the new index.
I chose to stop here, as subsequent gains would be marginal (and possibly greedy).
Why not just add indexes on every column?
I added two indexes, why not add indexes on every column?
There are a few reasons:
- Over-indexing is a thing - as mentioned earlier, there is a cost to indexes. This comes mainly in the form of storage space, but also in creating work for the database every time it creates a row. Keeping all the indexes up to date takes effort.
- The two indexes are used heavily in the slowest query. The other data appearing in the query filters donāt lend themselves as well to being indexed on (although it would be possible).
- Any additional indexes wouldnāt be helpful most of the time. The main performance bottle necks were addressed with just one index.
- Thereās an appeal in a minimal solution.
Room for further improvements?
As VXT grows, I expect some performance issues to start creeping back in (although it will take a while).
If we want to improve performance further, adding more indexes wonāt help. We will need to re-think the structure of the queries themselves. Currently, the queries are made up of highly modular components, an affordance made to enable a more feature-rich analytics page. This works well, but means that sometimes we end up with sub-optimal queries (e.g. the one discussed in this post).
Having bespoke queries per analytics metric would allow us to more fully leverage database features to squeeze out as much performance as possible. As an example, letās look at what a ābespokeā version of the query this post discusses might look like.
In a nutshell, the query is getting the number of inbound calls answered for each user in an org:
SELECT
uid,
COUNT(*)
FROM
"PartyEndedEvent"
WHERE
"orgId" = 'someOrgId'
AND "Direction" = 'inbound'
AND timezone('UTC', "timestamp") >= to_timestamp('1723136100')
AND timezone('UTC', "timestamp") <= to_timestamp('1723147800');
This query is much simpler, but would break the interface between the client and the server, meaning there would be work involved to keep analytics working (not to mention the effort involved in validating that the updated queries are still correct).
Update
We shipped the changes described in this post and did indeed see a reduction in query time in production. However there were still some queries that were performing poorly (although not as poorly as before the update).
Doing more experimentation locally, it was found that something was overlooked that would allow us to squeeze out even more speed relatively easily - using multicolumn indexes.
Removing the recently created indexes (i.e. the ones on orgId and uid), we replaced them with an index on (orgId, uid). Additionally, we added an index on (orgId, orgPhoneNumberId) as the poorly performing queries were doing a lot of filtering on those columns, too.
Itās now been a week since releasing the multicolumn indexes, and the slowest queries are now taking on average ~230ms. Of course, we could pull this number down more, but as mentioned above, this is more than good enough.