explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hq0

Settings
# exclusive inclusive rows x rows loops node
1. 7.861 13,203.252 ↑ 11,660.4 867 1

GroupAggregate (cost=2,655,692.29..3,262,265.71 rows=10,109,557 width=299) (actual time=13,195.166..13,203.252 rows=867 loops=1)

  • Group Key: messages.id, clients.id, pq.start_date, wk.id, cmp.id, message_metrics.id
2. 2.719 13,195.391 ↑ 2,168.0 4,663 1

Sort (cost=2,655,692.29..2,680,966.19 rows=10,109,557 width=187) (actual time=13,195.112..13,195.391 rows=4,663 loops=1)

  • Sort Key: messages.id, pq.start_date, wk.id, cmp.id, message_metrics.id
  • Sort Method: quicksort Memory: 2,505kB
3. 0.725 13,192.672 ↑ 2,168.0 4,663 1

Hash Left Join (cost=577,846.90..933,034.89 rows=10,109,557 width=187) (actual time=13,157.266..13,192.672 rows=4,663 loops=1)

  • Hash Cond: ((fir.id = fim.functional_insight_report_id) AND (functional_tags_gene_values.functional_tag_id = fim.taggable_id))
4. 36.821 13,188.302 ↑ 2,207.8 4,579 1

Hash Right Join (cost=577,680.39..857,045.07 rows=10,109,557 width=191) (actual time=13,153.607..13,188.302 rows=4,579 loops=1)

  • Hash Cond: (fir.analysis_id = an.id)
5. 0.258 0.258 ↑ 2.7 621 1

Seq Scan on functional_insight_reports fir (cost=0.00..55.88 rows=1,694 width=8) (actual time=0.010..0.258 rows=621 loops=1)

  • Filter: approved_for_reporting
  • Rows Removed by Filter: 1,884
6. 3.358 13,151.223 ↑ 2,207.8 4,579 1

Hash (cost=174,876.92..174,876.92 rows=10,109,557 width=195) (actual time=13,151.223..13,151.223 rows=4,579 loops=1)

  • Buckets: 262,144 Batches: 128 Memory Usage: 2,048kB
7. 3.436 13,147.865 ↑ 2,207.8 4,579 1

Merge Right Join (cost=21,368.79..174,876.92 rows=10,109,557 width=195) (actual time=13,140.994..13,147.865 rows=4,579 loops=1)

  • Merge Cond: (functional_tags_gene_values.gene_value_id = mgv.gene_value_id)
8. 6.653 6.653 ↑ 2.2 33,628 1

Index Scan using idx_25481_index_functional_tags_gene_values_on_gene_value_id on functional_tags_gene_values (cost=0.29..1,682.74 rows=72,930 width=16) (actual time=0.013..6.653 rows=33,628 loops=1)

9. 3.311 13,137.776 ↑ 6.1 4,579 1

Sort (cost=21,368.50..21,437.81 rows=27,724 width=195) (actual time=13,137.295..13,137.776 rows=4,579 loops=1)

  • Sort Key: mgv.gene_value_id
  • Sort Method: quicksort Memory: 2,535kB
10. 0.073 13,134.465 ↑ 6.1 4,579 1

Nested Loop Left Join (cost=18,181.23..19,322.63 rows=27,724 width=195) (actual time=218.945..13,134.465 rows=4,579 loops=1)

11. 42.740 13,125.234 ↓ 572.4 4,579 1

Hash Join (cost=18,165.77..18,471.09 rows=8 width=187) (actual time=218.926..13,125.234 rows=4,579 loops=1)

  • Hash Cond: (gene_values.gene_id = genes.id)
  • Join 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 Join Filter: 162
12. 1.351 156.658 ↓ 3.0 4,741 1

Hash Left Join (cost=32.17..333.39 rows=1,560 width=203) (actual time=5.869..156.658 rows=4,741 loops=1)

  • Hash Cond: (ftgv.formatting_tag_id = ft.id)
13. 0.805 155.275 ↓ 3.0 4,741 1

Nested Loop Left Join (cost=14.07..311.15 rows=1,560 width=171) (actual time=5.832..155.275 rows=4,741 loops=1)

14. 1.648 149.729 ↓ 4,741.0 4,741 1

Nested Loop Left Join (cost=13.77..267.95 rows=1 width=163) (actual time=5.825..149.729 rows=4,741 loops=1)

15. 0.000 143.340 ↓ 4,741.0 4,741 1

Nested Loop (cost=13.49..266.84 rows=1 width=163) (actual time=5.817..143.340 rows=4,741 loops=1)

16. 1.207 134.049 ↓ 4,741.0 4,741 1

Nested Loop (cost=13.06..266.19 rows=1 width=115) (actual time=5.808..134.049 rows=4,741 loops=1)

