explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9Ez

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

Limit (cost=229,350.08..229,350.18 rows=40 width=288) (actual time=1,163.287..1,163.311 rows=40 loops=1)

2.          

CTE persons_term

3. 0.002 0.260 ↑ 55.0 1 1

Bitmap Heap Scan on persons (cost=24.57..246.02 rows=55 width=4) (actual time=0.260..0.260 rows=1 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")) @@ '''believe'':*'::tsquery))
  • Heap Blocks: exact=1
4. 0.258 0.258 ↑ 55.0 1 1

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

CTE companies_term

6. 0.042 0.077 ↑ 3.8 23 1

Bitmap Heap Scan on companies companies_1 (cost=17.17..395.45 rows=88 width=4) (actual time=0.043..0.077 rows=23 loops=1)

  • Recheck Cond: ((setweight(to_tsvector('simple'::regconfig, COALESCE(name, ''::text)), 'A'::"char") || setweight(to_tsvector('simple'::regconfig, COALESCE(domain, ''::text)), 'B'::"char")) @@ '''believe'':*'::tsquery)
  • Filter: ((organization_id = 7) OR (organization_id IS NULL))
  • Heap Blocks: exact=23
7. 0.035 0.035 ↑ 6.7 23 1

Bitmap Index Scan on companies_tsv_index (cost=0.00..17.14 rows=153 width=0) (actual time=0.035..0.035 rows=23 loops=1)

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

CTE dropdown_options_term

9. 0.001 0.047 ↓ 0.0 0 1

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

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

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

11. 0.045 0.045 ↓ 0.0 0 1

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

  • Index Cond: ((organization_id = 7) AND (to_tsvector('simple'::regconfig, COALESCE(text, ''::text)) @@ '''believe'':*'::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. 0.392 1,163.296 ↑ 333.2 40 1

Sort (cost=228,596.88..228,630.21 rows=13,329 width=288) (actual time=1,163.286..1,163.296 rows=40 loops=1)

  • Sort Key: last_event_subquery.last_event_date DESC NULLS LAST, ((lower(companies.name) = 'believe'::text)) DESC NULLS LAST, ((lower(companies.domain) = 'believe'::text)) DESC NULLS LAST, companies.id
  • Sort Method: top-N heapsort Memory: 35kB
14. 0.434 1,162.904 ↑ 114.9 116 1

Hash Left Join (cost=141,972.25..228,175.56 rows=13,329 width=288) (actual time=432.924..1,162.904 rows=116 loops=1)

  • Hash Cond: (companies.id = last_event_subquery.distinct_col)
15. 22.627 856.538 ↑ 114.9 116 1

Hash Join (cost=43,615.78..129,635.74 rows=13,329 width=274) (actual time=126.961..856.538 rows=116 loops=1)

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

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

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

Seq Scan on companies (cost=31.03..84,038.38 rows=438,695 width=274) (actual time=0.177..457.322 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.149 0.149 ↓ 1.1 32 1

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

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

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

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

  • Index Cond: (organization_id = 7)
  • Heap Fetches: 89595
22. 0.042 18.287 ↑ 40.2 137 1

Hash (cost=27,721.19..27,721.19 rows=5,510 width=4) (actual time=18.287..18.287 rows=137 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 69kB
23. 0.101 18.245 ↑ 40.2 137 1

HashAggregate (cost=27,610.99..27,666.09 rows=5,510 width=4) (actual time=18.205..18.245 rows=137 loops=1)

  • Group Key: companies_term.id
24. 0.055 18.144 ↑ 39.6 139 1

Append (cost=249.55..27,597.22 rows=5,510 width=4) (actual time=0.395..18.144 rows=139 loops=1)

25. 0.014 0.395 ↑ 3.9 23 1

HashAggregate (cost=249.55..250.44 rows=89 width=4) (actual time=0.395..0.395 rows=23 loops=1)

  • Group Key: companies_term.id
26. 0.011 0.381 ↑ 3.7 24 1

Append (cost=0.00..249.32 rows=89 width=4) (actual time=0.045..0.381 rows=24 loops=1)

27. 0.091 0.091 ↑ 3.8 23 1

CTE Scan on companies_term (cost=0.00..1.76 rows=88 width=4) (actual time=0.044..0.091 rows=23 loops=1)

28. 0.003 0.279 ↑ 1.0 1 1

Nested Loop (cost=0.43..246.68 rows=1 width=4) (actual time=0.277..0.279 rows=1 loops=1)

29. 0.262 0.262 ↑ 55.0 1 1

CTE Scan on persons_term (cost=0.00..1.10 rows=55 width=4) (actual time=0.261..0.262 rows=1 loops=1)

30. 0.014 0.014 ↑ 1.0 1 1

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

  • Index Cond: (person_id = persons_term.id)
  • Filter: (organization_id = 7)
31. 0.042 17.694 ↑ 46.7 116 1

Subquery Scan on t1 (cost=27,182.37..27,290.79 rows=5,421 width=4) (actual time=17.610..17.694 rows=116 loops=1)

32. 0.096 17.652 ↑ 46.7 116 1

HashAggregate (cost=27,182.37..27,236.58 rows=5,421 width=8) (actual time=17.610..17.652 rows=116 loops=1)

  • Group Key: entity_values.company_id, entity_values.list_entry_id
33. 0.042 17.556 ↑ 46.7 116 1

Append (cost=7,238.62..27,155.27 rows=5,421 width=8) (actual time=17.330..17.556 rows=116 loops=1)

34. 0.002 0.129 ↓ 0.0 0 1

HashAggregate (cost=7,238.62..7,238.74 rows=12 width=8) (actual time=0.129..0.129 rows=0 loops=1)

  • Group Key: entity_values.company_id, entity_values.list_entry_id
35. 0.001 0.127 ↓ 0.0 0 1

Append (cost=7,209.51..7,238.56 rows=12 width=8) (actual time=0.127..0.127 rows=0 loops=1)

36. 0.000 0.078 ↓ 0.0 0 1

HashAggregate (cost=7,209.51..7,209.62 rows=11 width=8) (actual time=0.078..0.078 rows=0 loops=1)

  • Group Key: entity_values.company_id, entity_values.list_entry_id
37. 0.002 0.078 ↓ 0.0 0 1

Append (cost=105.46..7,209.46 rows=11 width=8) (actual time=0.078..0.078 rows=0 loops=1)

38. 0.004 0.013 ↓ 0.0 0 1

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

39. 0.000 0.000 ↑ 55.0 1 1

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

40. 0.001 0.009 ↓ 0.0 0 1

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

  • Recheck Cond: ((value_person_id = persons_term_1.id) AND (entity_attribute_id = 13499))
  • Filter: (organization_id = 7)
41. 0.001 0.008 ↓ 0.0 0 1

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

42. 0.007 0.007 ↓ 0.0 0 1

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

  • Index Cond: (value_person_id = persons_term_1.id)
43. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on entity_values_entity_attribute_id_company_id_index (cost=0.00..101.01 rows=7,011 width=0) (never executed)

  • Index Cond: (entity_attribute_id = 13499)
44. 0.011 0.063 ↓ 0.0 0 1

Nested Loop (cost=0.43..1,296.32 rows=1 width=8) (actual time=0.063..0.063 rows=0 loops=1)

45. 0.006 0.006 ↑ 3.8 23 1

CTE Scan on companies_term companies_term_1 (cost=0.00..1.76 rows=88 width=4) (actual time=0.001..0.006 rows=23 loops=1)

46. 0.046 0.046 ↓ 0.0 0 23

Index Scan using entity_values_value_company_id_index on entity_values entity_values_1 (cost=0.43..14.70 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=23)

  • Index Cond: (value_company_id = companies_term_1.id)
  • Filter: ((organization_id = 7) AND (entity_attribute_id = ANY ('{120416,77661,115418,118588}'::integer[])))
47. 0.000 0.048 ↓ 0.0 0 1

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

48. 0.048 0.048 ↓ 0.0 0 1

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

49. 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)
50. 0.000 0.000 ↓ 0.0 0

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

51. 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)
52. 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[]))
53. 0.198 17.385 ↑ 46.6 116 1

