explain.depesz.com

PostgreSQL's explain analyze made readable

Result: p50j

Settings
# exclusive inclusive rows x rows loops node
1. 51.652 525.618 ↓ 170.2 851 1

GroupAggregate (cost=30,230.08..30,230.41 rows=5 width=401) (actual time=469.619..525.618 rows=851 loops=1)

  • Group Key: messages.id, clients.id, pq.start_date, wk.id, cmp.id, message_metrics.id
2. 22.892 473.966 ↓ 4,319.0 21,595 1

Sort (cost=30,230.08..30,230.09 rows=5 width=275) (actual time=469.483..473.966 rows=21,595 loops=1)

  • Sort Key: messages.id, pq.start_date, wk.id, cmp.id, message_metrics.id
  • Sort Method: quicksort Memory: 14,957kB
3. 61.817 451.074 ↓ 4,319.0 21,595 1

Hash Right Join (cost=7,111.82..30,230.02 rows=5 width=275) (actual time=312.502..451.074 rows=21,595 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. 31.615 83.539 ↑ 1.0 465,859 1

Append (cost=0.00..13,186.22 rows=467,387 width=48) (actual time=0.010..83.539 rows=465,859 loops=1)

5. 51.615 51.615 ↑ 1.0 464,717 1

Seq Scan on descriptive_tags_gene_values dtgv (cost=0.00..8,485.92 rows=466,292 width=48) (actual time=0.009..51.615 rows=464,717 loops=1)

6. 0.161 0.309 ↓ 1.0 1,142 1

Subquery Scan on *SELECT* 2 (cost=0.00..37.38 rows=1,095 width=48) (actual time=0.018..0.309 rows=1,142 loops=1)

7. 0.148 0.148 ↓ 1.0 1,142 1

Seq Scan on custom_tags_gene_values ctgv (cost=0.00..20.95 rows=1,095 width=40) (actual time=0.009..0.148 rows=1,142 loops=1)

8. 13.791 305.718 ↓ 4,319.0 21,595 1

Hash (cost=7,111.73..7,111.73 rows=5 width=261) (actual time=305.718..305.718 rows=21,595 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 10,804kB
9. 2.348 291.927 ↓ 4,319.0 21,595 1

Nested Loop Left Join (cost=3,069.61..7,111.73 rows=5 width=261) (actual time=69.228..291.927 rows=21,595 loops=1)

10. 16.235 275.734 ↓ 4,615.0 4,615 1

Nested Loop Left Join (cost=3,069.19..7,110.15 rows=1 width=243) (actual time=69.220..275.734 rows=4,615 loops=1)

  • Join Filter: (fim.functional_insight_report_id = fir.id)
  • Rows Removed by Join Filter: 251,126
11. 0.934 182.472 ↓ 4,531.0 4,531 1

Nested Loop Left Join (cost=3,068.91..7,105.43 rows=1 width=247) (actual time=69.218..182.472 rows=4,531 loops=1)

12. 0.273 177.007 ↓ 4,531.0 4,531 1

Nested Loop Left Join (cost=3,068.63..7,105.12 rows=1 width=251) (actual time=69.213..177.007 rows=4,531 loops=1)

13. 2.472 172.203 ↓ 4,531.0 4,531 1

Nested Loop Left Join (cost=3,068.34..7,104.80 rows=1 width=243) (actual time=69.207..172.203 rows=4,531 loops=1)

14. 3.159 165.200 ↓ 4,531.0 4,531 1

Nested Loop Left Join (cost=3,068.05..7,104.48 rows=1 width=239) (actual time=69.198..165.200 rows=4,531 loops=1)

  • 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: 162
15. 0.413 120.245 ↓ 4,693.0 4,693 1

Nested Loop Left Join (cost=3,067.91..4,056.47 rows=1 width=255) (actual time=69.196..120.245 rows=4,693 loops=1)

16. 0.000 115.139 ↓ 4,693.0 4,693 1

Nested Loop Left Join (cost=3,067.62..4,056.15 rows=1 width=247) (actual time=69.190..115.139 rows=4,693 loops=1)

17. 2.307 111.441 ↓ 4,693.0 4,693 1

Nested Loop Left Join (cost=3,067.48..4,055.98 rows=1 width=247) (actual time=69.188..111.441 rows=4,693 loops=1)

18. 0.858 104.441 ↓ 4,693.0 4,693 1

Nested Loop Left Join (cost=3,067.05..4,055.53 rows=1 width=239) (actual time=69.179..104.441 rows=4,693 loops=1)

19. 3.280 98.890 ↓ 4,693.0 4,693 1

Nested Loop (cost=3,066.77..4,054.53 rows=1 width=239) (actual time=69.172..98.890 rows=4,693 loops=1)

20. 1.487 90.917 ↓ 4,693.0 4,693 1

Hash Join (cost=3,066.35..4,054.07 rows=1 width=231) (actual time=69.160..90.917 rows=4,693 loops=1)

  • Hash Cond: (an.grammar_id = gra_1.grammar_id)
  • Join Filter: (gra."primary" OR ((count(gra_1.response_action_id)) = 1))
  • Rows Removed by Join Filter: 484
21. 0.000 23.153 ↓ 1,294.2 5,177 1

Nested Loop (cost=5.22..991.23 rows=4 width=264) (actual time=2.853..23.153 rows=5,177 loops=1)

22. 0.747 13.210 ↓ 1,294.2 5,177 1

Nested Loop (cost=4.79..989.04 rows=4 width=218) (actual time=2.846..13.210 rows=5,177 loops=1)

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

Nested Loop (cost=4.36..988.41 rows=1 width=218) (actual time=2.835..9.679 rows=928 loops=1)

  • Join Filter: ((an.grammar_id = gra.grammar_id) AND (ra.id = gra.response_action_id))
  • Rows Removed by Join Filter: 1,980
24. 0.540 7.143 ↓ 928.0 928 1

Nested Loop (cost=3.94..987.85 rows=1 width=225) (actual time=2.826..7.143 rows=928 loops=1)

  • Join Filter: (an.grammar_id = messages.grammar_id)
25. 0.240 4.609 ↓ 110.8 997 1

Nested Loop (cost=3.51..983.15 rows=9 width=107) (actual time=2.817..4.609 rows=997 loops=1)

26. 0.026 3.955 ↓ 69.0 69 1

Nested Loop (cost=3.09..981.57 rows=1 width=80) (actual time=2.811..3.955 rows=69 loops=1)

27. 0.102 3.614 ↓ 105.0 105 1

Nested Loop (cost=2.80..981.20 rows=1 width=80) (actual time=2.803..3.614 rows=105 loops=1)

28. 0.405 3.339 ↓ 10.2 173 1

Nested Loop (cost=2.52..975.96 rows=17 width=72) (actual time=2.781..3.339 rows=173 loops=1)

  • Join Filter: (wk.channel_id = ch.id)
  • Rows Removed by Join Filter: 4,498
29. 0.018 0.018 ↑ 1.0 27 1

Seq Scan on channels ch (cost=0.00..2.27 rows=27 width=8) (actual time=0.006..0.018 rows=27 loops=1)

30. 0.274 2.916 ↓ 10.2 173 27

Materialize (cost=2.52..966.84 rows=17 width=80) (actual time=0.003..0.108 rows=173 loops=27)

31. 0.000 2.642 ↓ 10.2 173 1

Nested Loop (cost=2.52..966.76 rows=17 width=80) (actual time=0.070..2.642 rows=173 loops=1)

32. 0.023 2.305 ↓ 10.2 173 1

Nested Loop (cost=2.23..960.77 rows=17 width=88) (actual time=0.062..2.305 rows=173 loops=1)

33. 0.009 0.009 ↑ 1.0 1 1

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

  • Index Cond: (id = 674)
34. 0.081 2.273 ↓ 10.2 173 1

Nested Loop (cost=1.95..958.11 rows=17 width=80) (actual time=0.054..2.273 rows=173 loops=1)

35. 0.000 1.862 ↑ 1.1 110 1

Nested Loop (cost=1.53..869.51 rows=117 width=48) (actual time=0.047..1.862 rows=110 loops=1)

36. 0.051 1.044 ↑ 1.8 164 1

Nested Loop (cost=1.11..632.47 rows=287 width=32) (actual time=0.037..1.044 rows=164 loops=1)

37. 0.097 0.417 ↑ 2.7 144 1

Nested Loop (cost=0.82..454.16 rows=388 width=24) (actual time=0.025..0.417 rows=144 loops=1)

38. 0.076 0.076 ↑ 1.0 122 1

Index Only Scan using campaigns_client_go_country_id on campaigns cmp (cost=0.41..15.68 rows=122 width=16) (actual time=0.014..0.076 rows=122 loops=1)

  • Index Cond: (client_id = 674)
  • Heap Fetches: 64
39. 0.244 0.244 ↑ 4.0 1 122

Index Only Scan using sensei_experiments_cmp_imp_type_id on sensei_experiments exp (cost=0.41..3.55 rows=4 width=16) (actual time=0.002..0.002 rows=1 loops=122)

  • Index Cond: (campaign_id = cmp.id)
  • Heap Fetches: 108
40. 0.576 0.576 ↑ 1.0 1 144

Index Scan using idx_26018_index_workspaces_on_experiment_id on workspaces wk (cost=0.29..0.45 rows=1 width=24) (actual time=0.003..0.004 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
41. 0.820 0.820 ↑ 1.0 1 164

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

  • Index Cond: (workspace_id = wk.id)
  • Filter: (state = 'analyzed'::text)
  • Rows Removed by Filter: 3
42. 0.330 0.330 ↓ 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.003..0.003 rows=2 loops=110)

  • Index Cond: ((grammar_id = gr.id) AND (approved = true))
  • Filter: (approved AND approved_for_reporting)
  • Rows Removed by Filter: 0
43. 0.346 0.346 ↑ 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.002..0.002 rows=1 loops=173)

  • Index Cond: (workspace_id = wk.id)
  • Heap Fetches: 173
44. 0.173 0.173 ↑ 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.001..0.001 rows=1 loops=173)

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