17. 1.327 130.241 ↓ 867.0 867 1

Nested Loop (cost=12.63..64.39 rows=1 width=115) (actual time=5.799..130.241 rows=867 loops=1)

18. 1.351 127.495 ↓ 1,419.0 1,419 1

Nested Loop (cost=12.34..64.00 rows=1 width=115) (actual time=5.790..127.495 rows=1,419 loops=1)

19. 0.268 123.792 ↓ 2,352.0 2,352 1

Nested Loop (cost=12.07..63.70 rows=1 width=131) (actual time=5.783..123.792 rows=2,352 loops=1)

20. 0.761 118.538 ↓ 2,493.0 2,493 1

Nested Loop (cost=11.64..63.21 rows=1 width=147) (actual time=5.773..118.538 rows=2,493 loops=1)

  • Join Filter: (an.grammar_id = messages.grammar_id)
21. 0.615 112.417 ↓ 206.2 2,680 1

Nested Loop (cost=11.22..56.39 rows=13 width=99) (actual time=5.760..112.417 rows=2,680 loops=1)

22. 0.103 110.584 ↓ 174.0 174 1

Nested Loop (cost=10.79..54.43 rows=1 width=72) (actual time=5.751..110.584 rows=174 loops=1)

23. 0.085 109.926 ↓ 111.0 111 1

Nested Loop (cost=10.37..39.88 rows=1 width=40) (actual time=5.742..109.926 rows=111 loops=1)

24. 0.054 108.857 ↓ 164.0 164 1

Nested Loop (cost=9.95..20.12 rows=1 width=40) (actual time=5.731..108.857 rows=164 loops=1)

25. 0.083 108.639 ↓ 164.0 164 1

Nested Loop (cost=9.80..17.74 rows=1 width=48) (actual time=5.726..108.639 rows=164 loops=1)

26. 0.150 108.064 ↓ 164.0 164 1

Nested Loop (cost=9.51..15.23 rows=1 width=40) (actual time=5.718..108.064 rows=164 loops=1)

27. 12.752 107.586 ↓ 164.0 164 1

Nested Loop (cost=9.24..12.73 rows=1 width=32) (actual time=5.708..107.586 rows=164 loops=1)

28. 15.908 66.354 ↓ 28,480.0 28,480 1

Nested Loop (cost=8.94..12.27 rows=1 width=24) (actual time=5.305..66.354 rows=28,480 loops=1)

29. 17.043 21.966 ↓ 28,480.0 28,480 1

Bitmap Heap Scan on workspaces wk (cost=8.65..9.77 rows=1 width=24) (actual time=5.296..21.966 rows=28,480 loops=1)

  • Recheck Cond: ((phase_type = 'exploration'::text) AND (build_state = 'workable'::text))
  • Filter: (opt_in AND (NOT exclude_from_reporting))
  • Rows Removed by Filter: 52
  • Heap Blocks: exact=3,610
30. 0.120 4.923 ↓ 0.0 0 1

BitmapAnd (cost=8.65..8.65 rows=1 width=0) (actual time=4.923..4.923 rows=0 loops=1)

31. 1.787 1.787 ↓ 131.7 30,035 1

Bitmap Index Scan on idx_26018_index_workspaces_on_phase_type (cost=0.00..4.20 rows=228 width=0) (actual time=1.787..1.787 rows=30,035 loops=1)

  • Index Cond: (phase_type = 'exploration'::text)
32. 3.016 3.016 ↓ 240.7 54,877 1

Bitmap Index Scan on idx_26018_index_workspaces_on_build_state (cost=0.00..4.20 rows=228 width=0) (actual time=3.016..3.016 rows=54,877 loops=1)

  • Index Cond: (build_state = 'workable'::text)
33. 28.480 28.480 ↑ 1.0 1 28,480

Index Scan using index_sensei_experiments_on_id on sensei_experiments exp (cost=0.29..2.51 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=28,480)

  • Index Cond: (id = wk.experiment_id)
34. 28.480 28.480 ↓ 0.0 0 28,480

Index Scan using idx_25121_primary on campaigns cmp (cost=0.29..0.45 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=28,480)

  • Index Cond: (id = exp.campaign_id)
  • Filter: (client_id = 674)
  • Rows Removed by Filter: 1
35. 0.328 0.328 ↑ 1.0 1 164

Index Scan using idx_25162_primary on clients (cost=0.28..2.50 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=164)

  • Index Cond: (id = 674)
36. 0.492 0.492 ↑ 1.0 1 164

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

  • Index Cond: (workspace_id = wk.id)
  • Heap Fetches: 164
37. 0.164 0.164 ↑ 1.0 1 164