Bitmap Heap Scan on entity_values entity_values_3 (cost=912.97..19,862.20 rows=5,409 width=8) (actual time=17.201..17.385 rows=116 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))) @@ '''believe'':*'::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=114
54. 17.187 17.187 ↑ 46.6 116 1

Bitmap Index Scan on entity_values_tsv_index (cost=0.00..911.62 rows=5,409 width=0) (actual time=17.187..17.187 rows=116 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))) @@ '''believe'':*'::tsquery))
55. 10.884 305.932 ↓ 40.9 34,810 1

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

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1792kB
56. 14.728 295.048 ↓ 40.9 34,810 1

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

57. 27.712 280.320 ↓ 40.9 34,810 1

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

58. 65.120 252.608 ↓ 107.1 91,161 1

Sort (cost=98,333.07..98,335.20 rows=851 width=16) (actual time=230.707..252.608 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
59. 58.067 187.488 ↓ 107.1 91,161 1

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

  • Hash Cond: (companies_persons_1.person_id = person_smart_attributes.person_id)
60. 40.867 48.922 ↓ 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.278..48.922 rows=91,310 loops=1)

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

  • Index Cond: (organization_id = 7)
62. 31.306 80.499 ↓ 1.1 101,364 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 4714kB
63. 42.093 49.193 ↓ 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.562..49.193 rows=101,364 loops=1)

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

  • Index Cond: (organization_id = 7)