Index Scan using idx_25722_primary on performance_queries pq (cost=0.29..0.36 rows=1 width=16) (actual time=0.003..0.003 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
46. 0.414 0.414 ↑ 1.0 14 69

Index Scan using index_message_metrics_on_analysis_id on message_metrics (cost=0.43..1.44 rows=14 width=35) (actual time=0.004..0.006 rows=14 loops=69)

  • Index Cond: (analysis_id = an.id)
47. 1.994 1.994 ↑ 1.0 1 997

Index Scan using idx_25659_primary on messages (cost=0.42..0.51 rows=1 width=118) (actual time=0.002..0.002 rows=1 loops=997)

  • Index Cond: (id = message_metrics.message_id)
  • Filter: (message_grammar_type IS NULL)
  • Rows Removed by Filter: 0
48. 1.856 1.856 ↑ 1.3 3 928

Index Scan using idx_3241_grammar_id_response_action_id on grammar_response_actions gra (cost=0.42..0.50 rows=4 width=17) (actual time=0.002..0.002 rows=3 loops=928)

  • Index Cond: (grammar_id = messages.grammar_id)
49. 2.784 2.784 ↑ 1.0 6 928

Index Only Scan using index_message_gene_values_on_message_id_and_gene_value_id on message_gene_values mgv (cost=0.43..0.55 rows=6 width=16) (actual time=0.002..0.003 rows=6 loops=928)

  • Index Cond: (message_id = message_metrics.message_id)
  • Heap Fetches: 2,797
50. 10.354 10.354 ↑ 1.0 1 5,177

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

  • Index Cond: (id = mgv.gene_value_id)
51. 8.872 66.277 ↓ 19.5 65,287 1

Hash (cost=3,019.19..3,019.19 rows=3,355 width=16) (actual time=66.277..66.277 rows=65,287 loops=1)

  • Buckets: 65,536 (originally 4096) Batches: 1 (originally 1) Memory Usage: 3,573kB
52. 29.615 57.405 ↓ 19.5 65,287 1

HashAggregate (cost=2,952.09..2,985.64 rows=3,355 width=16) (actual time=46.587..57.405 rows=65,287 loops=1)

  • Group Key: gra_1.grammar_id
53. 7.900 27.790 ↓ 20.5 68,730 1

Nested Loop (cost=0.70..2,935.32 rows=3,355 width=16) (actual time=0.012..27.790 rows=68,730 loops=1)

54. 0.018 0.018 ↑ 1.0 23 1

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

  • Index Cond: (kind = 'instant'::text)
55. 19.872 19.872 ↓ 2.6 2,988 23

Index Scan using index_grammar_response_actions_on_response_action_id on grammar_response_actions gra_1 (cost=0.42..115.43 rows=1,152 width=16) (actual time=0.004..0.864 rows=2,988 loops=23)

  • Index Cond: (response_action_id = ra_1.id)
56. 4.693 4.693 ↑ 1.0 1 4,693

Index Scan using idx_25486_primary on genes (cost=0.42..0.45 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=4,693)

  • Index Cond: (id = gene_values.gene_id)
57. 4.693 4.693 ↑ 2.0 1 4,693

Index Only Scan using idx_25143_index_campaign_tags_campaigns_on_campaign_id_and_camp on campaign_tags_campaigns (cost=0.28..0.98 rows=2 width=8) (actual time=0.001..0.001 rows=1 loops=4,693)

  • Index Cond: (campaign_id = cmp.id)
  • Heap Fetches: 2,605
58. 4.693 4.693 ↓ 0.0 0 4,693

Index Only Scan using emotional_tags_gene_values_gene_value_id_idx on emotional_tags_gene_values etgv (cost=0.42..0.44 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=4,693)

  • Index Cond: (gene_value_id = mgv.gene_value_id)
  • Heap Fetches: 1,688
59. 4.693 4.693 ↓ 0.0 0 4,693

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

  • Index Cond: (id = etgv.emotional_tag_id)
  • Filter: (NOT use_in_apollo_only)
60. 4.693 4.693 ↓ 0.0 0 4,693

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.001..0.001 rows=0 loops=4,693)

  • Index Cond: (gene_value_id = mgv.gene_value_id)
