explain.depesz.com

PostgreSQL's explain analyze made readable

Result: S6Dc

Settings
# exclusive inclusive rows x rows loops node
1. 119.516 81,356.972 ↓ 592.0 2,960 1

GroupAggregate (cost=30,388.29..30,388.62 rows=5 width=399) (actual time=81,228.645..81,356.972 rows=2,960 loops=1)

  • Group Key: messages.id, clients.id, pq.start_date, wk.id, cmp.id, message_metrics.id
2. 59.836 81,237.456 ↓ 16,158.4 80,792 1

Sort (cost=30,388.29..30,388.30 rows=5 width=272) (actual time=81,228.571..81,237.456 rows=80,792 loops=1)

  • Sort Key: messages.id, pq.start_date, wk.id, cmp.id, message_metrics.id
  • Sort Method: quicksort Memory: 24,209kB
3. 105.631 81,177.620 ↓ 16,158.4 80,792 1

Hash Right Join (cost=4,582.58..30,388.23 rows=5 width=272) (actual time=81,007.111..81,177.620 rows=80,792 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.703 83.874 ↑ 1.0 469,327 1

Append (cost=0.00..15,775.21 rows=472,020 width=48) (actual time=0.013..83.874 rows=469,327 loops=1)

5. 51.924 51.924 ↑ 1.0 468,175 1

Seq Scan on descriptive_tags_gene_values dtgv (cost=0.00..8,667.78 rows=470,878 width=48) (actual time=0.012..51.924 rows=468,175 loops=1)

6. 0.111 0.247 ↓ 1.0 1,152 1

Subquery Scan on *SELECT* 2 (cost=0.00..38.55 rows=1,142 width=48) (actual time=0.013..0.247 rows=1,152 loops=1)

7. 0.136 0.136 ↓ 1.0 1,152 1

Seq Scan on custom_tags_gene_values ctgv (cost=0.00..21.42 rows=1,142 width=40) (actual time=0.012..0.136 rows=1,152 loops=1)

8. 68.212 80,988.115 ↓ 16,158.4 80,792 1

Hash (cost=4,582.50..4,582.50 rows=5 width=258) (actual time=80,988.115..80,988.115 rows=80,792 loops=1)

  • Buckets: 131,072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 17,935kB
9. 20.744 80,919.903 ↓ 16,158.4 80,792 1

Nested Loop Left Join (cost=2,964.19..4,582.50 rows=5 width=258) (actual time=51.595..80,919.903 rows=80,792 loops=1)

10. 9.834 80,818.039 ↓ 20,280.0 20,280 1

Nested Loop Left Join (cost=2,963.77..4,580.92 rows=1 width=240) (actual time=51.590..80,818.039 rows=20,280 loops=1)

  • Join Filter: (fim.functional_insight_report_id = fir.id)
  • Rows Removed by Join Filter: 70,368
11. 20.325 80,767.645 ↓ 20,280.0 20,280 1

Nested Loop Left Join (cost=2,963.49..4,576.12 rows=1 width=244) (actual time=51.583..80,767.645 rows=20,280 loops=1)

12. 21.802 80,727.040 ↓ 20,280.0 20,280 1

Nested Loop Left Join (cost=2,963.21..4,575.82 rows=1 width=248) (actual time=51.576..80,727.040 rows=20,280 loops=1)

13. 8.172 80,684.958 ↓ 20,280.0 20,280 1

Nested Loop Left Join (cost=2,962.92..4,575.50 rows=1 width=240) (actual time=51.570..80,684.958 rows=20,280 loops=1)

14. 13.390 80,615.946 ↓ 20,280.0 20,280 1

Nested Loop Left Join (cost=2,962.63..4,575.17 rows=1 width=236) (actual time=51.558..80,615.946 rows=20,280 loops=1)

15. 20.916 80,582.276 ↓ 20,280.0 20,280 1

Nested Loop Left Join (cost=2,962.49..4,575.01 rows=1 width=236) (actual time=51.554..80,582.276 rows=20,280 loops=1)

16. 23.602 80,520.800 ↓ 20,280.0 20,280 1

Nested Loop Left Join (cost=2,962.07..4,574.55 rows=1 width=228) (actual time=51.544..80,520.800 rows=20,280 loops=1)

17. 21,246.930 80,436.454 ↓ 10,124.0 10,124 1

Nested Loop (cost=2,961.78..4,573.86 rows=1 width=228) (actual time=51.529..80,436.454 rows=10,124 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: 378,440,796
18. 16.339 344.224 ↓ 10,748.0 10,748 1

Hash Left Join (cost=10.55..1,505.04 rows=1 width=261) (actual time=2.728..344.224 rows=10,748 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: 356
19. 14.956 278.893 ↓ 2,776.0 11,104 1

Nested Loop Left Join (cost=5.93..1,500.40 rows=4 width=277) (actual time=2.689..278.893 rows=11,104 loops=1)

20. 19.475 241.729 ↓ 2,776.0 11,104 1

Nested Loop (cost=5.64..1,499.12 rows=4 width=269) (actual time=2.683..241.729 rows=11,104 loops=1)

21. 18.010 188.942 ↓ 2,776.0 11,104 1

Nested Loop (cost=5.22..1,497.30 rows=4 width=262) (actual time=2.674..188.942 rows=11,104 loops=1)

22. 10.394 126.516 ↓ 2,776.0 11,104 1

Nested Loop (cost=4.79..1,495.15 rows=4 width=216) (actual time=2.665..126.516 rows=11,104 loops=1)

  • Join Filter: (messages.id = mgv.message_id)
23. 6.177 94.450 ↓ 3,096.0 3,096 1

Nested Loop (cost=4.36..1,494.52 rows=1 width=216) (actual time=2.656..94.450 rows=3,096 loops=1)

  • Join Filter: ((an.grammar_id = gra.grammar_id) AND (ra.id = gra.response_action_id))
  • Rows Removed by Join Filter: 2,208
24. 6.062 69.697 ↓ 3,096.0 3,096 1

Nested Loop (cost=3.94..1,493.96 rows=1 width=223) (actual time=2.646..69.697 rows=3,096 loops=1)

  • Join Filter: (an.grammar_id = messages.grammar_id)
25. 3.146 43.415 ↓ 374.4 3,370 1

Nested Loop (cost=3.51..1,489.09 rows=9 width=107) (actual time=2.637..43.415 rows=3,370 loops=1)

26. 0.779 35.901 ↓ 273.0 273 1

Nested Loop (cost=3.09..1,487.50 rows=1 width=80) (actual time=2.629..35.901 rows=273 loops=1)

27. 0.368 32.569 ↓ 851.0 851 1

Nested Loop (cost=2.80..1,487.14 rows=1 width=80) (actual time=0.166..32.569 rows=851 loops=1)

28. 0.789 28.909 ↓ 53.1 1,646 1

Nested Loop (cost=2.52..1,477.88 rows=31 width=72) (actual time=0.160..28.909 rows=1,646 loops=1)

  • Join Filter: (wk.channel_id = ch.id)
  • Rows Removed by Join Filter: 11,548
29. 0.734 26.474 ↓ 53.1 1,646 1

Nested Loop (cost=2.52..1,463.36 rows=31 width=80) (actual time=0.148..26.474 rows=1,646 loops=1)

30. 0.399 22.448 ↓ 53.1 1,646 1

Nested Loop (cost=2.23..1,453.41 rows=31 width=88) (actual time=0.140..22.448 rows=1,646 loops=1)

31. 0.010 0.010 ↑ 1.0 1 1

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

  • Index Cond: (id = 177)
32. 0.811 22.039 ↓ 53.1 1,646 1

Nested Loop (cost=1.95..1,450.61 rows=31 width=80) (actual time=0.129..22.039 rows=1,646 loops=1)

33. 0.717 16.743 ↓ 4.2 897 1

Nested Loop (cost=1.53..1,285.75 rows=216 width=48) (actual time=0.121..16.743 rows=897 loops=1)

34. 0.715 9.858 ↓ 1.9 1,028 1

Nested Loop (cost=1.11..843.76 rows=535 width=32) (actual time=0.108..9.858 rows=1,028 loops=1)

35. 0.413 1.673 ↓ 3.4 2,490 1

Nested Loop (cost=0.82..509.96 rows=727 width=24) (actual time=0.035..1.673 rows=2,490 loops=1)

36. 0.120 0.120 ↑ 1.0 228 1

Index Only Scan using campaigns_client_go_country_id on campaigns cmp (cost=0.41..32.85 rows=228 width=16) (actual time=0.010..0.120 rows=228 loops=1)

  • Index Cond: (client_id = 177)
  • Heap Fetches: 145
37. 1.140 1.140 ↓ 2.8 11 228

Index Only Scan using sensei_experiments_cmp_imp_type_id on sensei_experiments exp (cost=0.41..2.05 rows=4 width=16) (actual time=0.002..0.005 rows=11 loops=228)

  • Index Cond: (campaign_id = cmp.id)
  • Heap Fetches: 241
38. 7.470 7.470 ↓ 0.0 0 2,490

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.003 rows=0 loops=2,490)

  • 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
39. 6.168 6.168 ↑ 1.0 1 1,028

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.004..0.006 rows=1 loops=1,028)

  • Index Cond: (workspace_id = wk.id)
  • Filter: (state = 'analyzed'::text)
  • Rows Removed by Filter: 3
40. 4.485 4.485 ↓ 2.0 2 897

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

  • Index Cond: ((grammar_id = gr.id) AND (approved = true))
  • Filter: (approved AND approved_for_reporting)
  • Rows Removed by Filter: 0
41. 3.292 3.292 ↑ 1.0 1 1,646

Index Only Scan using idx_25874_index_segment_groups_on_workspace_id on segment_groups sg (cost=0.29..0.32 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1,646)

  • Index Cond: (workspace_id = wk.id)
  • Heap Fetches: 705
42. 1.635 1.646 ↑ 3.4 8 1,646

Materialize (cost=0.00..2.41 rows=27 width=8) (actual time=0.000..0.001 rows=8 loops=1,646)

43. 0.011 0.011 ↑ 1.2 23 1

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

44. 3.292 3.292 ↑ 1.0 1 1,646

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

  • Index Cond: (id = an.response_action_id)
  • Filter: (kind = 'instant'::text)
  • Rows Removed by Filter: 0
45. 2.553 2.553 ↓ 0.0 0 851

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=0 loops=851)

  • Index Cond: (id = an.performance_query_id)
  • Filter: ((start_date >= '2019-02-03 00:00:00'::timestamp without time zone) AND (start_date <= '2020-07-01 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 1
46. 4.368 4.368 ↑ 1.2 12 273

Index Scan using index_message_metrics_on_analysis_id on message_metrics (cost=0.43..1.45 rows=14 width=35) (actual time=0.006..0.016 rows=12 loops=273)

  • Index Cond: (analysis_id = an.id)
47. 20.220 20.220 ↑ 1.0 1 3,370

Index Scan using idx_25659_primary on messages (cost=0.42..0.53 rows=1 width=116) (actual time=0.006..0.006 rows=1 loops=3,370)

  • Index Cond: (id = message_metrics.message_id)
  • Filter: (message_grammar_type IS NULL)
  • Rows Removed by Filter: 0
48. 18.576 18.576 ↑ 2.0 2 3,096

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.005..0.006 rows=2 loops=3,096)

  • Index Cond: (grammar_id = messages.grammar_id)
49. 21.672 21.672 ↑ 1.5 4 3,096

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.005..0.007 rows=4 loops=3,096)

  • Index Cond: (message_id = message_metrics.message_id)
  • Heap Fetches: 5,431
