explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3L2n

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 841.775 ↓ 0.0 0 1

Nested Loop (cost=17,121,630.70..17,121,642.93 rows=1 width=2,105) (actual time=841.775..841.775 rows=0 loops=1)

  • Join Filter: ((a.actionid)::text = (survey_events.action_id)::text)
2. 1.598 841.773 ↓ 0.0 0 1

Subquery Scan on a (cost=17,121,630.42..17,121,634.62 rows=1 width=2,103) (actual time=841.773..841.773 rows=0 loops=1)

  • Filter: ((a.templateid)::text = '188'::text)
  • Rows Removed by Filter: 18636
3. 14.393 840.175 ↓ 144.5 18,636 1

WindowAgg (cost=17,121,630.42..17,121,633.00 rows=129 width=2,103) (actual time=805.779..840.175 rows=18,636 loops=1)

4. 73.856 825.782 ↓ 144.5 18,636 1

Sort (cost=17,121,630.42..17,121,630.75 rows=129 width=2,095) (actual time=805.724..825.782 rows=18,636 loops=1)

  • Sort Key: foo.actionid
  • Sort Method: external merge Disk: 21728kB
5. 17.317 751.926 ↓ 144.5 18,636 1

WindowAgg (cost=17,121,623.00..17,121,625.90 rows=129 width=2,095) (actual time=699.434..751.926 rows=18,636 loops=1)

6. 106.659 734.609 ↓ 144.5 18,636 1

Sort (cost=17,121,623.00..17,121,623.32 rows=129 width=2,087) (actual time=699.426..734.609 rows=18,636 loops=1)

  • Sort Key: foo.parentsurveyquestionid, foo.actionid
  • Sort Method: external merge Disk: 21728kB
7. 14.171 627.950 ↓ 144.5 18,636 1

WindowAgg (cost=17,121,614.93..17,121,618.48 rows=129 width=2,087) (actual time=583.459..627.950 rows=18,636 loops=1)

8. 106.357 613.779 ↓ 144.5 18,636 1

Sort (cost=17,121,614.93..17,121,615.25 rows=129 width=2,079) (actual time=583.450..613.779 rows=18,636 loops=1)

  • Sort Key: foo.localcreatedat, foo.actionid, foo.parentsurveyquestionid, foo.question_order
  • Sort Method: external merge Disk: 21520kB
9. 4.830 507.422 ↓ 144.5 18,636 1

Subquery Scan on foo (cost=17,121,608.79..17,121,610.41 rows=129 width=2,079) (actual time=490.350..507.422 rows=18,636 loops=1)

10. 54.138 502.592 ↓ 144.5 18,636 1

Sort (cost=17,121,608.79..17,121,609.12 rows=129 width=2,079) (actual time=490.348..502.592 rows=18,636 loops=1)

  • Sort Key: pages.num
  • Sort Method: external merge Disk: 21672kB
11. 1.649 448.454 ↓ 144.5 18,636 1

Append (cost=4.74..17,121,604.27 rows=129 width=2,079) (actual time=4.323..448.454 rows=18,636 loops=1)

12. 12.731 84.193 ↓ 363.8 11,642 1

Nested Loop (cost=4.74..3,912.72 rows=32 width=1,533) (actual time=4.323..84.193 rows=11,642 loops=1)

13. 0.397 58.475 ↓ 2.5 999 1

Nested Loop (cost=4.32..531.40 rows=400 width=43) (actual time=0.261..58.475 rows=999 loops=1)

14. 0.328 56.080 ↓ 2.5 999 1

Nested Loop (cost=4.32..27.40 rows=400 width=43) (actual time=0.252..56.080 rows=999 loops=1)

15. 0.387 0.417 ↓ 54.2 217 1

Bitmap Heap Scan on templates (cost=4.29..19.37 rows=4 width=363) (actual time=0.054..0.417 rows=217 loops=1)

  • Recheck Cond: (((template_type)::text = 'Survey'::text) AND (back_content ~ 'questions'::text))
  • Heap Blocks: exact=182
