perf(core): Make execution queries faster #9817

ivov · 2024-06-20T10:08:59Z

This reverts commit dbb1c2e.

krynble

The way I see it is that we just need 4 indices, and the image below outlines the changes I'm proposing:

The reason for each index has been described in the ticket, but in the end we want all 4 db systems to have only those 4 indices, wdyt?

packages/cli/src/databases/migrations/common/1717498465932-RefactorExecutionIndices.ts

ivov · 2024-06-24T10:13:06Z

Thanks, from the story I was under the impression we only wanted to touch these, rather than all of them.

packages/cli/src/databases/migrations/common/1717498465932-RefactorExecutionIndices.ts

packages/cli/src/databases/dsl/Indices.ts

packages/cli/src/databases/migrations/common/1717498465932-RefactorExecutionIndices.ts

tomi

Couple comments, mainly about the structure of the indexes

packages/cli/src/databases/dsl/Indices.ts

packages/cli/src/databases/migrations/common/1717498465932-RefactorExecutionIndices.ts

ivov · 2024-08-16T12:06:00Z

Find multiple executions

Method: ExecutionRepository.findManyByRangeQuery

Sample TypeORM log:

SELECT COUNT(DISTINCT("execution"."id")) AS "cnt" FROM "execution_entity" "execution" INNER JOIN "workflow_entity" "workflow" ON "workflow"."id"="execution"."workflowId" WHERE ( "execution"."workflowId" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) AND "execution"."status" IN (?, ?) ) AND ( "execution"."deletedAt" IS NULL ) -- PARAMETERS: ["04OYwcQzYJdnjsdd","2N1V33FP2dKlmTz7","3SmuvtkVARLuQW6y","4Zo1ABVbAXz8pdhh","6fVKKFKQBGAnvZ4a","76hvMdsQBVBdnYNM","8dlw8vap8tPkg50i","A2v6XntNlA19XTBu","BDp700hAdBGEcoaI","CTfQjEFQcW5G3fMr","CfLvzSSWLAZVKyFV","Dp4reGx2lxez7Vni","EZ1fmiFhp6lby6Jg","Hm90qPVAq25VzrdD","LECmidOfr7VfQ8I7","LLRzyiCFeC9vmDki","Ov4lOrsuNJqQQvgm","Penre9AUYUc0oCr7","QIJEUiWfX8fe1qXO","QR2GgBoUbcM3JuuX","QnrkjpyTEfofdqTM","Rtm65p8UI6lmhqKX","UQtas8whCecK4p2c","UU6i2lrPR5Aipx0N","VT6WW8eWyqitfy16","X59nkhZx5Q5QxUVa","XNfBTQYWfrXaKtIE","XYKSUGOlf4jPJJ43","YTxYdrPMwsxQHj93","ZprM1PsYwZO3IBWA","fxc5dTtFiI1dlzzA","gCjcQNUkC5kepysS","h9zGiCxSeIPZtUW2","hDpmfTPWQMttZrGc","heRTgosoRcMOYZfB","j9TAi7mwIOhP3dJd","jYBbm6ZOWX1DNMRP","lexlovNxCRXANfqP","lqJVl2vUAs4hHIiU","nJKtmhRIZ3lwBekg","nJKtmhRIZ3lwBekg","onPzjgFhmLPqkWXU","rVJSrrVsFVInO1Mf","reVGqrHPH1xjdC3q","w71QwtdB2bThj4ap","zr50K32bPqIzW0jA","new","running"]

Equivalent SQL query:

SELECT COUNT(DISTINCT execution.id) AS cnt
FROM execution_entity execution
INNER JOIN workflow_entity workflow ON workflow.id = execution."workflowId"
WHERE execution."workflowId" IN (
    '04OYwcQzYJdnjsdd', '2N1V33FP2dKlmTz7', '3SmuvtkVARLuQW6y', '4Zo1ABVbAXz8pdhh',
    '6fVKKFKQBGAnvZ4a', '76hvMdsQBVBdnYNM', '8dlw8vap8tPkg50i', 'A2v6XntNlA19XTBu',
    'BDp700hAdBGEcoaI', 'CTfQjEFQcW5G3fMr', 'CfLvzSSWLAZVKyFV', 'Dp4reGx2lxez7Vni',
    'EZ1fmiFhp6lby6Jg', 'Hm90qPVAq25VzrdD', 'LECmidOfr7VfQ8I7', 'LLRzyiCFeC9vmDki',
    'Ov4lOrsuNJqQQvgm', 'Penre9AUYUc0oCr7', 'QIJEUiWfX8fe1qXO', 'QR2GgBoUbcM3JuuX',
    'QnrkjpyTEfofdqTM', 'Rtm65p8UI6lmhqKX', 'UQtas8whCecK4p2c', 'UU6i2lrPR5Aipx0N',
    'VT6WW8eWyqitfy16', 'X59nkhZx5Q5QxUVa', 'XNfBTQYWfrXaKtIE', 'XYKSUGOlf4jPJJ43',
    'YTxYdrPMwsxQHj93', 'ZprM1PsYwZO3IBWA', 'fxc5dTtFiI1dlzzA', 'gCjcQNUkC5kepysS',
    'h9zGiCxSeIPZtUW2', 'hDpmfTPWQMttZrGc', 'heRTgosoRcMOYZfB', 'j9TAi7mwIOhP3dJd',
    'jYBbm6ZOWX1DNMRP', 'lexlovNxCRXANfqP', 'lqJVl2vUAs4hHIiU', 'nJKtmhRIZ3lwBekg',
    'nJKtmhRIZ3lwBekg', 'onPzjgFhmLPqkWXU', 'rVJSrrVsFVInO1Mf', 'reVGqrHPH1xjdC3q',
    'w71QwtdB2bThj4ap', 'zr50K32bPqIzW0jA'
)
AND execution.status IN ('new', 'running')
AND execution."deletedAt" IS NULL;

EXPLAIN QUERY PLAN on SQLite 3.43 on master

SEARCH execution USING INDEX IDX_execution_entity_deletedAt (deletedAt=?)
SEARCH workflow USING COVERING INDEX sqlite_autoindex_workflow_entity_1 (id=?)

EXPLAIN ANALYZE on Postgres 13.7 on master

Aggregate  (cost=16.55..16.56 rows=1 width=8) (actual time=0.027..0.030 rows=1 loops=1)
  ->  Nested Loop  (cost=0.29..16.54 rows=1 width=4) (actual time=0.008..0.010 rows=0 loops=1)
"        ->  Index Scan using ""IDX_execution_entity_deletedAt"" on execution_entity execution  (cost=0.15..8.23 rows=1 width=94) (actual time=0.007..0.008 rows=0 loops=1)"
"              Index Cond: (""deletedAt"" IS NULL)"
"              Filter: (((status)::text = ANY ('{new,running}'::text[])) AND ((""workflowId"")::text = ANY ('{04OYwcQzYJdnjsdd,2N1V33FP2dKlmTz7,3SmuvtkVARLuQW6y,4Zo1ABVbAXz8pdhh,6fVKKFKQBGAnvZ4a,76hvMdsQBVBdnYNM,8dlw8vap8tPkg50i,A2v6XntNlA19XTBu,BDp700hAdBGEcoaI,CTfQjEFQcW5G3fMr,CfLvzSSWLAZVKyFV,Dp4reGx2lxez7Vni,EZ1fmiFhp6lby6Jg,Hm90qPVAq25VzrdD,LECmidOfr7VfQ8I7,LLRzyiCFeC9vmDki,Ov4lOrsuNJqQQvgm,Penre9AUYUc0oCr7,QIJEUiWfX8fe1qXO,QR2GgBoUbcM3JuuX,QnrkjpyTEfofdqTM,Rtm65p8UI6lmhqKX,UQtas8whCecK4p2c,UU6i2lrPR5Aipx0N,VT6WW8eWyqitfy16,X59nkhZx5Q5QxUVa,XNfBTQYWfrXaKtIE,XYKSUGOlf4jPJJ43,YTxYdrPMwsxQHj93,ZprM1PsYwZO3IBWA,fxc5dTtFiI1dlzzA,gCjcQNUkC5kepysS,h9zGiCxSeIPZtUW2,hDpmfTPWQMttZrGc,heRTgosoRcMOYZfB,j9TAi7mwIOhP3dJd,jYBbm6ZOWX1DNMRP,lexlovNxCRXANfqP,lqJVl2vUAs4hHIiU,nJKtmhRIZ3lwBekg,nJKtmhRIZ3lwBekg,onPzjgFhmLPqkWXU,rVJSrrVsFVInO1Mf,reVGqrHPH1xjdC3q,w71QwtdB2bThj4ap,zr50K32bPqIzW0jA}'::text[])))"
        ->  Index Only Scan using workflow_entity_pkey on workflow_entity workflow  (cost=0.14..8.16 rows=1 width=90) (never executed)
