explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6a0C

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 1,837.326 ↑ 1.0 40 1

Limit (cost=253,167.62..253,167.72 rows=40 width=288) (actual time=1,837.302..1,837.326 rows=40 loops=1)

2.          

CTE persons_term

3. 0.069 4.072 ↑ 1.2 44 1

Bitmap Heap Scan on persons (cost=24.57..246.02 rows=55 width=4) (actual time=4.011..4.072 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.003 4.003 ↑ 1.2 44 1

Bitmap Index Scan on persons_new_tsv_index (cost=0.00..24.55 rows=55 width=0) (actual time=4.003..4.003 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. 10.530 14.179 ↓ 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=4.630..14.179 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. 3.649 3.649 ↑ 1.3 8,451 1

Bitmap Index Scan on companies_tsv_index (cost=0.00..102.00 rows=10,667 width=0) (actual time=3.649..3.649 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.135 ↓ 0.0 0 1

Bitmap Heap Scan on dropdown_options (cost=109.40..111.72 rows=1 width=4) (actual time=0.135..0.135 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.001 0.134 ↓ 0.0 0 1

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

11. 0.055 0.055 ↑ 232.0 1 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=1 loops=1)

  • Index Cond: ((organization_id = 7) AND (to_tsvector('simple'::regconfig, COALESCE(text, ''::text)) @@ '''venture'':*'::tsquery))
12. 0.078 0.078 ↑ 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.078..0.078 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. 17.948 1,837.311 ↑ 333.2 40 1

Sort (cost=231,441.84..231,475.16 rows=13,329 width=288) (actual time=1,837.300..1,837.311 rows=40 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: 35kB
14. 61.585 1,819.363 ↓ 2.9 38,363 1

Hash Left Join (cost=144,816.15..231,020.52 rows=13,329 width=288) (actual time=942.972..1,819.363 rows=38,363 loops=1)

  • Hash Cond: (companies.id = last_event_subquery.distinct_col)
15. 46.723 1,449.090 ↓ 2.9 38,363 1

Hash Join (cost=46,459.68..132,480.70 rows=13,329 width=274) (actual time=634.252..1,449.090 rows=38,363 loops=1)

  • Hash Cond: (companies.id = companies_term.id)
16. 298.298 825.926 ↓ 3.4 89,587 1

Hash Join (cost=15,825.71..101,744.75 rows=26,658 width=278) (actual time=57.642..825.926 rows=89,587 loops=1)

  • Hash Cond: (companies.id = organizations_relevant_companies.company_id)
17. 470.157 470.309 ↓ 2.0 890,922 1

Seq Scan on companies (cost=31.03..84,038.38 rows=438,695 width=274) (actual time=0.172..470.309 rows=890,922 loops=1)

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

SubPlan (forSeq Scan)

19. 0.152 0.152 ↓ 1.1 32 1

Seq Scan on hidden_globals (cost=0.00..30.95 rows=30 width=4) (actual time=0.006..0.152 rows=32 loops=1)

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

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

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

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

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

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

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

  • Group Key: companies_term.id
24. 25.551 497.576 ↓ 4.0 46,574 1

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

25. 5.940 32.531 ↓ 1.4 8,463 1

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

  • Group Key: companies_term.id
26. 3.278 26.591 ↓ 1.4 8,508 1

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

27. 18.960 18.960 ↓ 1.4 8,451 1

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

28. 0.039 4.353 ↓ 57.0 57 1

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

29. 4.094 4.094 ↑ 1.2 44 1

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

30. 0.220 0.220 ↑ 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.004..0.005 rows=1 loops=44)

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

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

32. 45.663 417.718 ↓ 7.0 38,111 1

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

  • Group Key: entity_values.company_id, entity_values.list_entry_id
33. 23.210 372.055 ↓ 11.1 60,407 1

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

34. 0.008 10.711 ↑ 5.0 6 1

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

  • Group Key: entity_values.company_id, entity_values.list_entry_id
35. 0.003 10.703 ↑ 5.0 6 1

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

36. 0.005 10.563 ↑ 4.8 6 1

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

  • Group Key: entity_values.company_id, entity_values.list_entry_id
37. 0.005 10.558 ↑ 4.8 6 1

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

38. 0.049 2.127 ↑ 10.0 1 1

Nested Loop (cost=105.46..5,913.02 rows=10 width=8) (actual time=2.031..2.127 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.068 ↓ 0.0 0 44

Bitmap Heap Scan on entity_values (cost=105.46..107.48 rows=1 width=12) (actual time=0.047..0.047 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.010 2.024 ↓ 0.0 0 44

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

42. 0.088 0.088 ↓ 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.002..0.002 rows=0 loops=44)

  • Index Cond: (value_person_id = persons_term_1.id)
43. 1.926 1.926 ↑ 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.642..0.642 rows=6,773 loops=3)

  • Index Cond: (entity_attribute_id = 13499)
44. 1.620 8.426 ↑ 3.8 5 1

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

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

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

  • Sort Key: entity_values_1.value_company_id
  • Sort Method: quicksort Memory: 25kB
46. 0.023 0.040 ↑ 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.022..0.040 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.017 0.017 ↑ 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.017..0.017 rows=14 loops=1)

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

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

  • Sort Key: companies_term_1.id
  • Sort Method: quicksort Memory: 781kB
49. 1.943 1.943 ↓ 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.000..1.943 rows=8,451 loops=1)

50. 0.001 0.137 ↓ 0.0 0 1

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

51. 0.136 0.136 ↓ 0.0 0 1

CTE Scan on dropdown_options_term (cost=0.00..0.02 rows=1 width=4) (actual time=0.136..0.136 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. 43.864 338.134 ↓ 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=296.580..338.134 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. 294.270 294.270 ↓ 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=294.270..294.270 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. 10.670 308.688 ↓ 40.9 34,810 1

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

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1792kB
59. 14.526 298.018 ↓ 40.9 34,810 1

Subquery Scan on last_event_subquery (cost=98,333.07..98,345.83 rows=851 width=16) (actual time=232.182..298.018 rows=34,810 loops=1)

60. 27.520 283.492 ↓ 40.9 34,810 1

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

61. 67.687 255.972 ↓ 107.1 91,161 1

Sort (cost=98,333.07..98,335.20 rows=851 width=16) (actual time=232.178..255.972 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
62. 58.510 188.285 ↓ 107.1 91,161 1

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

  • Hash Cond: (companies_persons_1.person_id = person_smart_attributes.person_id)
63. 41.703 49.729 ↓ 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.237..49.729 rows=91,310 loops=1)

  • Recheck Cond: (organization_id = 7)
  • Heap Blocks: exact=13563
64. 8.026 8.026 ↓ 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.026..8.026 rows=91,312 loops=1)

  • Index Cond: (organization_id = 7)
65. 31.133 80.046 ↓ 1.1 101,364 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 4714kB
66. 41.830 48.913 ↓ 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.566..48.913 rows=101,364 loops=1)

  • Recheck Cond: (organization_id = 7)
  • Heap Blocks: exact=9606
67. 7.083 7.083 ↓ 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.083..7.083 rows=101,364 loops=1)

  • Index Cond: (organization_id = 7)