explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6k7k

Settings
# exclusive inclusive rows x rows loops node
1. 1.807 657.443 ↓ 279.0 279 1

Merge Join (cost=17,099,530.07..17,099,536.58 rows=1 width=2,105) (actual time=656.946..657.443 rows=279 loops=1)

  • Merge Cond: ((foo.actionid)::text = (survey_events.action_id)::text)
  • Join Filter: ((foo.templateid)::text = (survey_events.template_id)::text)
2. 14.431 655.504 ↓ 96.1 18,636 1

WindowAgg (cost=17,099,427.59..17,099,431.47 rows=194 width=2,103) (actual time=620.484..655.504 rows=18,636 loops=1)

3. 73.850 641.073 ↓ 96.1 18,636 1

Sort (cost=17,099,427.59..17,099,428.08 rows=194 width=2,095) (actual time=620.452..641.073 rows=18,636 loops=1)

  • Sort Key: foo.actionid
  • Sort Method: external merge Disk: 21728kB
4. 16.711 567.223 ↓ 96.1 18,636 1

WindowAgg (cost=17,099,415.85..17,099,420.22 rows=194 width=2,095) (actual time=517.253..567.223 rows=18,636 loops=1)

5. 105.367 550.512 ↓ 96.1 18,636 1

Sort (cost=17,099,415.85..17,099,416.34 rows=194 width=2,087) (actual time=517.245..550.512 rows=18,636 loops=1)

  • Sort Key: foo.parentsurveyquestionid, foo.actionid
  • Sort Method: external merge Disk: 21728kB
6. 14.527 445.145 ↓ 96.1 18,636 1

WindowAgg (cost=17,099,403.15..17,099,408.48 rows=194 width=2,087) (actual time=399.184..445.145 rows=18,636 loops=1)

7. 117.411 430.618 ↓ 96.1 18,636 1

Sort (cost=17,099,403.15..17,099,403.63 rows=194 width=2,079) (actual time=399.178..430.618 rows=18,636 loops=1)

  • Sort Key: foo.localcreatedat, foo.actionid, foo.parentsurveyquestionid, foo.question_order
  • Sort Method: external merge Disk: 21520kB
8. 2.684 313.207 ↓ 96.1 18,636 1

Subquery Scan on foo (cost=17,081,467.23..17,099,395.77 rows=194 width=2,079) (actual time=4.257..313.207 rows=18,636 loops=1)

9. 1.474 310.523 ↓ 96.1 18,636 1

Append (cost=17,081,467.23..17,099,393.83 rows=194 width=2,079) (actual time=4.257..310.523 rows=18,636 loops=1)

10.          

CTE primaryquestionscte

11. 0.788 59.164 ↓ 2.5 999 1

Nested Loop (cost=4.32..532.40 rows=400 width=139) (actual time=0.288..59.164 rows=999 loops=1)

12. 0.280 56.378 ↓ 2.5 999 1

Nested Loop (cost=4.32..27.40 rows=400 width=107) (actual time=0.276..56.378 rows=999 loops=1)

13. 0.512 0.546 ↓ 54.2 217 1

Bitmap Heap Scan on templates (cost=4.29..19.37 rows=4 width=427) (actual time=0.057..0.546 rows=217 loops=1)

  • Recheck Cond: (((template_type)::text = 'Survey'::text) AND (back_content ~ 'questions'::text))
  • Heap Blocks: exact=182
14. 0.034 0.034 ↓ 54.2 217 1

Bitmap Index Scan on ix_templates_tenant_key_template_id_type (cost=0.00..4.29 rows=4 width=0) (actual time=0.034..0.034 rows=217 loops=1)

15. 55.552 55.552 ↑ 20.0 5 217

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

16. 1.998 1.998 ↑ 1.0 1 999

Function Scan on jsonb_array_elements questions (cost=0.01..1.25 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=999)

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

CTE followupquestionscte

18. 1.745 64.101 ↓ 1.2 488 1

HashAggregate (cost=17,080,927.41..17,080,934.41 rows=400 width=167) (actual time=63.826..64.101 rows=488 loops=1)

  • Group Key: templates_1.template_id, templates_1.tenant_key, (childquestions.value ->> 'parentName'::text), (childquestions.value ->> 'name'::text), 0
19. 0.780 62.356 ↑ 257,732.0 1,552 1

Nested Loop (cost=4.33..10,080,927.41 rows=400,000,000 width=167) (actual time=0.257..62.356 rows=1,552 loops=1)

20. 0.391 60.598 ↑ 8,180.0 489 1

Nested Loop (cost=4.33..80,927.41 rows=4,000,000 width=131) (actual time=0.244..60.598 rows=489 loops=1)

21. 0.433 59.317 ↑ 44.9 890 1

Nested Loop (cost=4.32..927.40 rows=40,000 width=99) (actual time=0.237..59.317 rows=890 loops=1)

22. 0.265 55.887 ↓ 2.5 999 1