16. 0.030 0.030 ↓ 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.030..0.030 rows=217 loops=1)

17. 55.335 55.335 ↑ 20.0 5 217

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

18. 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
19. 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..8.44 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 = (templates.template_id)::text) AND ((survey_question_id)::text = (questions.value ->> 'name'::text)))
20. 1.424 362.612 ↓ 72.1 6,994 1

Subquery Scan on *SELECT* 2 (cost=17,106,031.61..17,117,691.23 rows=97 width=1,519) (actual time=237.431..362.612 rows=6,994 loops=1)

21. 122.739 361.188 ↓ 72.1 6,994 1

Hash Right Join (cost=17,106,031.61..17,117,690.02 rows=97 width=1,515) (actual time=237.425..361.188 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))
22. 12.948 12.948 ↑ 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.088..12.948 rows=65,487 loops=1)

23. 11.261 225.501 ↓ 72.1 6,994 1

Hash (cost=17,106,029.91..17,106,029.91 rows=97 width=1,491) (actual time=225.501..225.501 rows=6,994 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 4 (originally 1) Memory Usage: 4066kB
24. 6.311 214.240 ↓ 72.1 6,994 1

Merge Join (cost=17,105,876.91..17,106,029.91 rows=97 width=1,491) (actual time=172.555..214.240 rows=6,994 loops=1)

  • Merge Cond: (((survey_responses_1.template_id)::text = (followupquestionscte.template_id)::text) AND ((survey_responses_1.survey_question_id)::text = followupquestionscte.parent_survey_question_id))
25. 111.200 142.326 ↑ 1.0 19,264 1

Sort (cost=24,921.21..24,970.89 rows=19,870 width=1,433) (actual time=106.998..142.326 rows=19,264 loops=1)

  • Sort Key: survey_responses_1.template_id, survey_responses_1.survey_question_id
  • Sort Method: external merge Disk: 21056kB
26. 31.126 31.126 ↑ 1.0 19,264 1

Seq Scan on survey_responses survey_responses_1 (cost=0.00..11,074.16 rows=19,870 width=1,433) (actual time=4.271..31.126 rows=19,264 loops=1)

  • Filter: ((tenant_key)::text = '48a63f3aad918e97028ef9e0b8e828c2403a50a3781500b46e04236e03d755f7'::text)
  • Rows Removed by Filter: 46223
27. 1.370 65.603 ↓ 17.8 7,132 1

Sort (cost=17,080,955.70..17,080,956.70 rows=400 width=99) (actual time=65.069..65.603 rows=7,132 loops=1)

  • Sort Key: followupquestionscte.template_id, followupquestionscte.parent_survey_question_id
  • Sort Method: quicksort Memory: 141kB
28. 0.042 64.233 ↓ 1.2 488 1

Subquery Scan on followupquestionscte (cost=17,080,927.41..17,080,938.41 rows=400 width=99) (actual time=64.004..64.233 rows=488 loops=1)

29. 1.693 64.191 ↓ 1.2 488 1

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

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

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

31. 0.406 60.848 ↑ 8,180.0 489 1

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

32. 0.390 59.552 ↑ 44.9 890 1

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

33. 0.317 56.165 ↓ 2.5 999 1

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

34. 0.477 0.513 ↓ 54.2 217 1

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

  • Recheck Cond: (((template_type)::text = 'Survey'::text) AND (back_content ~ 'questions'::text))
  • Heap Blocks: exact=182
35. 0.036 0.036 ↓ 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.036..0.036 rows=217 loops=1)

36. 55.335 55.335 ↑ 20.0 5 217

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

37. 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
38. 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)

39. 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)

40. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_survey_events_tenant_key_template_id on survey_events (cost=0.28..8.30 rows=1 width=75) (never executed)

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