"              Index Cond: (id = (execution.""workflowId"")::text)"
              Heap Fetches: 0
Planning Time: 1.705 ms
Execution Time: 0.133 ms

EXPLAIN QUERY PLAN on SQLite 3.43 on pay-1609

SEARCH execution USING INDEX IDX_execution_entity_deletedAt (deletedAt=?)
SEARCH workflow USING COVERING INDEX sqlite_autoindex_workflow_entity_1 (id=?)

EXPLAIN ANALYZE on Postgres 13.7 on pay-1609

Aggregate  (cost=16.55..16.56 rows=1 width=8) (actual time=0.149..0.153 rows=1 loops=1)
  ->  Nested Loop  (cost=0.29..16.54 rows=1 width=4) (actual time=0.016..0.019 rows=0 loops=1)
"        ->  Index Scan using ""IDX_execution_entity_deletedAt"" on execution_entity execution  (cost=0.15..8.23 rows=1 width=94) (actual time=0.013..0.015 rows=0 loops=1)"
"              Index Cond: (""deletedAt"" IS NULL)"
"              Filter: (((status)::text = ANY ('{new,running}'::text[])) AND ((""workflowId"")::text = ANY ('{04OYwcQzYJdnjsdd,2N1V33FP2dKlmTz7,3SmuvtkVARLuQW6y,4Zo1ABVbAXz8pdhh,6fVKKFKQBGAnvZ4a,76hvMdsQBVBdnYNM,8dlw8vap8tPkg50i,A2v6XntNlA19XTBu,BDp700hAdBGEcoaI,CTfQjEFQcW5G3fMr,CfLvzSSWLAZVKyFV,Dp4reGx2lxez7Vni,EZ1fmiFhp6lby6Jg,Hm90qPVAq25VzrdD,LECmidOfr7VfQ8I7,LLRzyiCFeC9vmDki,Ov4lOrsuNJqQQvgm,Penre9AUYUc0oCr7,QIJEUiWfX8fe1qXO,QR2GgBoUbcM3JuuX,QnrkjpyTEfofdqTM,Rtm65p8UI6lmhqKX,UQtas8whCecK4p2c,UU6i2lrPR5Aipx0N,VT6WW8eWyqitfy16,X59nkhZx5Q5QxUVa,XNfBTQYWfrXaKtIE,XYKSUGOlf4jPJJ43,YTxYdrPMwsxQHj93,ZprM1PsYwZO3IBWA,fxc5dTtFiI1dlzzA,gCjcQNUkC5kepysS,h9zGiCxSeIPZtUW2,hDpmfTPWQMttZrGc,heRTgosoRcMOYZfB,j9TAi7mwIOhP3dJd,jYBbm6ZOWX1DNMRP,lexlovNxCRXANfqP,lqJVl2vUAs4hHIiU,nJKtmhRIZ3lwBekg,nJKtmhRIZ3lwBekg,onPzjgFhmLPqkWXU,rVJSrrVsFVInO1Mf,reVGqrHPH1xjdC3q,w71QwtdB2bThj4ap,zr50K32bPqIzW0jA}'::text[])))"
        ->  Index Only Scan using workflow_entity_pkey on workflow_entity workflow  (cost=0.14..8.16 rows=1 width=90) (never executed)