Nested Loop (cost=4.32..27.40 rows=400 width=99) (actual time=0.223..55.887 rows=999 loops=1)

23. 0.476 0.504 ↓ 54.2 217 1

Bitmap Heap Scan on templates templates_1 (cost=4.29..19.37 rows=4 width=427) (actual time=0.049..0.504 rows=217 loops=1)

  • Recheck Cond: (((template_type)::text = 'Survey'::text) AND (back_content ~ 'questions'::text))
  • Heap Blocks: exact=182
24. 0.028 0.028 ↓ 54.2 217 1

Bitmap Index Scan on ix_templates_tenant_key_template_id_type (cost=0.00..4.29 rows=4 width=0) (actual time=0.028..0.028 rows=217 loops=1)

25. 55.118 55.118 ↑ 20.0 5 217

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

26. 2.997 2.997 ↑ 100.0 1 999

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

  • Filter: ((value ->> 'childQuestions'::text) IS NOT NULL)
  • Rows Removed by Filter: 1
27. 0.890 0.890 ↑ 100.0 1 890

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

28. 0.978 0.978 ↑ 33.3 3 489

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

29. 10.208 82.998 ↓ 120.0 11,642 1

Nested Loop (cost=0.42..3,132.73 rows=97 width=1,533) (actual time=4.256..82.998 rows=11,642 loops=1)

30. 59.803 59.803 ↓ 2.5 999 1

CTE Scan on primaryquestionscte (cost=0.00..8.00 rows=400 width=218) (actual time=0.289..59.803 rows=999 loops=1)

31. 12.987 12.987 ↓ 12.0 12 999

Index Scan using ix_survey_responses_tenant_key_template_id_survey_question_id on survey_responses (cost=0.42..7.80 rows=1 width=1,433) (actual time=0.004..0.013 rows=12 loops=999)

  • Index Cond: (((tenant_key)::text = '48a63f3aad918e97028ef9e0b8e828c2403a50a3781500b46e04236e03d755f7'::text) AND ((template_id)::text = (primaryquestionscte.template_id)::text) AND ((survey_question_id)::text = primaryquestionscte.survey_question_id))
32. 1.464 226.051 ↓ 72.1 6,994 1

Subquery Scan on *SELECT* 2 (cost=3,133.70..14,793.32 rows=97 width=1,519) (actual time=100.224..226.051 rows=6,994 loops=1)

33. 123.541 224.587 ↓ 72.1 6,994 1

Hash Right Join (cost=3,133.70..14,792.11 rows=97 width=1,515) (actual time=100.219..224.587 rows=6,994 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))
34. 12.934 12.934 ↑ 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.071..12.934 rows=65,487 loops=1)

35. 13.075 88.112 ↓ 72.1 6,994 1

Hash (cost=3,132.00..3,132.00 rows=97 width=1,491) (actual time=88.112..88.112 rows=6,994 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 4 (originally 1) Memory Usage: 4065kB
36. 3.792 75.037 ↓ 72.1 6,994 1

Nested Loop (cost=0.42..3,132.00 rows=97 width=1,491) (actual time=64.220..75.037 rows=6,994 loops=1)

37. 64.413 64.413 ↓ 1.2 488 1

CTE Scan on followupquestionscte (cost=0.00..8.00 rows=400 width=242) (actual time=63.829..64.413 rows=488 loops=1)

38. 6.832 6.832 ↓ 14.0 14 488

Index Scan using ix_survey_responses_tenant_key_template_id_survey_question_id on survey_responses survey_responses_1 (cost=0.42..7.80 rows=1 width=1,433) (actual time=0.004..0.014 rows=14 loops=488)

  • Index Cond: (((tenant_key)::text = '48a63f3aad918e97028ef9e0b8e828c2403a50a3781500b46e04236e03d755f7'::text) AND ((template_id)::text = (followupquestionscte.template_id)::text) AND ((survey_question_id)::text = followupquestionscte.parent_survey_question_id))
39. 0.071 0.132 ↓ 8.2 272 1

Sort (cost=102.48..102.56 rows=33 width=75) (actual time=0.107..0.132 rows=272 loops=1)

  • Sort Key: survey_events.action_id
  • Sort Method: quicksort Memory: 29kB
40. 0.038 0.061 ↑ 1.0 32 1

Bitmap Heap Scan on survey_events (cost=4.54..101.65 rows=33 width=75) (actual time=0.032..0.061 rows=32 loops=1)

  • Recheck Cond: ((tenant_key)::text = '48a63f3aad918e97028ef9e0b8e828c2403a50a3781500b46e04236e03d755f7'::text)
  • Heap Blocks: exact=28
41. 0.023 0.023 ↑ 1.0 32 1

Bitmap Index Scan on ix_survey_events_tenant_key_template_id (cost=0.00..4.53 rows=33 width=0) (actual time=0.023..0.023 rows=32 loops=1)

  • Index Cond: ((tenant_key)::text = '48a63f3aad918e97028ef9e0b8e828c2403a50a3781500b46e04236e03d755f7'::text)