50. 44.416 44.416 ↑ 1.0 1 11,104

Index Scan using idx_25507_primary on gene_values (cost=0.42..0.54 rows=1 width=46) (actual time=0.004..0.004 rows=1 loops=11,104)

  • Index Cond: (id = mgv.gene_value_id)
51. 33.312 33.312 ↑ 1.0 1 11,104

Index Scan using idx_25486_primary on genes (cost=0.42..0.45 rows=1 width=15) (actual time=0.003..0.003 rows=1 loops=11,104)

  • Index Cond: (id = gene_values.gene_id)
52. 22.208 22.208 ↓ 0.0 0 11,104

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=11,104)

  • Index Cond: (gene_value_id = mgv.gene_value_id)
53. 0.011 0.032 ↑ 1.0 72 1

Hash (cost=3.72..3.72 rows=72 width=14) (actual time=0.032..0.032 rows=72 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
54. 0.021 0.021 ↑ 1.0 72 1

Seq Scan on formatting_tags ft (cost=0.00..3.72 rows=72 width=14) (actual time=0.005..0.021 rows=72 loops=1)

55.          

SubPlan (for Hash Left Join)

56. 0.352 6.400 ↓ 0.0 0 256

Nested Loop (cost=1.41..33.54 rows=1 width=0) (actual time=0.025..0.025 rows=0 loops=256)

57. 0.448 4.608 ↑ 1.0 1 256

Nested Loop (cost=1.27..33.36 rows=1 width=8) (actual time=0.017..0.018 rows=1 loops=256)

58. 0.896 3.584 ↑ 1.0 1 256

Nested Loop (cost=0.84..30.70 rows=1 width=16) (actual time=0.013..0.014 rows=1 loops=256)

59. 1.536 1.536 ↑ 9.0 1 256

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.006..0.006 rows=1 loops=256)

  • Index Cond: (gene_id = genes.id)
  • Filter: (id <> gene_values.id)
  • Rows Removed by Filter: 1