"              Index Cond: (id = (execution.""workflowId"")::text)"
              Heap Fetches: 0
Planning Time: 4.385 ms
Execution Time: 0.361 ms

Find waiting executions

Method: ExecutionRepository.getWaitingExecutions

Sample TypeORM log:

SELECT "ExecutionEntity"."id" AS "ExecutionEntity_id", "ExecutionEntity"."waitTill" AS "ExecutionEntity_waitTill" FROM "execution_entity" "ExecutionEntity" WHERE ( (("ExecutionEntity"."waitTill" <= ?) AND ("ExecutionEntity"."status" != ?)) ) AND ( "ExecutionEntity"."deletedAt" IS NULL ) ORDER BY "ExecutionEntity_waitTill" ASC -- PARAMETERS: ["2024-08-16 11:37:40.997","crashed"]

SQL query:

SELECT id AS "ExecutionEntity_id", "waitTill" AS "ExecutionEntity_waitTill" 
FROM execution_entity 
WHERE ("waitTill" <= '2024-08-16 11:37:40.997' AND status != 'crashed')
  AND "deletedAt" IS NULL 
ORDER BY "waitTill" ASC;

EXPLAIN QUERY PLAN on SQLite 3.43 on master

SEARCH execution_entity USING INDEX IDX_execution_entity_deletedAt (deletedAt=?)
USE TEMP B-TREE FOR ORDER BY

EXPLAIN ANALYZE on Postgres 13.7 on master

Sort  (cost=8.18..8.19 rows=1 width=12) (actual time=0.118..0.121 rows=0 loops=1)
"  Sort Key: ""waitTill"""
  Sort Method: quicksort  Memory: 25kB
"  ->  Index Scan using ""IDX_execution_entity_deletedAt"" on execution_entity  (cost=0.15..8.17 rows=1 width=12) (actual time=0.016..0.018 rows=0 loops=1)"
"        Index Cond: (""deletedAt"" IS NULL)"
"        Filter: ((""waitTill"" <= '2024-08-16 11:37:40.997+02'::timestamp with time zone) AND ((status)::text <> 'crashed'::text))"
Planning Time: 2.057 ms
Execution Time: 0.427 ms

EXPLAIN QUERY PLAN on SQLite 3.43 on pay-1609

SEARCH execution_entity USING INDEX IDX_execution_entity_deletedAt (deletedAt=?)
USE TEMP B-TREE FOR ORDER BY

EXPLAIN ANALYZE on Postgres 13.7 on pay-1609

Index Scan using idx_execution_entity_wait_till_status_deleted_at on execution_entity  (cost=0.12..8.14 rows=1 width=12) (actual time=0.007..0.008 rows=0 loops=1)
"  Index Cond: (""waitTill"" <= '2024-08-16 11:37:40.997+02'::timestamp with time zone)"
"  Filter: ((status)::text <> 'crashed'::text)"
Planning Time: 0.383 ms
Execution Time: 0.046 ms

Query to soft-delete executions

Method: ExecutionRepository.softDeletePrunableExecutions

Sample TypeORM log:

UPDATE "execution_entity" SET "deletedAt" = ? WHERE ("deletedAt" IS NULL AND NOT("status" IN (?, ?, ?))) AND ("stoppedAt" <= ?) -- PARAMETERS: ["2024-08-16 11:39:13.455","new","running","waiting","2024-08-02 11:39:13.442"]