Index Only Scan using idx_25149_primary on channels ch (cost=0.15..2.37 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=164)

  • Index Cond: (id = wk.channel_id)
  • Heap Fetches: 0
38. 0.984 0.984 ↑ 5.0 1 164

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

  • Index Cond: (workspace_id = sg.workspace_id)
  • Filter: (state = 'analyzed'::text)
  • Rows Removed by Filter: 3
39. 0.555 0.555 ↑ 56.0 2 111

Index Scan using idx_234_action_approved on analyses an (cost=0.42..13.43 rows=112 width=32) (actual time=0.004..0.005 rows=2 loops=111)

  • Index Cond: ((grammar_id = gr.id) AND (approved = true))
  • Filter: (approved AND approved_for_reporting)
  • Rows Removed by Filter: 0
40. 1.218 1.218 ↓ 1.2 15 174

Index Scan using index_message_metrics_on_analysis_id on message_metrics (cost=0.43..1.82 rows=13 width=35) (actual time=0.004..0.007 rows=15 loops=174)

  • Index Cond: (analysis_id = an.id)
41. 5.360 5.360 ↑ 1.0 1 2,680

Index Scan using idx_25659_primary on messages (cost=0.43..0.51 rows=1 width=48) (actual time=0.002..0.002 rows=1 loops=2,680)

  • Index Cond: (id = message_metrics.message_id)
  • Filter: (message_grammar_type IS NULL)
  • Rows Removed by Filter: 0
42. 4.986 4.986 ↑ 4.0 1 2,493

Index Scan using idx_3241_grammar_id_response_action_id on grammar_response_actions gra (cost=0.42..0.67 rows=4 width=16) (actual time=0.002..0.002 rows=1 loops=2,493)

  • Index Cond: ((grammar_id = an.grammar_id) AND (response_action_id = an.response_action_id))
  • Filter: "primary
  • Rows Removed by Filter: 0
43. 2.352 2.352 ↑ 1.0 1 2,352

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=2,352)

  • Index Cond: (id = an.response_action_id)
  • Filter: (kind = 'instant'::text)
  • Rows Removed by Filter: 0
44. 1.419 1.419 ↑ 1.0 1 1,419

Index Scan using idx_25722_primary on performance_queries pq (cost=0.29..0.38 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=1,419)

  • 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
45. 2.601 2.601 ↑ 1,423.8 5 867

Index Scan using idx_25668_index_message_gene_values_on_message_id on message_gene_values mgv (cost=0.43..130.62 rows=7,119 width=16) (actual time=0.002..0.003 rows=5 loops=867)

  • Index Cond: (message_id = messages.id)
46. 9.482 9.482 ↑ 1.0 1 4,741

Index Scan using idx_25507_primary on gene_values (cost=0.43..0.64 rows=1 width=48) (actual time=0.002..0.002 rows=1 loops=4,741)

  • Index Cond: (id = mgv.gene_value_id)
47. 4.741 4.741 ↑ 27.0 1 4,741

Index Only Scan using idx_25143_index_campaign_tags_campaigns_on_campaign_id_and_camp on campaign_tags_campaigns (cost=0.28..0.85 rows=27 width=8) (actual time=0.001..0.001 rows=1 loops=4,741)

  • Index Cond: (campaign_id = cmp.id)
  • Heap Fetches: 2,653
48. 4.741 4.741 ↓ 0.0 0 4,741

Index Scan using idx_25464_index_formatting_tags_gene_values_on_gene_value_id on formatting_tags_gene_values ftgv (cost=0.30..27.60 rows=1,560 width=16) (actual time=0.001..0.001 rows=0 loops=4,741)

  • Index Cond: (gene_value_id = mgv.gene_value_id)
49. 0.012 0.032 ↑ 5.0 72 1

