explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MVhs

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 999.449 ↑ 1.0 40 1

Limit (cost=240,265.78..240,265.88 rows=40 width=308) (actual time=999.428..999.449 rows=40 loops=1)

2.          

CTE persons_term

3. 0.104 4.118 ↓ 1.2 68 1

Bitmap Heap Scan on persons (cost=24.57..246.02 rows=55 width=4) (actual time=4.023..4.118 rows=68 loops=1)

  • Recheck Cond: ((organization_id = 7) AND (((((setweight(to_tsvector('simple'::regconfig, COALESCE(first_name, ''::text)), 'A'::"char") || setweight(to_tsvector('simple'::regconfig, COALESCE(last_name, ''::text)), 'A'::"char")) || setweight(to_tsvector('simple'::regconfig, COALESCE(immutable_array_to_string(nicknames, ' '::text), ''::text)), 'B'::"char")) || setweight(to_tsvector('simple'::regconfig, COALESCE(immutable_array_to_string(emails, ' '::text), ''::text)), 'B'::"char")) || setweight(to_tsvector('simple'::regconfig, COALESCE(immutable_array_to_string(email_domains, ' '::text), ''::text)), 'B'::"char")) @@ '''capital'':*'::tsquery))
  • Heap Blocks: exact=67
4. 4.014 4.014 ↓ 1.2 68 1

Bitmap Index Scan on persons_new_tsv_index (cost=0.00..24.55 rows=55 width=0) (actual time=4.014..4.014 rows=68 loops=1)

  • Index Cond: ((organization_id = 7) AND (((((setweight(to_tsvector('simple'::regconfig, COALESCE(first_name, ''::text)), 'A'::"char") || setweight(to_tsvector('simple'::regconfig, COALESCE(last_name, ''::text)), 'A'::"char")) || setweight(to_tsvector('simple'::regconfig, COALESCE(immutable_array_to_string(nicknames, ' '::text), ''::text)), 'B'::"char")) || setweight(to_tsvector('simple'::regconfig, COALESCE(immutable_array_to_string(emails, ' '::text), ''::text)), 'B'::"char")) || setweight(to_tsvector('simple'::regconfig, COALESCE(immutable_array_to_string(email_domains, ' '::text), ''::text)), 'B'::"char")) @@ '''capital'':*'::tsquery))
5.          

CTE companies_term

6. 16.952 22.686 ↓ 1.6 13,920 1

Bitmap Heap Scan on companies companies_1 (cost=141.09..28,541.49 rows=8,655 width=4) (actual time=7.189..22.686 rows=13,920 loops=1)

  • Recheck Cond: ((setweight(to_tsvector('simple'::regconfig, COALESCE(name, ''::text)), 'A'::"char") || setweight(to_tsvector('simple'::regconfig, COALESCE(domain, ''::text)), 'B'::"char")) @@ '''capital'':*'::tsquery)
  • Filter: ((organization_id = 7) OR (organization_id IS NULL))
  • Heap Blocks: exact=9805
7. 5.734 5.734 ↑ 1.1 13,920 1

Bitmap Index Scan on companies_tsv_index (cost=0.00..138.92 rows=15,057 width=0) (actual time=5.734..5.734 rows=13,920 loops=1)

  • Index Cond: ((setweight(to_tsvector('simple'::regconfig, COALESCE(name, ''::text)), 'A'::"char") || setweight(to_tsvector('simple'::regconfig, COALESCE(domain, ''::text)), 'B'::"char")) @@ '''capital'':*'::tsquery)
8.          

CTE dropdown_options_term

9. 0.002 0.057 ↓ 0.0 0 1

Bitmap Heap Scan on dropdown_options (cost=109.40..111.72 rows=1 width=4) (actual time=0.057..0.057 rows=0 loops=1)

  • Recheck Cond: ((organization_id = 7) AND (to_tsvector('simple'::regconfig, COALESCE(text, ''::text)) @@ '''capital'':*'::tsquery) AND (entity_attribute_id = ANY ('{37445,1286,291,338,1287,2988,2989,1285,1288,130660,120416,73481,60778,10900,13499,1366,77661,105061,105482,107467,107738,108622,113138,114986,115312,115418,116564,117170,118588,118623,121602,123265,123266,123316,125445,129018,129935}'::integer[])))
10. 0.000 0.055 ↓ 0.0 0 1

BitmapAnd (cost=109.40..109.40 rows=1 width=0) (actual time=0.055..0.055 rows=0 loops=1)

11. 0.055 0.055 ↓ 0.0 0 1

Bitmap Index Scan on dropdown_options_tsv_index (cost=0.00..20.32 rows=232 width=0) (actual time=0.055..0.055 rows=0 loops=1)

  • Index Cond: ((organization_id = 7) AND (to_tsvector('simple'::regconfig, COALESCE(text, ''::text)) @@ '''capital'':*'::tsquery))
12. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on dropdown_options_entity_attribute_id_index (cost=0.00..88.84 rows=197 width=0) (never executed)

  • Index Cond: (entity_attribute_id = ANY ('{37445,1286,291,338,1287,2988,2989,1285,1288,130660,120416,73481,60778,10900,13499,1366,77661,105061,105482,107467,107738,108622,113138,114986,115312,115418,116564,117170,118588,118623,121602,123265,123266,123316,125445,129018,129935}'::integer[]))
13. 12.734 999.436 ↑ 666.4 40 1

Sort (cost=211,366.55..211,433.19 rows=26,657 width=308) (actual time=999.427..999.436 rows=40 loops=1)

  • Sort Key: person_smart_attributes.last_event_date DESC NULLS LAST, ((lower(companies.name) = 'capital'::text)) DESC NULLS LAST, ((lower(companies.domain) = 'capital'::text)) DESC NULLS LAST, companies.id
  • Sort Method: top-N heapsort Memory: 35kB
14. 38.063 986.702 ↑ 1.1 23,311 1

Hash Left Join (cost=144,817.79..210,523.93 rows=26,657 width=308) (actual time=742.288..986.702 rows=23,311 loops=1)

  • Hash Cond: (companies.id = companies_persons_1.company_id)
15. 23.680 653.639 ↑ 1.1 23,311 1

Hash Join (cost=46,461.32..111,800.77 rows=26,657 width=274) (actual time=447.258..653.639 rows=23,311 loops=1)

  • Hash Cond: (companies.id = organizations_relevant_companies.company_id)
16. 28.556 571.653 ↑ 12.6 34,863 1

Nested Loop (cost=30,666.63..94,094.45 rows=438,686 width=278) (actual time=388.800..571.653 rows=34,863 loops=1)

17. 29.087 403.625 ↓ 2.5 34,868 1

HashAggregate (cost=30,666.20..30,807.23 rows=14,103 width=4) (actual time=388.768..403.625 rows=34,868 loops=1)

  • Group Key: companies_term.id
18. 13.586 374.538 ↓ 2.6 36,509 1

Append (cost=527.98..30,630.95 rows=14,103 width=4) (actual time=46.985..374.538 rows=36,509 loops=1)

19. 10.696 51.486 ↓ 1.6 13,925 1

HashAggregate (cost=527.98..614.54 rows=8,656 width=4) (actual time=46.983..51.486 rows=13,925 loops=1)

  • Group Key: companies_term.id
20. 5.584 40.790 ↓ 1.6 13,987 1

Append (cost=0.00..506.33 rows=8,656 width=4) (actual time=7.193..40.790 rows=13,987 loops=1)

21. 30.680 30.680 ↓ 1.6 13,920 1

CTE Scan on companies_term (cost=0.00..173.10 rows=8,655 width=4) (actual time=7.193..30.680 rows=13,920 loops=1)

22. 0.097 4.526 ↓ 67.0 67 1

Nested Loop (cost=0.43..246.68 rows=1 width=4) (actual time=4.049..4.526 rows=67 loops=1)

23. 4.157 4.157 ↓ 1.2 68 1

CTE Scan on persons_term (cost=0.00..1.10 rows=55 width=4) (actual time=4.025..4.157 rows=68 loops=1)

24. 0.272 0.272 ↑ 1.0 1 68

Index Scan using companies_persons_person_id_index on companies_persons (cost=0.43..4.46 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=68)

  • Index Cond: (person_id = persons_term.id)
  • Filter: (organization_id = 7)
25. 8.946 309.466 ↓ 4.1 22,584 1

Subquery Scan on t1 (cost=29,679.88..29,788.82 rows=5,447 width=4) (actual time=293.622..309.466 rows=22,584 loops=1)

26. 20.769 300.520 ↓ 4.1 22,584 1

HashAggregate (cost=29,679.88..29,734.35 rows=5,447 width=8) (actual time=293.621..300.520 rows=22,584 loops=1)

  • Group Key: entity_values.company_id, entity_values.list_entry_id
27. 12.645 279.751 ↓ 6.1 33,275 1

Append (cost=9,735.22..29,652.65 rows=5,447 width=8) (actual time=18.170..279.751 rows=33,275 loops=1)

28. 0.006 18.170 ↑ 12.7 3 1

HashAggregate (cost=9,735.22..9,735.60 rows=38 width=8) (actual time=18.169..18.170 rows=3 loops=1)

  • Group Key: entity_values.company_id, entity_values.list_entry_id
29. 0.003 18.164 ↑ 12.7 3 1

Append (cost=9,705.20..9,735.03 rows=38 width=8) (actual time=18.103..18.164 rows=3 loops=1)

30. 0.006 18.102 ↑ 12.3 3 1

HashAggregate (cost=9,705.20..9,705.57 rows=37 width=8) (actual time=18.102..18.102 rows=3 loops=1)

  • Group Key: entity_values.company_id, entity_values.list_entry_id
31. 0.002 18.096 ↑ 12.3 3 1

Append (cost=105.46..9,705.02 rows=37 width=8) (actual time=13.695..18.096 rows=3 loops=1)

32. 0.022 5.279 ↓ 0.0 0 1

Nested Loop (cost=105.46..5,913.02 rows=10 width=8) (actual time=5.279..5.279 rows=0 loops=1)

33. 0.021 0.021 ↓ 1.2 68 1

CTE Scan on persons_term persons_term_1 (cost=0.00..1.10 rows=55 width=4) (actual time=0.001..0.021 rows=68 loops=1)

34. 0.068 5.236 ↓ 0.0 0 68

Bitmap Heap Scan on entity_values (cost=105.46..107.48 rows=1 width=12) (actual time=0.077..0.077 rows=0 loops=68)

  • Recheck Cond: ((value_person_id = persons_term_1.id) AND (entity_attribute_id = 13499))
  • Filter: (organization_id = 7)
35. 0.048 5.168 ↓ 0.0 0 68

BitmapAnd (cost=105.46..105.46 rows=1 width=0) (actual time=0.076..0.076 rows=0 loops=68)

36. 0.136 0.136 ↓ 0.0 0 68

Bitmap Index Scan on entity_values_value_person_id_index (cost=0.00..3.26 rows=111 width=0) (actual time=0.002..0.002 rows=0 loops=68)

  • Index Cond: (value_person_id = persons_term_1.id)
37. 4.984 4.984 ↑ 1.0 6,773 8

Bitmap Index Scan on entity_values_entity_attribute_id_company_id_index (cost=0.00..101.01 rows=7,011 width=0) (actual time=0.623..0.623 rows=6,773 loops=8)

  • Index Cond: (entity_attribute_id = 13499)
38. 2.279 12.815 ↑ 9.0 3 1

Merge Join (cost=3,743.53..3,791.62 rows=27 width=8) (actual time=8.415..12.815 rows=3 loops=1)

  • Merge Cond: (entity_values_1.value_company_id = companies_term_1.id)
39. 0.020 0.064 ↑ 65.0 14 1

Sort (cost=3,004.42..3,006.70 rows=910 width=12) (actual time=0.060..0.064 rows=14 loops=1)

  • Sort Key: entity_values_1.value_company_id
  • Sort Method: quicksort Memory: 25kB
40. 0.026 0.044 ↑ 65.0 14 1

Bitmap Heap Scan on entity_values entity_values_1 (cost=30.73..2,959.70 rows=910 width=12) (actual time=0.025..0.044 rows=14 loops=1)

  • Recheck Cond: (entity_attribute_id = ANY ('{120416,77661,115418,118588}'::integer[]))
  • Filter: (organization_id = 7)
  • Heap Blocks: exact=12
41. 0.018 0.018 ↑ 121.6 14 1

Bitmap Index Scan on entity_values_entity_attribute_id_company_id_index (cost=0.00..30.50 rows=1,703 width=0) (actual time=0.018..0.018 rows=14 loops=1)

  • Index Cond: (entity_attribute_id = ANY ('{120416,77661,115418,118588}'::integer[]))
42. 7.222 10.472 ↓ 1.2 10,478 1

Sort (cost=739.11..760.74 rows=8,655 width=4) (actual time=8.054..10.472 rows=10,478 loops=1)

  • Sort Key: companies_term_1.id
  • Sort Method: quicksort Memory: 1037kB
43. 3.250 3.250 ↓ 1.6 13,920 1

CTE Scan on companies_term companies_term_1 (cost=0.00..173.10 rows=8,655 width=4) (actual time=0.000..3.250 rows=13,920 loops=1)

44. 0.001 0.059 ↓ 0.0 0 1

Nested Loop (cost=26.66..28.71 rows=1 width=8) (actual time=0.059..0.059 rows=0 loops=1)

45. 0.058 0.058 ↓ 0.0 0 1

CTE Scan on dropdown_options_term (cost=0.00..0.02 rows=1 width=4) (actual time=0.058..0.058 rows=0 loops=1)

46. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on entity_values entity_values_2 (cost=26.66..28.68 rows=1 width=12) (never executed)

  • Recheck Cond: ((value_dropdown_option_id = dropdown_options_term.id) AND (entity_attribute_id = ANY ('{105482,108622,117170}'::integer[])))
  • Filter: (organization_id = 7)
47. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=26.66..26.66 rows=1 width=0) (never executed)

48. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on entity_values_value_dropdown_option_id_index (cost=0.00..3.36 rows=143 width=0) (never executed)

  • Index Cond: (value_dropdown_option_id = dropdown_options_term.id)
49. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on entity_values_entity_attribute_id_company_id_index (cost=0.00..22.88 rows=1,278 width=0) (never executed)

  • Index Cond: (entity_attribute_id = ANY ('{105482,108622,117170}'::integer[]))
50. 26.085 248.936 ↓ 6.2 33,272 1

Bitmap Heap Scan on entity_values entity_values_3 (cost=912.97..19,862.20 rows=5,409 width=8) (actual time=224.470..248.936 rows=33,272 loops=1)

  • Recheck Cond: ((organization_id = 7) AND (entity_attribute_id = ANY ('{37445,1286,291,338,1287,2989,1285,1288,73481,10900,1366,105061,107467,107738,113138,114986,115312,116564,118623,121602,123316,125445,129018,129935}'::integer[])) AND ((((((((to_tsvector('simple'::regconfig, COALESCE(value_text, ''::text)) || to_tsvector('simple'::regconfig, COALESCE(value_multiline_text, ''::text))) || to_tsvector('simple'::regconfig, COALESCE((value_number)::text, ''::text))) || to_tsvector('simple'::regconfig, COALESCE((value_location).street_address, ''::text))) || to_tsvector('simple'::regconfig, COALESCE((value_location).city, ''::text))) || to_tsvector('simple'::regconfig, COALESCE((value_location).state, ''::text))) || to_tsvector('simple'::regconfig, COALESCE((value_location).country, ''::text))) || to_tsvector('simple'::regconfig, COALESCE((value_location).continent, ''::text))) @@ '''capital'':*'::tsquery) AND ((value_text IS NOT NULL) OR (value_multiline_text IS NOT NULL) OR (value_number IS NOT NULL) OR (NOT (value_location IS NULL))))
  • Heap Blocks: exact=8792
51. 222.851 222.851 ↓ 6.2 33,276 1

Bitmap Index Scan on entity_values_tsv_index (cost=0.00..911.62 rows=5,409 width=0) (actual time=222.851..222.851 rows=33,276 loops=1)

  • Index Cond: ((organization_id = 7) AND (entity_attribute_id = ANY ('{37445,1286,291,338,1287,2989,1285,1288,73481,10900,1366,105061,107467,107738,113138,114986,115312,116564,118623,121602,123316,125445,129018,129935}'::integer[])) AND ((((((((to_tsvector('simple'::regconfig, COALESCE(value_text, ''::text)) || to_tsvector('simple'::regconfig, COALESCE(value_multiline_text, ''::text))) || to_tsvector('simple'::regconfig, COALESCE((value_number)::text, ''::text))) || to_tsvector('simple'::regconfig, COALESCE((value_location).street_address, ''::text))) || to_tsvector('simple'::regconfig, COALESCE((value_location).city, ''::text))) || to_tsvector('simple'::regconfig, COALESCE((value_location).state, ''::text))) || to_tsvector('simple'::regconfig, COALESCE((value_location).country, ''::text))) || to_tsvector('simple'::regconfig, COALESCE((value_location).continent, ''::text))) @@ '''capital'':*'::tsquery))
52. 139.472 139.472 ↑ 1.0 1 34,868

Index Scan using companies_pkey on companies (cost=0.43..4.47 rows=1 width=274) (actual time=0.003..0.004 rows=1 loops=34,868)

  • Index Cond: (id = companies_term.id)
  • Filter: (((organization_id = 7) OR (organization_id IS NULL)) AND (id <> ALL ('{161800872,162557504,58730420,148821180,164006542,1590904,136380421,161850747,161878208,197473816,1780258,164006542,1630399,158344254,1553029,1733171,161818105,149474826,2167322,1514731,148821510,1536609,1527003,1666699,163278493,161870196,1630706,1584523,198234314,113832612,1586770,1706391}'::integer[])))
  • Rows Removed by Filter: 0
53. 27.457 58.306 ↑ 1.0 89,595 1

Hash (cost=14,635.36..14,635.36 rows=92,746 width=4) (actual time=58.306..58.306 rows=89,595 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 4174kB
54. 30.849 30.849 ↑ 1.0 89,595 1

Index Only Scan using organizations_relevant_companies_organization_id_company_id_idx on organizations_relevant_companies (cost=0.43..14,635.36 rows=92,746 width=4) (actual time=0.017..30.849 rows=89,595 loops=1)

  • Index Cond: (organization_id = 7)
  • Heap Fetches: 89595
55. 10.183 295.000 ↓ 40.9 34,810 1

Hash (cost=98,345.83..98,345.83 rows=851 width=16) (actual time=295.000..295.000 rows=34,810 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1778kB
56. 27.768 284.817 ↓ 40.9 34,810 1

Unique (cost=98,333.07..98,337.32 rows=851 width=16) (actual time=235.845..284.817 rows=34,810 loops=1)

57. 65.165 257.049 ↓ 107.1 91,161 1

Sort (cost=98,333.07..98,335.20 rows=851 width=16) (actual time=235.842..257.049 rows=91,161 loops=1)

  • Sort Key: companies_persons_1.company_id, person_smart_attributes.last_event_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 7346kB
58. 61.298 191.884 ↓ 107.1 91,161 1

Hash Join (cost=50,643.16..98,291.66 rows=851 width=16) (actual time=91.586..191.884 rows=91,161 loops=1)

  • Hash Cond: (companies_persons_1.person_id = person_smart_attributes.person_id)
59. 41.685 49.794 ↓ 1.0 91,310 1

Bitmap Heap Scan on companies_persons companies_persons_1 (cost=1,411.08..48,709.14 rows=91,180 width=8) (actual time=10.317..49.794 rows=91,310 loops=1)

  • Recheck Cond: (organization_id = 7)
  • Heap Blocks: exact=13563
60. 8.109 8.109 ↓ 1.0 91,312 1

Bitmap Index Scan on companies_persons_organization_id_company_id_person_id_index (cost=0.00..1,388.28 rows=91,180 width=0) (actual time=8.109..8.109 rows=91,312 loops=1)

  • Index Cond: (organization_id = 7)
61. 31.412 80.792 ↓ 1.1 101,364 1

Hash (cost=48,033.74..48,033.74 rows=95,867 width=16) (actual time=80.792..80.792 rows=101,364 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 4714kB
62. 42.341 49.380 ↓ 1.1 101,364 1

Bitmap Heap Scan on person_smart_attributes (cost=1,269.40..48,033.74 rows=95,867 width=16) (actual time=8.504..49.380 rows=101,364 loops=1)

  • Recheck Cond: (organization_id = 7)
  • Heap Blocks: exact=9606
63. 7.039 7.039 ↓ 1.1 101,364 1

Bitmap Index Scan on person_smart_attributes_temp_organization_id_person_id_idx1 (cost=0.00..1,245.44 rows=95,867 width=0) (actual time=7.039..7.039 rows=101,364 loops=1)

  • Index Cond: (organization_id = 7)