SQL:

UPDATE execution_entity
SET "deletedAt" = '2024-08-16 11:39:13.455'
WHERE "deletedAt" IS NULL
  AND "status" NOT IN ('new', 'running', 'waiting')
  AND "stoppedAt" <= '2024-08-02 11:39:13.442';

EXPLAIN QUERY PLAN on SQLite 3.43 on master

SEARCH execution_entity USING INDEX IDX_execution_entity_deletedAt (deletedAt=?)

EXPLAIN ANALYZE on Postgres 13.7 on master

Update on execution_entity  (cost=0.15..8.17 rows=1 width=273) (actual time=0.006..0.008 rows=0 loops=1)
"  ->  Index Scan using ""IDX_execution_entity_deletedAt"" on execution_entity  (cost=0.15..8.17 rows=1 width=273) (actual time=0.004..0.004 rows=0 loops=1)"
"        Index Cond: (""deletedAt"" IS NULL)"
"        Filter: ((""stoppedAt"" <= '2024-08-02 11:39:13.442+02'::timestamp with time zone) AND ((status)::text <> ALL ('{new,running,waiting}'::text[])))"
Planning Time: 0.407 ms
Execution Time: 0.085 ms

EXPLAIN QUERY PLAN on SQLite 3.43 on pay-1609

SEARCH execution_entity USING INDEX IDX_execution_entity_deletedAt (deletedAt=?)

EXPLAIN ANALYZE on Postgres 13.7 on pay-1609

Update on execution_entity  (cost=0.12..8.15 rows=1 width=273) (actual time=0.015..0.021 rows=0 loops=1)
  ->  Index Scan using idx_execution_entity_stopped_at_status_deleted_at on execution_entity  (cost=0.12..8.15 rows=1 width=273) (actual time=0.010..0.011 rows=0 loops=1)
"        Index Cond: ((""stoppedAt"" <= '2024-08-02 11:39:13.442+02'::timestamp with time zone) AND (""deletedAt"" IS NULL))"
"        Filter: ((status)::text <> ALL ('{new,running,waiting}'::text[]))"
Planning Time: 0.487 ms
Execution Time: 0.196 ms

tomi

Nice stuff 🎉 Just one very small comment. Feel free to ignore it

tomi · 2024-08-22T10:36:02Z

packages/cli/src/databases/migrations/common/1723796243146-RefactorExecutionIndices.ts

+			await schemaBuilder.createIndex('execution_entity', ['workflowId', 'startedAt']);
+			await schemaBuilder.createIndex('execution_entity', ['waitTill', 'status', 'deletedAt']);
+			await schemaBuilder.createIndex('execution_entity', ['stoppedAt', 'status', 'deletedAt']);


Maybe these could be named explicitly as well, so the names would match for all different DBs

Thank you, since we'll be dropping MySQL, I think this is okay for now.

cypress · 2024-08-22T10:47:18Z

Test summary

414 • 0 • 0 • 0 • 1

Run details


Project	n8n
Status	Passed
Commit	`4068175`
Started	Aug 22, 2024 10:42 AM
Ended	Aug 22, 2024 10:47 AM
Duration	04:41 💡
OS	Linux Debian -
Browser	Electron 118

View run in Cypress Cloud ➡️

Flakiness

	e2e/17-sharing.cy.ts		1
1	Sharing > credentials should work between team and personal projects

This comment has been generated by cypress-bot as a result of this project's GitHub integration settings. You can manage this integration in this project's settings in the Cypress Cloud

github-actions · 2024-08-22T10:47:20Z

✅ All Cypress E2E specs passed

janober · 2024-08-28T11:48:49Z

Got released with n8n@1.57.0

perf(core): Speed up common execution queries

c1dda76

ivov changed the title ~~perf(core): Speed up common execution queries~~ perf(core): Accelerate common execution queries Jun 20, 2024