Hash (cost=13.60..13.60 rows=360 width=40) (actual time=0.032..0.032 rows=72 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
50. 0.020 0.020 ↑ 5.0 72 1

Seq Scan on formatting_tags ft (cost=0.00..13.60 rows=360 width=40) (actual time=0.006..0.020 rows=72 loops=1)

51. 126.023 212.628 ↓ 1.6 593,234 1

Hash (cost=13,361.60..13,361.60 rows=381,760 width=40) (actual time=212.628..212.628 rows=593,234 loops=1)

  • Buckets: 1,048,576 (originally 524288) Batches: 2 (originally 1) Memory Usage: 26,329kB
52. 86.605 86.605 ↓ 1.6 593,234 1

Seq Scan on genes (cost=0.00..13,361.60 rows=381,760 width=40) (actual time=0.005..86.605 rows=593,234 loops=1)

53.          

SubPlan (for Hash Join)

54. 0.258 12,678.120 ↓ 0.0 0 258

Nested Loop (cost=1.30..113,170.43 rows=1,543 width=0) (actual time=49.140..49.140 rows=0 loops=258)

55. 0.260 12,677.862 ↓ 0.0 0 258

Nested Loop (cost=0.88..113,148.50 rows=1,543 width=8) (actual time=49.139..49.139 rows=0 loops=258)

56. 1,315.284 12,675.282 ↑ 780.0 2 258

Merge Join (cost=0.73..112,856.10 rows=1,560 width=16) (actual time=44.256..49.129 rows=2 loops=258)

  • Merge Cond: (gv2.id = ftgv2.gene_value_id)
57. 7,230.192 7,230.192 ↑ 16,238.0 2 258

Index Only Scan using index_gene_values_on_id_and_gene_id_and_position on gene_values gv2 (cost=0.43..104,924.98 rows=32,476 width=16) (actual time=23.153..28.024 rows=2 loops=258)

  • Index Cond: (gene_id = genes.id)
  • Filter: (id <> gene_values.id)
  • Rows Removed by Filter: 1
  • Heap Fetches: 121
58. 4,129.806 4,129.806 ↑ 3.7 85,172 258

Index Scan using idx_25464_index_formatting_tags_gene_values_on_gene_value_id on formatting_tags_gene_values ftgv2 (cost=0.30..7,054.45 rows=311,950 width=16) (actual time=0.005..16.007 rows=85,172 loops=258)

59. 2.320 2.320 ↓ 0.0 0 580

Index Scan using idx_25452_primary on formatting_tags ft2 (cost=0.15..0.19 rows=1 width=8) (actual time=0.004..0.004 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
60. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.42..2.65 rows=1 width=8) (never executed)

61. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_25486_primary on genes g2 (cost=0.42..2.64 rows=1 width=8) (never executed)

  • Index Cond: (id = genes.id)
  • Heap Fetches: 0
62. 0.258 35.088 ↓ 0.0 0 258

Nested Loop (cost=5,735.23..40,198.04 rows=16,238 width=0) (actual time=0.136..0.136 rows=0 loops=258)

63. 24.098 34.830 ↓ 0.0 0 258

Index Scan using idx_25507_index_gene_values_on_gene_id on gene_values gv3 (cost=5,734.81..39,992.42 rows=16,238 width=8) (actual time=0.135..0.135 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
64.          

SubPlan (for Index Scan)

65. 10.732 10.732 ↑ 3.1 101,754 1

Seq Scan on formatting_tags_gene_values (cost=0.00..4,954.50 rows=311,950 width=8) (actual time=0.010..10.732 rows=101,754 loops=1)

66. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.42..2.65 rows=1 width=8) (never executed)

67. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_25486_primary on genes g3 (cost=0.42..2.64 rows=1 width=8) (never executed)

  • Index Cond: (id = genes.id)
  • Heap Fetches: 0
68. 4.552 9.158 ↓ 0.0 0 4,579

Hash Left Join (cost=15.46..85.32 rows=3,465 width=16) (actual time=0.002..0.002 rows=0 loops=4,579)

  • Hash Cond: (etgv.emotional_tag_id = et.id)
69. 4.579 4.579 ↓ 0.0 0 4,579

Index Scan using idx_25323_index_emotional_tags_gene_values_on_gene_value_id on emotional_tags_gene_values etgv (cost=0.42..61.07 rows=3,465 width=16) (actual time=0.001..0.001 rows=0 loops=4,579)

  • Index Cond: (gene_value_id = mgv.gene_value_id)
70. 0.004 0.027 ↑ 6.0 26 1

Hash (cost=13.10..13.10 rows=155 width=8) (actual time=0.027..0.027 rows=26 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
71. 0.023 0.023 ↑ 6.0 26 1

Seq Scan on emotional_tags et (cost=0.00..13.10 rows=155 width=8) (actual time=0.006..0.023 rows=26 loops=1)

  • Filter: (NOT use_in_apollo_only)
  • Rows Removed by Filter: 67
72. 0.893 3.645 ↓ 619.5 6,815 1

Hash (cost=166.34..166.34 rows=11 width=12) (actual time=3.645..3.645 rows=6,815 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 357kB
73. 2.752 2.752 ↓ 619.5 6,815 1

Index Scan using index_functional_insight_metrics_on_taggable on functional_insight_metrics fim (cost=0.29..166.34 rows=11 width=12) (actual time=0.023..2.752 rows=6,815 loops=1)

  • Index Cond: ((taggable_type)::text = 'FunctionalTag'::text)
  • Filter: (include_in_report AND (NOT add_on))
  • Rows Removed by Filter: 326
Planning time : 28.267 ms
Execution time : 13,203.715 ms