explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZLSa

Settings
# exclusive inclusive rows x rows loops node
1. 51.402 35,348.565 ↓ 170.2 851 1

GroupAggregate (cost=28,216.15..28,216.49 rows=5 width=399) (actual time=35,294.299..35,348.565 rows=851 loops=1)

  • Group Key: messages.id, clients.id, pq.start_date, wk.id, cmp.id, message_metrics.id
2. 21.331 35,297.163 ↓ 4,275.8 21,379 1

Sort (cost=28,216.15..28,216.17 rows=5 width=273) (actual time=35,294.185..35,297.163 rows=21,379 loops=1)

  • Sort Key: messages.id, pq.start_date, wk.id, cmp.id, message_metrics.id
  • Sort Method: quicksort Memory: 14,878kB
3. 63.259 35,275.832 ↓ 4,275.8 21,379 1

Hash Right Join (cost=3,879.04..28,216.10 rows=5 width=273) (actual time=35,140.002..35,275.832 rows=21,379 loops=1)

  • Hash Cond: ((dtgv.descriptive_tag_id = dim.taggable_id) AND (('DescriptiveTag'::text) = (dim.taggable_type)::text) AND (dtgv.gene_value_id = mgv.gene_value_id))
4. 34.525 79.709 ↑ 1.0 461,846 1

Append (cost=0.00..14,522.82 rows=461,846 width=48) (actual time=0.013..79.709 rows=461,846 loops=1)

5. 44.950 44.950 ↑ 1.0 460,712 1

Seq Scan on descriptive_tags_gene_values dtgv (cost=0.00..7,569.12 rows=460,712 width=48) (actual time=0.012..44.950 rows=460,712 loops=1)

6. 0.108 0.234 ↑ 1.0 1,134 1

Subquery Scan on *SELECT* 2 (cost=0.00..37.35 rows=1,134 width=48) (actual time=0.013..0.234 rows=1,134 loops=1)

7. 0.126 0.126 ↑ 1.0 1,134 1

Seq Scan on custom_tags_gene_values ctgv (cost=0.00..20.34 rows=1,134 width=40) (actual time=0.011..0.126 rows=1,134 loops=1)

8. 21.659 35,132.864 ↓ 4,275.8 21,379 1