Remove disabling transactions

c2d0761

n8n-assistant bot added core Enhancement outside /nodes-base and /editor-ui n8n team Authored by the n8n team labels Jun 20, 2024

ivov added 3 commits June 20, 2024 14:35

Remove unneeded sorting on FE

dbb1c2e

Skip if missing on drop index

956ceea

Revert "Remove unneeded sorting on FE"

cd2ab81

This reverts commit dbb1c2e.

ivov marked this pull request as ready for review June 20, 2024 13:23

ivov requested a review from a team as a code owner June 20, 2024 13:23

krynble reviewed Jun 24, 2024

View reviewed changes

packages/cli/src/databases/migrations/common/1717498465932-RefactorExecutionIndices.ts Outdated Show resolved Hide resolved

ivov added 2 commits June 27, 2024 18:17

Merge branch 'master' into pay-1609

445c908

Apply feedback

7145462

netroy self-requested a review July 3, 2024 08:14

netroy reviewed Jul 4, 2024

View reviewed changes

packages/cli/src/databases/migrations/common/1717498465932-RefactorExecutionIndices.ts Outdated Show resolved Hide resolved

Merge master, fix conflicts

49d6e9a

netroy reviewed Jul 11, 2024

View reviewed changes

ivov added 3 commits July 11, 2024 12:16

Convert stoppedAt into composite index for soft deletions

dedb12a

Combine when dropping stoppedAt

059ddf9

Move skipIfMissing to DropIndex

dd17796

tomi reviewed Jul 30, 2024

View reviewed changes

ivov added 5 commits July 30, 2024 17:09

Merge branch 'master' into pay-1609

4fcce3d

Merge branch 'master' into pay-1609

84a0228

Use TypeORMError

fdc5909

Update migration timestamp

43e18a3

Use status, startedAt for default and filter query

21212e2

ivov changed the title ~~perf(core): Accelerate common execution queries~~ perf(core): Make execution queries faster Aug 16, 2024

Use partial indexes for PG and MySQL

90af0ba

ivov added 3 commits August 22, 2024 11:22

Merge master, fix conflicts

e021007

Sort migrations by date

9af725d

Apply feedback re: workflowId, startedAt index

4068175

tomi approved these changes Aug 22, 2024

View reviewed changes

ivov merged commit dc7dc99 into master Aug 22, 2024
41 checks passed

ivov deleted the pay-1609 branch August 22, 2024 11:27

This was referenced Aug 28, 2024

🚀 Release 1.57.0 #10584

Merged

🚀 Release 1.57.0 #10587

Merged

janober added the Released label Aug 28, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

perf(core): Make execution queries faster #9817

perf(core): Make execution queries faster #9817

ivov commented Jun 20, 2024 •

edited

Loading

krynble left a comment

ivov commented Jun 24, 2024

tomi left a comment

ivov commented Aug 16, 2024

tomi left a comment

tomi Aug 22, 2024

ivov Aug 22, 2024

cypress bot commented Aug 22, 2024

github-actions bot commented Aug 22, 2024

janober commented Aug 28, 2024

perf(core): Make execution queries faster #9817

perf(core): Make execution queries faster #9817

Conversation

ivov commented Jun 20, 2024 • edited Loading

krynble left a comment

Choose a reason for hiding this comment

ivov commented Jun 24, 2024

tomi left a comment

Choose a reason for hiding this comment

ivov commented Aug 16, 2024

Find multiple executions

Find waiting executions

Query to soft-delete executions

tomi left a comment

Choose a reason for hiding this comment

tomi Aug 22, 2024

Choose a reason for hiding this comment

ivov Aug 22, 2024

Choose a reason for hiding this comment

cypress bot commented Aug 22, 2024

Test summary

Run details

Flakiness

github-actions bot commented Aug 22, 2024

janober commented Aug 28, 2024

ivov commented Jun 20, 2024 •

edited

Loading