61. 0.000 0.000 ↓ 0.0 0 4,693

Index Scan using idx_25452_primary on formatting_tags ft (cost=0.14..0.16 rows=1 width=14) (actual time=0.000..0.000 rows=0 loops=4,693)

  • Index Cond: (id = ftgv.formatting_tag_id)
62.          

SubPlan (for Nested Loop Left Join)

63. 0.000 3.612 ↓ 0.0 0 258

Nested Loop (cost=1.41..33.55 rows=1 width=0) (actual time=0.014..0.014 rows=0 loops=258)

64. 0.194 2.580 ↓ 2.0 2 258

Nested Loop (cost=1.27..33.36 rows=1 width=8) (actual time=0.005..0.010 rows=2 loops=258)

65. 0.452 1.806 ↓ 2.0 2 258

Nested Loop (cost=0.84..30.71 rows=1 width=16) (actual time=0.004..0.007 rows=2 loops=258)

66. 0.774 0.774 ↑ 4.5 2 258

Index Scan using idx_25507_index_gene_values_on_gene_id on gene_values gv2 (cost=0.42..11.30 rows=9 width=16) (actual time=0.002..0.003 rows=2 loops=258)

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

Index Only Scan using idx_1242_gv_id_formatting_tag_id on formatting_tags_gene_values ftgv2 (cost=0.42..2.15 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=580)

  • Index Cond: (gene_value_id = gv2.id)
  • Heap Fetches: 452