60. 1.152 1.152 ↑ 1.0 1 288

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.004..0.004 rows=1 loops=288)

  • Index Cond: (gene_value_id = gv2.id)
  • Heap Fetches: 260
61. 0.576 0.576 ↑ 1.0 1 288

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

  • Index Cond: (id = genes.id)
  • Heap Fetches: 128
62. 1.440 1.440 ↓ 0.0 0 288

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

  • 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
63. 0.224 42.560 ↓ 0.0 0 224

Nested Loop (cost=3,047.51..3,060.67 rows=5 width=0) (actual time=0.190..0.190 rows=0 loops=224)

64. 0.448 0.448 ↑ 1.0 1 224

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

  • Index Cond: (id = genes.id)
  • Heap Fetches: 128
65. 25.511 41.888 ↓ 0.0 0 224

Index Scan using idx_25507_index_gene_values_on_gene_id on gene_values gv3 (cost=3,047.08..3,057.98 rows=5 width=8) (actual time=0.187..0.187 rows=0 loops=224)

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

SubPlan (for Index Scan)

67. 16.377 16.377 ↓ 1.0 102,049 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,792.27 rows=101,754 width=8) (actual time=0.012..16.377 rows=102,049 loops=1)

  • Heap Fetches: 48,315
68. 58,818.134 58,845.300 ↓ 10.5 35,211 10,748

