explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xEgW

Settings
# exclusive inclusive rows x rows loops node
1. 25.363 566.750 ↓ 4,996.0 4,996 1

Hash Join (cost=4,283,714.16..4,284,048.71 rows=1 width=2,105) (actual time=540.833..566.750 rows=4,996 loops=1)

  • Hash Cond: (((survey_events.action_id)::text = (a.actionid)::text) AND ((survey_events.template_id)::text = (a.templateid)::text))
2. 0.799 0.799 ↑ 1.0 646 1

Seq Scan on survey_events (cost=0.00..329.69 rows=647 width=75) (actual time=0.135..0.799 rows=646 loops=1)

  • Filter: ((tenant_key)::text = 'f8792720e1184218e31f7bbc41246beea02d629588d604a786c8db87dde3e602'::text)
  • Rows Removed by Filter: 1622
3. 28.576 540.588 ↓ 201.0 18,896 1

Hash (cost=4,283,712.75..4,283,712.75 rows=94 width=2,103) (actual time=540.588..540.588 rows=18,896 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 8 (originally 1) Memory Usage: 4034kB
4. 2.677 512.012 ↓ 201.1 18,904 1

Subquery Scan on a (cost=4,283,709.93..4,283,712.75 rows=94 width=2,103) (actual time=473.517..512.012 rows=18,904 loops=1)

5. 15.683 509.335 ↓ 201.1 18,904 1

WindowAgg (cost=4,283,709.93..4,283,711.81 rows=94 width=2,103) (actual time=473.516..509.335 rows=18,904 loops=1)

6. 71.971 493.652 ↓ 201.1 18,904 1

Sort (cost=4,283,709.93..4,283,710.16 rows=94 width=2,095) (actual time=473.506..493.652 rows=18,904 loops=1)

  • Sort Key: foo.actionid
  • Sort Method: external merge Disk: 21984kB
7. 16.514 421.681 ↓ 201.1 18,904 1

WindowAgg (cost=4,283,704.73..4,283,706.85 rows=94 width=2,095) (actual time=381.562..421.681 rows=18,904 loops=1)

8. 94.957 405.167 ↓ 201.1 18,904 1

Sort (cost=4,283,704.73..4,283,704.97 rows=94 width=2,087) (actual time=381.554..405.167 rows=18,904 loops=1)

  • Sort Key: foo.parentsurveyquestionid, foo.actionid
  • Sort Method: external merge Disk: 21952kB
9. 14.596 310.210 ↓ 201.1 18,904 1

WindowAgg (cost=4,283,699.06..4,283,701.65 rows=94 width=2,087) (actual time=270.258..310.210 rows=18,904 loops=1)

10. 106.389 295.614 ↓ 201.1 18,904 1

Sort (cost=4,283,699.06..4,283,699.30 rows=94 width=2,079) (actual time=270.251..295.614 rows=18,904 loops=1)

  • Sort Key: foo.localcreatedat, foo.actionid, foo.parentsurveyquestionid, foo.question_order
  • Sort Method: external merge Disk: 21752kB
11. 2.829 189.225 ↓ 201.1 18,904 1

Subquery Scan on foo (cost=4,270,374.07..4,283,695.98 rows=94 width=2,079) (actual time=0.549..189.225 rows=18,904 loops=1)

12. 1.738 186.396 ↓ 201.1 18,904 1

Append (cost=4,270,374.07..4,283,695.04 rows=94 width=2,079) (actual time=0.549..186.396 rows=18,904 loops=1)

13.          

CTE primaryquestionscte

14. 0.019 1.576 ↑ 5.9 17 1

Nested Loop (cost=0.30..136.57 rows=100 width=139) (actual time=0.087..1.576 rows=17 loops=1)

15. 0.015 1.472 ↑ 5.9 17 1

Nested Loop (cost=0.30..10.31 rows=100 width=107) (actual time=0.078..1.472 rows=17 loops=1)

16. 0.017 0.017 ↓ 5.0 5 1

Index Scan using ix_templates_tenant_key_template_id_type on templates (cost=0.27..8.29 rows=1 width=427) (actual time=0.009..0.017 rows=5 loops=1)

  • Index Cond: ((tenant_key)::text = 'f8792720e1184218e31f7bbc41246beea02d629588d604a786c8db87dde3e602'::text)
17. 1.440 1.440 ↑ 33.3 3 5

Function Scan on jsonb_array_elements pages (cost=0.03..1.03 rows=100 width=40) (actual time=0.288..0.288 rows=3 loops=5)

18. 0.085 0.085 ↑ 1.0 1 17

Function Scan on jsonb_array_elements questions (cost=0.01..1.25 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=17)

  • Filter: ((value ->> 'visibleIf'::text) IS NULL)
  • Rows Removed by Filter: 1
19.          

CTE followupquestionscte

20. 0.064 1.631 ↑ 7.7 13 1

HashAggregate (cost=4,270,235.33..4,270,237.08 rows=100 width=167) (actual time=1.619..1.631 rows=13 loops=1)

  • Group Key: templates_1.template_id, templates_1.tenant_key, (childquestions.value ->> 'parentName'::text), (childquestions.value ->> 'name'::text), 0
21. 0.042 1.567 ↑ 1,818,181.8 55 1

Nested Loop (cost=0.31..2,520,235.33 rows=100,000,000 width=167) (actual time=0.501..1.567 rows=55 loops=1)

22. 0.002 1.499 ↑ 76,923.1 13 1

Nested Loop (cost=0.31..20,235.32 rows=1,000,000 width=131) (actual time=0.489..1.499 rows=13 loops=1)

23. 0.004 1.469 ↑ 714.3 14 1

Nested Loop (cost=0.30..235.32 rows=10,000 width=99) (actual time=0.481..1.469 rows=14 loops=1)

24. 0.005 1.380 ↑ 5.9 17 1

Nested Loop (cost=0.30..10.31 rows=100 width=99) (actual time=0.063..1.380 rows=17 loops=1)

25. 0.015 0.015 ↓ 5.0 5 1

Index Scan using ix_templates_tenant_key_template_id_type on templates templates_1 (cost=0.27..8.29 rows=1 width=427) (actual time=0.007..0.015 rows=5 loops=1)

  • Index Cond: ((tenant_key)::text = 'f8792720e1184218e31f7bbc41246beea02d629588d604a786c8db87dde3e602'::text)
26. 1.360 1.360 ↑ 33.3 3 5

Function Scan on jsonb_array_elements pages_1 (cost=0.03..1.03 rows=100 width=32) (actual time=0.271..0.272 rows=3 loops=5)

27. 0.085 0.085 ↑ 100.0 1 17

Function Scan on jsonb_array_elements questions_1 (cost=0.01..1.25 rows=100 width=32) (actual time=0.004..0.005 rows=1 loops=17)

  • Filter: ((value ->> 'childQuestions'::text) IS NOT NULL)
  • Rows Removed by Filter: 1
28. 0.028 0.028 ↑ 100.0 1 14

Function Scan on jsonb_array_elements childquestions (cost=0.01..1.00 rows=100 width=32) (actual time=0.002..0.002 rows=1 loops=14)

29. 0.026 0.026 ↑ 25.0 4 13

Function Scan on jsonb_array_elements followups (cost=0.01..1.00 rows=100 width=32) (actual time=0.002..0.002 rows=4 loops=13)

30. 10.048 20.921 ↓ 250.3 11,766 1

Nested Loop (cost=0.42..831.35 rows=47 width=1,533) (actual time=0.549..20.921 rows=11,766 loops=1)

31. 1.591 1.591 ↑ 5.9 17 1

CTE Scan on primaryquestionscte (cost=0.00..2.00 rows=100 width=218) (actual time=0.089..1.591 rows=17 loops=1)

32. 9.282 9.282 ↓ 692.0 692 17

Index Scan using ix_survey_responses_tenant_key_template_id_survey_question_id on survey_responses (cost=0.42..8.28 rows=1 width=1,433) (actual time=0.008..0.546 rows=692 loops=17)

  • Index Cond: (((tenant_key)::text = 'f8792720e1184218e31f7bbc41246beea02d629588d604a786c8db87dde3e602'::text) AND ((template_id)::text = (primaryquestionscte.template_id)::text) AND ((survey_question_id)::text = primaryquestionscte.survey_question_id))
33. 1.690 163.737 ↓ 151.9 7,138 1

Subquery Scan on *SELECT* 2 (cost=831.82..12,489.57 rows=47 width=1,519) (actual time=22.580..163.737 rows=7,138 loops=1)

34. 125.995 162.047 ↓ 151.9 7,138 1

Hash Right Join (cost=831.82..12,488.98 rows=47 width=1,515) (actual time=22.576..162.047 rows=7,138 loops=1)

  • Hash Cond: (((test.survey_question_id)::text = followupquestionscte.survey_question_id) AND ((test.action_id)::text = (survey_responses_1.action_id)::text) AND ((test.template_id)::text = (survey_responses_1.template_id)::text))
35. 13.808 13.808 ↑ 1.0 65,487 1

Seq Scan on survey_responses test (cost=0.00..10,907.93 rows=66,493 width=65) (actual time=0.089..13.808 rows=65,487 loops=1)

36. 12.936 22.244 ↓ 151.9 7,138 1

Hash (cost=831.00..831.00 rows=47 width=1,491) (actual time=22.244..22.244 rows=7,138 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 4 (originally 1) Memory Usage: 4066kB
37. 3.273 9.308 ↓ 151.9 7,138 1

Nested Loop (cost=0.42..831.00 rows=47 width=1,491) (actual time=1.641..9.308 rows=7,138 loops=1)

38. 1.641 1.641 ↑ 7.7 13 1

CTE Scan on followupquestionscte (cost=0.00..2.00 rows=100 width=242) (actual time=1.621..1.641 rows=13 loops=1)

39. 4.394 4.394 ↓ 549.0 549 13

Index Scan using ix_survey_responses_tenant_key_template_id_survey_question_id on survey_responses survey_responses_1 (cost=0.42..8.28 rows=1 width=1,433) (actual time=0.006..0.338 rows=549 loops=13)

  • Index Cond: (((tenant_key)::text = 'f8792720e1184218e31f7bbc41246beea02d629588d604a786c8db87dde3e602'::text) AND ((template_id)::text = (followupquestionscte.template_id)::text) AND ((survey_question_id)::text = followupquestionscte.parent_survey_question_id))
Planning time : 7.200 ms
Execution time : 580.158 ms