68. 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: 214
69. 1.160 1.160 ↓ 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.002..0.002 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
70. 0.000 38.184 ↓ 0.0 0 258

Nested Loop (cost=3,010.97..3,024.13 rows=4 width=0) (actual time=0.148..0.148 rows=0 loops=258)

71. 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: 85
72. 20.859 37.926 ↓ 0.0 0 258

Index Scan using idx_25507_index_gene_values_on_gene_id on gene_values gv3 (cost=3,010.55..3,021.45 rows=4 width=8) (actual time=0.147..0.147 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
73.          

SubPlan (for Index Scan)

74. 17.067 17.067 ↓ 1.0 101,739 1

Index Only Scan using idx_25464_index_formatting_tags_gene_values_on_gene_value_id on formatting_tags_gene_values (cost=0.29..2,756.92 rows=101,280 width=8) (actual time=0.009..17.067 rows=101,739 loops=1)

  • Heap Fetches: 47,883
75. 4.531 4.531 ↑ 1.0 1 4,531

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

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

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

  • Index Cond: (gene_value_id = mgv.gene_value_id)
  • Heap Fetches: 241
77. 4.531 4.531 ↓ 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.001..0.001 rows=0 loops=4,531)

  • Index Cond: (analysis_id = an.id)
  • Filter: approved_for_reporting
  • Rows Removed by Filter: 0
78. 77.027 77.027 ↑ 2.3 55 4,531

Index Scan using index_functional_insight_metrics_on_taggable on functional_insight_metrics fim (cost=0.28..3.17 rows=124 width=12) (actual time=0.000..0.017 rows=55 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
79. 13.845 13.845 ↑ 1.5 4 4,615

Index Scan using idx_descr_insight_metrics_report_id on descriptive_insight_metrics dim (cost=0.42..1.52 rows=6 width=26) (actual time=0.001..0.003 rows=4 loops=4,615)

  • Index Cond: (descriptive_insight_report_id = dir.id)
  • Filter: include_in_report
  • Rows Removed by Filter: 1
Planning time : 31.909 ms
Execution time : 526.505 ms