HashAggregate (cost=2,951.23..2,984.83 rows=3,360 width=16) (actual time=0.004..5.475 rows=35,211 loops=10,748)

  • Group Key: gra_1.grammar_id
69. 7.665 27.166 ↓ 20.5 69,021 1

Nested Loop (cost=0.70..2,934.43 rows=3,360 width=16) (actual time=0.016..27.166 rows=69,021 loops=1)

70. 0.020 0.020 ↑ 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.008..0.020 rows=23 loops=1)

  • Index Cond: (kind = 'instant'::text)
71. 19.481 19.481 ↓ 2.6 3,001 23

Index Scan using index_grammar_response_actions_on_response_action_id on grammar_response_actions gra_1 (cost=0.42..115.47 rows=1,144 width=16) (actual time=0.004..0.847 rows=3,001 loops=23)

  • Index Cond: (response_action_id = ra_1.id)
72. 60.744 60.744 ↑ 1.0 2 10,124

Index Only Scan using idx_25143_index_campaign_tags_campaigns_on_campaign_id_and_camp on campaign_tags_campaigns (cost=0.28..0.67 rows=2 width=8) (actual time=0.005..0.006 rows=2 loops=10,124)

  • Index Cond: (campaign_id = cmp.id)
  • Heap Fetches: 20,280
73. 40.560 40.560 ↓ 0.0 0 20,280

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.002..0.002 rows=0 loops=20,280)

  • Index Cond: (gene_value_id = mgv.gene_value_id)
  • Heap Fetches: 6,738
74. 20.280 20.280 ↓ 0.0 0 20,280

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=20,280)

  • Index Cond: (id = etgv.emotional_tag_id)
  • Filter: (NOT use_in_apollo_only)
75. 60.840 60.840 ↑ 1.0 1 20,280

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=20,280)

  • Index Cond: (analysis_id = an.id)
  • Filter: approved_for_reporting
  • Rows Removed by Filter: 0
76. 20.280 20.280 ↓ 0.0 0 20,280

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=20,280)

  • Index Cond: (gene_value_id = mgv.gene_value_id)
  • Heap Fetches: 64
77. 20.280 20.280 ↓ 0.0 0 20,280

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=20,280)

  • Index Cond: (analysis_id = an.id)
  • Filter: approved_for_reporting
  • Rows Removed by Filter: 0
78. 40.560 40.560 ↑ 42.3 3 20,280

Index Scan using index_functional_insight_metrics_on_taggable on functional_insight_metrics fim (cost=0.28..3.21 rows=127 width=12) (actual time=0.000..0.002 rows=3 loops=20,280)

  • 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: 0
79. 81.120 81.120 ↑ 1.5 4 20,280

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

  • Index Cond: (descriptive_insight_report_id = dir.id)
  • Filter: include_in_report
  • Rows Removed by Filter: 0
Planning time : 36.129 ms
Execution time : 81,359.315 ms