Hash (cost=3,878.95..3,878.95 rows=5 width=259) (actual time=35,132.863..35,132.864 rows=21,379 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 10,740kB
9. 8.794 35,111.205 ↓ 4,275.8 21,379 1

Nested Loop Left Join (cost=2,742.53..3,878.95 rows=5 width=259) (actual time=52.739..35,111.205 rows=21,379 loops=1)

10. 17.005 35,083.951 ↓ 4,615.0 4,615 1

Nested Loop Left Join (cost=2,742.11..3,877.59 rows=1 width=241) (actual time=52.735..35,083.951 rows=4,615 loops=1)

  • Join Filter: (fim.functional_insight_report_id = fir.id)
  • Rows Removed by Join Filter: 260,214
11. 2.576 34,953.671 ↓ 4,531.0 4,531 1

Nested Loop Left Join (cost=2,741.83..3,872.67 rows=1 width=245) (actual time=52.729..34,953.671 rows=4,531 loops=1)

12. 2.176 34,942.033 ↓ 4,531.0 4,531 1

Nested Loop Left Join (cost=2,741.55..3,872.37 rows=1 width=249) (actual time=52.722..34,942.033 rows=4,531 loops=1)

13. 3.595 34,930.795 ↓ 4,531.0 4,531 1

Nested Loop Left Join (cost=2,741.26..3,872.05 rows=1 width=241) (actual time=52.716..34,930.795 rows=4,531 loops=1)

14. 5.104 34,913.607 ↓ 4,531.0 4,531 1

Nested Loop Left Join (cost=2,740.97..3,871.72 rows=1 width=237) (actual time=52.706..34,913.607 rows=4,531 loops=1)

15. 3.374 34,903.972 ↓ 4,531.0 4,531 1

Nested Loop Left Join (cost=2,740.82..3,871.55 rows=1 width=237) (actual time=52.702..34,903.972 rows=4,531 loops=1)

16. 5.230 34,887.005 ↓ 4,531.0 4,531 1

Nested Loop Left Join (cost=2,740.40..3,871.10 rows=1 width=229) (actual time=52.695..34,887.005 rows=4,531 loops=1)

17. 10,235.056 34,863.651 ↓ 4,531.0 4,531 1

Nested Loop (cost=2,740.12..3,870.24 rows=1 width=229) (actual time=52.684..34,863.651 rows=4,531 loops=1)

  • Join Filter: ((gra."primary" OR ((count(gra_1.response_action_id)) = 1)) AND (an.grammar_id = gra_1.grammar_id))
  • Rows Removed by Join Filter: 181,610,423
18. 7.838 123.125 ↓ 4,995.0 4,995 1

Hash Left Join (cost=11.35..1,024.22 rows=1 width=262) (actual time=0.240..123.125 rows=4,995 loops=1)

  • Hash Cond: (ftgv.formatting_tag_id = ft.id)
  • Filter: ((ft.id IS NULL) OR ((118 = ANY ((concat('{', ft.ancestor_ids, '}'))::integer[])) AND (genes.name = ANY ('{symbol,CTA_symbol,cta_symbol}'::text[])) AND (NOT (SubPlan 1)) AND (NOT (SubPlan 3))))
  • Rows Removed by Filter: 182
19. 2.518 79.915 ↓ 1,294.2 5,177 1

Nested Loop Left Join (cost=7.73..1,020.59 rows=4 width=278) (actual time=0.208..79.915 rows=5,177 loops=1)

20. 7.190 67.043 ↓ 1,294.2 5,177 1

Nested Loop (cost=7.44..1,019.31 rows=4 width=270) (actual time=0.202..67.043 rows=5,177 loops=1)

21. 7.137 49.499 ↓ 1,294.2 5,177 1

Nested Loop (cost=7.01..1,017.50 rows=4 width=262) (actual time=0.195..49.499 rows=5,177 loops=1)

22. 4.368 26.831 ↓ 1,294.2 5,177 1

Nested Loop (cost=6.59..1,015.58 rows=4 width=216) (actual time=0.188..26.831 rows=5,177 loops=1)

  • Join Filter: (messages.id = mgv.message_id)
23. 1.955 15.967 ↓ 928.0 928 1

Nested Loop (cost=6.16..1,014.95 rows=1 width=216) (actual time=0.179..15.967 rows=928 loops=1)

  • Join Filter: (an.grammar_id = messages.grammar_id)
24. 0.953 9.027 ↓ 110.8 997 1

Nested Loop (cost=5.73..1,010.15 rows=9 width=100) (actual time=0.172..9.027 rows=997 loops=1)

25. 0.198 6.832 ↓ 69.0 69 1

Nested Loop (cost=5.31..1,008.61 rows=1 width=73) (actual time=0.165..6.832 rows=69 loops=1)

  • Join Filter: (ra.id = gra.response_action_id)
26. 0.163 6.289 ↓ 69.0 69 1

Nested Loop (cost=4.88..1,008.06 rows=1 width=80) (actual time=0.158..6.289 rows=69 loops=1)

27. 0.082 5.706 ↓ 105.0 105 1

Nested Loop (cost=4.59..1,007.72 rows=1 width=80) (actual time=0.086..5.706 rows=105 loops=1)

28. 0.198 5.105 ↓ 10.8 173 1

Nested Loop (cost=4.32..1,002.94 rows=16 width=72) (actual time=0.081..5.105 rows=173 loops=1)

  • Join Filter: (wk.channel_id = ch.id)
  • Rows Removed by Join Filter: 1,202
29. 0.202 4.734 ↓ 10.8 173 1

Nested Loop (cost=4.32..995.50 rows=16 width=80) (actual time=0.071..4.734 rows=173 loops=1)

30. 0.077 4.013 ↓ 10.8 173 1

Nested Loop (cost=4.03..990.12 rows=16 width=88) (actual time=0.064..4.013 rows=173 loops=1)

31. 0.008 0.008 ↑ 1.0 1 1

Index Scan using idx_25162_primary on clients (cost=0.28..2.49 rows=1 width=16) (actual time=0.007..0.008 rows=1 loops=1)

  • Index Cond: (id = 674)
32. 0.186 3.928 ↓ 10.8 173 1

Nested Loop (cost=3.75..987.47 rows=16 width=80) (actual time=0.054..3.928 rows=173 loops=1)

33. 0.226 3.082 ↑ 1.1 110 1

Nested Loop (cost=3.33..898.80 rows=117 width=48) (actual time=0.046..3.082 rows=110 loops=1)

34. 0.194 1.872 ↑ 1.7 164 1

Nested Loop (cost=2.91..672.86 rows=285 width=32) (actual time=0.038..1.872 rows=164 loops=1)

35. 0.105 0.814 ↑ 2.7 144 1

Nested Loop (cost=2.62..502.40 rows=388 width=24) (actual time=0.029..0.814 rows=144 loops=1)

36. 0.210 0.221 ↑ 1.0 122 1

Bitmap Heap Scan on campaigns cmp (cost=2.33..118.57 rows=122 width=16) (actual time=0.022..0.221 rows=122 loops=1)

  • Recheck Cond: (client_id = 674)
  • Heap Blocks: exact=102
37. 0.011 0.011 ↑ 1.0 122 1

Bitmap Index Scan on idx_25121_index_campaigns_on_client_id (cost=0.00..2.30 rows=122 width=0) (actual time=0.011..0.011 rows=122 loops=1)

  • Index Cond: (client_id = 674)
38. 0.488 0.488 ↑ 4.0 1 122

Index Scan using index_sensei_experiments_on_campaign_id on sensei_experiments exp (cost=0.29..3.11 rows=4 width=16) (actual time=0.003..0.004 rows=1 loops=122)

  • Index Cond: (campaign_id = cmp.id)
39. 0.864 0.864 ↑ 1.0 1 144

Index Scan using idx_26018_index_workspaces_on_experiment_id on workspaces wk (cost=0.29..0.43 rows=1 width=24) (actual time=0.004..0.006 rows=1 loops=144)

  • Index Cond: (experiment_id = exp.id)
  • Filter: (opt_in AND (NOT exclude_from_reporting) AND (build_state = 'workable'::text) AND (phase_type = 'exploration'::text))
  • Rows Removed by Filter: 1
40. 0.984 0.984 ↑ 1.0 1 164

Index Scan using idx_25557_index_grammars_on_workspace_id on grammars gr (cost=0.42..0.78 rows=1 width=16) (actual time=0.005..0.006 rows=1 loops=164)

  • Index Cond: (workspace_id = wk.id)
  • Filter: (state = 'analyzed'::text)
  • Rows Removed by Filter: 3
41. 0.660 0.660 ↓ 2.0 2 110

Index Scan using idx_234_action_approved on analyses an (cost=0.42..0.75 rows=1 width=32) (actual time=0.005..0.006 rows=2 loops=110)

  • Index Cond: ((grammar_id = gr.id) AND (approved = true))
  • Filter: (approved AND approved_for_reporting)
  • Rows Removed by Filter: 0
42. 0.519 0.519 ↑ 1.0 1 173

Index Only Scan using idx_25874_index_segment_groups_on_workspace_id on segment_groups sg (cost=0.29..0.34 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=173)

  • Index Cond: (workspace_id = wk.id)
  • Heap Fetches: 173
43. 0.166 0.173 ↑ 3.4 8 173

Materialize (cost=0.00..1.41 rows=27 width=8) (actual time=0.000..0.001 rows=8 loops=173)

44. 0.007 0.007 ↑ 1.7 16 1

Seq Scan on channels ch (cost=0.00..1.27 rows=27 width=8) (actual time=0.004..0.007 rows=16 loops=1)

45. 0.519 0.519 ↑ 1.0 1 173

Index Scan using idx_25820_primary on response_actions ra (cost=0.28..0.30 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=173)

  • Index Cond: (id = an.response_action_id)
  • Filter: (kind = 'instant'::text)
  • Rows Removed by Filter: 0
46. 0.420 0.420 ↑ 1.0 1 105

Index Scan using idx_25722_primary on performance_queries pq (cost=0.29..0.34 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=105)

  • Index Cond: (id = an.performance_query_id)
  • Filter: ((start_date >= '2019-05-27 00:00:00'::timestamp without time zone) AND (start_date <= '2020-06-26 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 0
47. 0.345 0.345 ↑ 1.0 1 69

Index Scan using idx_3241_grammar_id_response_action_id on grammar_response_actions gra (cost=0.42..0.53 rows=1 width=17) (actual time=0.005..0.005 rows=1 loops=69)

  • Index Cond: ((grammar_id = an.grammar_id) AND (response_action_id = an.response_action_id))
48. 1.242 1.242 ↑ 1.0 14 69

Index Scan using index_message_metrics_on_analysis_id on message_metrics (cost=0.43..1.40 rows=14 width=35) (actual time=0.005..0.018 rows=14 loops=69)

  • Index Cond: (analysis_id = an.id)
49. 4.985 4.985 ↑ 1.0 1 997

Index Scan using idx_25659_primary on messages (cost=0.42..0.52 rows=1 width=116) (actual time=0.005..0.005 rows=1 loops=997)

  • Index Cond: (id = message_metrics.message_id)
  • Filter: (message_grammar_type IS NULL)
  • Rows Removed by Filter: 0
50. 6.496 6.496 ↑ 1.0 6 928

Index Scan using idx_25668_index_message_gene_values_on_message_id on message_gene_values mgv (cost=0.43..0.56 rows=6 width=16) (actual time=0.004..0.007 rows=6 loops=928)

  • Index Cond: (message_id = message_metrics.message_id)
51. 15.531 15.531 ↑ 1.0 1 5,177

Index Scan using idx_25507_primary on gene_values (cost=0.42..0.48 rows=1 width=46) (actual time=0.003..0.003 rows=1 loops=5,177)

  • Index Cond: (id = mgv.gene_value_id)
52. 10.354 10.354 ↑ 1.0 1 5,177

Index Scan using idx_25486_primary on genes (cost=0.42..0.45 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=5,177)

  • Index Cond: (id = gene_values.gene_id)
53. 10.354 10.354 ↓ 0.0 0 5,177

Index Scan using idx_25464_index_formatting_tags_gene_values_on_gene_value_id on formatting_tags_gene_values ftgv (cost=0.29..0.31 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=5,177)

  • Index Cond: (gene_value_id = mgv.gene_value_id)
54. 0.011 0.026 ↑ 1.0 72 1

Hash (cost=2.72..2.72 rows=72 width=14) (actual time=0.026..0.026 rows=72 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
55. 0.015 0.015 ↑ 1.0 72 1

Seq Scan on formatting_tags ft (cost=0.00..2.72 rows=72 width=14) (actual time=0.004..0.015 rows=72 loops=1)

56.          

SubPlan (for Hash Left Join)

57. 0.324 6.192 ↓ 0.0 0 258

Nested Loop (cost=1.29..22.36 rows=1 width=0) (actual time=0.024..0.024 rows=0 loops=258)

58. 0.968 4.128 ↓ 2.0 2 258

Nested Loop (cost=1.14..22.17 rows=1 width=8) (actual time=0.011..0.016 rows=2 loops=258)

59. 0.710 2.580 ↓ 2.0 2 258

Nested Loop (cost=0.72..19.52 rows=1 width=16) (actual time=0.008..0.010 rows=2 loops=258)

60. 1.290 1.290 ↑ 2.5 2 258

Index Scan using idx_25507_index_gene_values_on_gene_id on gene_values gv2 (cost=0.42..6.92 rows=5 width=16) (actual time=0.004..0.005 rows=2 loops=258)

  • Index Cond: (gene_id = genes.id)
  • Filter: (id <> gene_values.id)
  • Rows Removed by Filter: 1
61. 0.580 0.580 ↑ 1.0 1 580

Index Scan using idx_25464_index_formatting_tags_gene_values_on_gene_value_id on formatting_tags_gene_values ftgv2 (cost=0.29..2.51 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=580)

  • Index Cond: (gene_value_id = gv2.id)
62. 0.580 0.580 ↑ 1.0 1 580

Index Only Scan using idx_25486_primary on genes g2 (cost=0.42..2.64 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=580)

  • Index Cond: (id = genes.id)
  • Heap Fetches: 580
63. 1.740 1.740 ↓ 0.0 0 580

Index Scan using idx_25452_primary on formatting_tags ft2 (cost=0.14..0.18 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=580)

  • Index Cond: (id = ftgv2.formatting_tag_id)
  • Filter: ((id <> ALL ('{130,578}'::bigint[])) AND (118 <> ALL ((concat('{', ancestor_ids, '}'))::integer[])))
  • Rows Removed by Filter: 1
64. 0.516 29.154 ↓ 0.0 0 258

Nested Loop (cost=1,917.40..1,926.15 rows=2 width=0) (actual time=0.113..0.113 rows=0 loops=258)

65. 0.258 0.258 ↑ 1.0 1 258

Index Only Scan using idx_25486_primary on genes g3 (cost=0.42..2.64 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=258)

  • Index Cond: (id = genes.id)
  • Heap Fetches: 258
66. 19.407 28.380 ↓ 0.0 0 258

Index Scan using idx_25507_index_gene_values_on_gene_id on gene_values gv3 (cost=1,916.97..1,923.48 rows=2 width=8) (actual time=0.110..0.110 rows=0 loops=258)

  • Index Cond: (gene_id = genes.id)
  • Filter: ((id <> gene_values.id) AND (NOT (hashed SubPlan 2)))
  • Rows Removed by Filter: 3
67.          

SubPlan (for Index Scan)

68. 8.973 8.973 ↑ 1.0 101,564 1

Seq Scan on formatting_tags_gene_values (cost=0.00..1,662.64 rows=101,564 width=8) (actual time=0.011..8.973 rows=101,564 loops=1)

69. 24,484.332 24,505.470 ↓ 10.9 36,359 4,995

HashAggregate (cost=2,728.77..2,762.27 rows=3,350 width=16) (actual time=0.008..4.906 rows=36,359 loops=4,995)

  • Group Key: gra_1.grammar_id
70. 6.723 21.138 ↓ 20.5 68,602 1

Nested Loop (cost=0.70..2,712.02 rows=3,350 width=16) (actual time=0.020..21.138 rows=68,602 loops=1)

71. 0.017 0.017 ↑ 1.0 23 1

Index Scan using idx_25820_index_response_actions_on_kind on response_actions ra_1 (cost=0.28..15.55 rows=23 width=8) (actual time=0.007..0.017 rows=23 loops=1)

  • Index Cond: (kind = 'instant'::text)
72. 14.398 14.398 ↓ 2.6 2,983 23

Index Scan using index_grammar_response_actions_on_response_action_id on grammar_response_actions gra_1 (cost=0.42..105.78 rows=1,146 width=16) (actual time=0.003..0.626 rows=2,983 loops=23)

  • Index Cond: (response_action_id = ra_1.id)
73. 18.124 18.124 ↑ 2.0 1 4,531

Index Only Scan using idx_25143_index_campaign_tags_campaigns_on_campaign_id_and_camp on campaign_tags_campaigns (cost=0.28..0.84 rows=2 width=8) (actual time=0.003..0.004 rows=1 loops=4,531)

  • Index Cond: (campaign_id = cmp.id)
  • Heap Fetches: 2,507
74. 13.593 13.593 ↓ 0.0 0 4,531

Index Scan using idx_25323_index_emotional_tags_gene_values_on_gene_value_id on emotional_tags_gene_values etgv (cost=0.42..0.44 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=4,531)

  • Index Cond: (gene_value_id = mgv.gene_value_id)
75. 4.531 4.531 ↓ 0.0 0 4,531

Index Scan using idx_25311_primary on emotional_tags et (cost=0.14..0.16 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=4,531)

  • Index Cond: (id = etgv.emotional_tag_id)
  • Filter: (NOT use_in_apollo_only)
76. 13.593 13.593 ↑ 1.0 1 4,531

Index Scan using index_descriptive_insight_reports_on_analysis_id on descriptive_insight_reports dir (cost=0.29..0.32 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=4,531)

  • Index Cond: (analysis_id = an.id)
  • Filter: approved_for_reporting
  • Rows Removed by Filter: 0
77. 9.062 9.062 ↓ 0.0 0 4,531

Index Scan using idx_25481_index_functional_tags_gene_values_on_gene_value_id on functional_tags_gene_values (cost=0.29..0.31 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=4,531)

  • Index Cond: (gene_value_id = mgv.gene_value_id)
78. 9.062 9.062 ↓ 0.0 0 4,531

Index Scan using index_functional_insight_reports_on_analysis_id on functional_insight_reports fir (cost=0.28..0.30 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=4,531)

  • Index Cond: (analysis_id = an.id)
  • Filter: approved_for_reporting
  • Rows Removed by Filter: 0
79. 113.275 113.275 ↑ 2.3 57 4,531

Index Scan using index_functional_insight_metrics_on_taggable on functional_insight_metrics fim (cost=0.29..3.29 rows=130 width=12) (actual time=0.001..0.025 rows=57 loops=4,531)

  • Index Cond: ((taggable_id = functional_tags_gene_values.functional_tag_id) AND ((taggable_type)::text = 'FunctionalTag'::text))
  • Filter: (include_in_report AND (NOT add_on))
  • Rows Removed by Filter: 3
80. 18.460 18.460 ↑ 1.5 4 4,615

Index Scan using idx_descr_insight_metrics_report_id on descriptive_insight_metrics dim (cost=0.42..1.30 rows=6 width=26) (actual time=0.002..0.004 rows=4 loops=4,615)

  • Index Cond: (descriptive_insight_report_id = dir.id)
  • Filter: include_in_report
  • Rows Removed by Filter: 1
Planning time : 36.473 ms
Execution time : 35,349.113 ms