explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MOM

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 1,627.829 ↑ 1.0 45 1

Limit (cost=189,497.90..189,498.01 rows=45 width=288) (actual time=1,627.792..1,627.829 rows=45 loops=1)

2.          

CTE persons_term

3. 0.098 4.287 ↑ 1.2 44 1

Bitmap Heap Scan on persons (cost=24.57..246.02 rows=55 width=4) (actual time=4.196..4.287 rows=44 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")) @@ '''venture'':*'::tsquery))
  • Heap Blocks: exact=44
4. 4.189 4.189 ↑ 1.2 44 1

Bitmap Index Scan on persons_new_tsv_index (cost=0.00..24.55 rows=55 width=0) (actual time=4.189..4.189 rows=44 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")) @@ '''venture'':*'::tsquery))
5.          

CTE companies_term

6. 20.490 24.638 ↓ 1.4 8,451 1

Bitmap Heap Scan on companies companies_1 (cost=103.53..21,368.04 rows=6,132 width=4) (actual time=5.143..24.638 rows=8,451 loops=1)

  • Recheck Cond: ((setweight(to_tsvector('simple'::regconfig, COALESCE(name, ''::text)), 'A'::"char") || setweight(to_tsvector('simple'::regconfig, COALESCE(domain, ''::text)), 'B'::"char")) @@ '''venture'':*'::tsquery)
  • Filter: ((organization_id = 7) OR (organization_id IS NULL))
  • Heap Blocks: exact=6757
7. 4.148 4.148 ↑ 1.3 8,451 1

Bitmap Index Scan on companies_tsv_index (cost=0.00..102.00 rows=10,667 width=0) (actual time=4.148..4.148 rows=8,451 loops=1)

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

CTE dropdown_options_term

9. 0.001 0.181 ↓ 0.0 0 1

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

  • Recheck Cond: ((organization_id = 7) AND (to_tsvector('simple'::regconfig, COALESCE(text, ''::text)) @@ '''venture'':*'::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.002 0.180 ↓ 0.0 0 1

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

11. 0.083 0.083 ↑ 232.0 1 1

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

  • Index Cond: ((organization_id = 7) AND (to_tsvector('simple'::regconfig, COALESCE(text, ''::text)) @@ '''venture'':*'::tsquery))
12. 0.095 0.095 ↑ 19.7 10 1

Bitmap Index Scan on dropdown_options_entity_attribute_id_index (cost=0.00..88.84 rows=197 width=0) (actual time=0.095..0.095 rows=10 loops=1)

  • 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. 18.129 1,627.809 ↑ 296.2 45 1

Sort (cost=167,772.12..167,805.45 rows=13,329 width=288) (actual time=1,627.790..1,627.809 rows=45 loops=1)

  • Sort Key: last_event_subquery.last_event_date DESC NULLS LAST, ((lower(companies.name) = 'venture'::text)) DESC NULLS LAST, ((lower(companies.domain) = 'venture'::text)) DESC NULLS LAST, companies.id
  • Sort Method: top-N heapsort Memory: 36kB
14. 59.082 1,609.680 ↓ 2.9 38,330 1

Hash Left Join (cost=81,135.17..167,339.47 rows=13,329 width=288) (actual time=662.743..1,609.680 rows=38,330 loops=1)

  • Hash Cond: (companies.id = last_event_subquery.distinct_col)
15. 50.844 1,494.280 ↓ 2.9 38,330 1

Hash Join (cost=46,480.71..132,501.73 rows=13,329 width=274) (actual time=606.347..1,494.280 rows=38,330 loops=1)

  • Hash Cond: (companies.id = companies_term.id)
16. 334.863 898.061 ↓ 3.4 89,515 1

Hash Join (cost=15,846.74..101,765.78 rows=26,658 width=278) (actual time=60.545..898.061 rows=89,515 loops=1)

  • Hash Cond: (companies.id = organizations_relevant_companies.company_id)
17. 503.522 504.018 ↓ 2.0 889,922 1

Seq Scan on companies (cost=52.05..84,059.40 rows=438,695 width=274) (actual time=0.900..504.018 rows=889,922 loops=1)

  • Filter: (((organization_id IS NULL) OR (organization_id = 7)) AND (NOT (hashed SubPlan 4)))
  • Rows Removed by Filter: 636369
18.          

SubPlan (forSeq Scan)

19. 0.496 0.496 ↑ 1.0 1,032 1

Seq Scan on hidden_globals (cost=0.00..49.48 rows=1,032 width=4) (actual time=0.007..0.496 rows=1,032 loops=1)

  • Filter: (organization_id = 7)
  • Rows Removed by Filter: 1566
20. 27.830 59.180 ↑ 1.0 89,595 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 4174kB
21. 31.350 31.350 ↑ 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.031..31.350 rows=89,595 loops=1)

  • Index Cond: (organization_id = 7)
  • Heap Fetches: 89595
22. 12.885 545.375 ↓ 3.9 44,749 1

Hash (cost=30,489.32..30,489.32 rows=11,572 width=4) (actual time=545.375..545.375 rows=44,749 loops=1)

  • Buckets: 65536 (originally 16384) Batches: 1 (originally 1) Memory Usage: 2086kB
23. 30.414 532.490 ↓ 3.9 44,749 1

HashAggregate (cost=30,257.88..30,373.60 rows=11,572 width=4) (actual time=520.999..532.490 rows=44,749 loops=1)

  • Group Key: companies_term.id
24. 18.293 502.076 ↓ 4.0 46,574 1

Append (cost=445.98..30,228.95 rows=11,572 width=4) (actual time=40.783..502.076 rows=46,574 loops=1)

25. 5.964 43.233 ↓ 1.4 8,463 1

HashAggregate (cost=445.98..507.31 rows=6,133 width=4) (actual time=40.782..43.233 rows=8,463 loops=1)

  • Group Key: companies_term.id
26. 3.274 37.269 ↓ 1.4 8,508 1

Append (cost=0.00..430.64 rows=6,133 width=4) (actual time=5.147..37.269 rows=8,508 loops=1)

27. 29.232 29.232 ↓ 1.4 8,451 1

CTE Scan on companies_term (cost=0.00..122.64 rows=6,132 width=4) (actual time=5.147..29.232 rows=8,451 loops=1)

28. 0.049 4.763 ↓ 57.0 57 1

Nested Loop (cost=0.43..246.68 rows=1 width=4) (actual time=4.225..4.763 rows=57 loops=1)

29. 4.318 4.318 ↑ 1.2 44 1

CTE Scan on persons_term (cost=0.00..1.10 rows=55 width=4) (actual time=4.197..4.318 rows=44 loops=1)

30. 0.396 0.396 ↑ 1.0 1 44

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

  • Index Cond: (person_id = persons_term.id)
  • Filter: (organization_id = 7)
31. 15.597 440.550 ↓ 7.0 38,111 1

Subquery Scan on t1 (cost=29,435.81..29,544.59 rows=5,439 width=4) (actual time=410.816..440.550 rows=38,111 loops=1)

32. 39.528 424.953 ↓ 7.0 38,111 1

HashAggregate (cost=29,435.81..29,490.20 rows=5,439 width=8) (actual time=410.814..424.953 rows=38,111 loops=1)

  • Group Key: entity_values.company_id, entity_values.list_entry_id
33. 23.110 385.425 ↓ 11.1 60,407 1

Append (cost=9,491.42..29,408.61 rows=5,439 width=8) (actual time=10.950..385.425 rows=60,407 loops=1)

34. 0.006 10.951 ↑ 5.0 6 1

HashAggregate (cost=9,491.42..9,491.72 rows=30 width=8) (actual time=10.950..10.951 rows=6 loops=1)

  • Group Key: entity_values.company_id, entity_values.list_entry_id
35. 0.004 10.945 ↑ 5.0 6 1

Append (cost=9,461.69..9,491.27 rows=30 width=8) (actual time=10.756..10.945 rows=6 loops=1)

36. 0.012 10.758 ↑ 4.8 6 1

HashAggregate (cost=9,461.69..9,461.98 rows=29 width=8) (actual time=10.756..10.758 rows=6 loops=1)

  • Group Key: entity_values.company_id, entity_values.list_entry_id
37. 0.002 10.746 ↑ 4.8 6 1

Append (cost=105.46..9,461.54 rows=29 width=8) (actual time=2.083..10.746 rows=6 loops=1)

38. 0.020 2.186 ↑ 10.0 1 1

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

39. 0.010 0.010 ↑ 1.2 44 1

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

40. 0.044 2.156 ↓ 0.0 0 44

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

  • Recheck Cond: ((value_person_id = persons_term_1.id) AND (entity_attribute_id = 13499))
  • Filter: (organization_id = 7)
  • Heap Blocks: exact=1
41. 0.030 2.112 ↓ 0.0 0 44

BitmapAnd (cost=105.46..105.46 rows=1 width=0) (actual time=0.048..0.048 rows=0 loops=44)

42. 0.132 0.132 ↓ 0.0 0 44

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

  • Index Cond: (value_person_id = persons_term_1.id)
43. 1.950 1.950 ↑ 1.0 6,773 3

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.650..0.650 rows=6,773 loops=3)

  • Index Cond: (entity_attribute_id = 13499)
44. 1.578 8.558 ↑ 3.8 5 1

Merge Join (cost=3,512.83..3,548.23 rows=19 width=8) (actual time=5.280..8.558 rows=5 loops=1)

  • Merge Cond: (entity_values_1.value_company_id = companies_term_1.id)
45. 0.018 0.091 ↑ 65.0 14 1

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

  • Sort Key: entity_values_1.value_company_id
  • Sort Method: quicksort Memory: 25kB
46. 0.052 0.073 ↑ 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.026..0.073 rows=14 loops=1)

  • Recheck Cond: (entity_attribute_id = ANY ('{120416,77661,115418,118588}'::integer[]))
  • Filter: (organization_id = 7)
  • Heap Blocks: exact=12
47. 0.021 0.021 ↑ 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.021..0.021 rows=14 loops=1)

  • Index Cond: (entity_attribute_id = ANY ('{120416,77661,115418,118588}'::integer[]))
48. 4.878 6.889 ↓ 1.2 7,330 1

Sort (cost=508.41..523.74 rows=6,132 width=4) (actual time=5.187..6.889 rows=7,330 loops=1)

  • Sort Key: companies_term_1.id
  • Sort Method: quicksort Memory: 781kB
49. 2.011 2.011 ↓ 1.4 8,451 1

CTE Scan on companies_term companies_term_1 (cost=0.00..122.64 rows=6,132 width=4) (actual time=0.001..2.011 rows=8,451 loops=1)

50. 0.001 0.183 ↓ 0.0 0 1

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

51. 0.182 0.182 ↓ 0.0 0 1

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

52. 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)
53. 0.000 0.000 ↓ 0.0 0

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

54. 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)
55. 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[]))
56. 54.686 351.364 ↓ 11.2 60,401 1

Bitmap Heap Scan on entity_values entity_values_3 (cost=912.97..19,862.20 rows=5,409 width=8) (actual time=299.045..351.364 rows=60,401 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))) @@ '''venture'':*'::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=12040
57. 296.678 296.678 ↓ 11.2 60,402 1

Bitmap Index Scan on entity_values_tsv_index (cost=0.00..911.62 rows=5,409 width=0) (actual time=296.678..296.678 rows=60,402 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))) @@ '''venture'':*'::tsquery))
58. 1.224 56.318 ↓ 31.6 3,638 1

Hash (cost=34,653.02..34,653.02 rows=115 width=16) (actual time=56.318..56.318 rows=3,638 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 217kB
59. 1.485 55.094 ↓ 31.6 3,638 1

Subquery Scan on last_event_subquery (cost=34,651.30..34,653.02 rows=115 width=16) (actual time=49.259..55.094 rows=3,638 loops=1)

60. 2.506 53.609 ↓ 31.6 3,638 1

Unique (cost=34,651.30..34,651.87 rows=115 width=16) (actual time=49.257..53.609 rows=3,638 loops=1)

61. 5.927 51.103 ↓ 68.8 7,910 1

Sort (cost=34,651.30..34,651.59 rows=115 width=16) (actual time=49.256..51.103 rows=7,910 loops=1)

  • Sort Key: companies_persons_1.company_id, person_smart_attributes.last_event_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 563kB
62. 8.071 45.176 ↓ 68.8 7,910 1

Nested Loop (cost=0.73..34,647.36 rows=115 width=16) (actual time=0.044..45.176 rows=7,910 loops=1)

63. 3.713 3.713 ↑ 1.0 8,348 1

Index Scan using person_smart_attributes_temp_organization_id_person_id_idx on person_smart_attributes (cost=0.29..984.81 rows=8,427 width=16) (actual time=0.024..3.713 rows=8,348 loops=1)

  • Index Cond: (organization_id = 7)
64. 33.392 33.392 ↑ 1.0 1 8,348

Index Scan using companies_persons_person_id_index on companies_persons companies_persons_1 (cost=0.43..3.98 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=8,348)

  • Index Cond: (person_id = person_smart_attributes.person_id)
  • Filter: (organization_id = 7)