explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wnS9 : w/o hidden globals

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 420.051 ↑ 1.0 40 1

Limit (cost=181,219.51..181,219.61 rows=40 width=308) (actual time=420.021..420.051 rows=40 loops=1)

2.          

CTE persons_term

3. 0.026 0.883 ↑ 3.4 16 1

Bitmap Heap Scan on persons (cost=24.57..246.02 rows=55 width=4) (actual time=0.861..0.883 rows=16 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")) @@ '''expert'':*'::tsquery))
  • Heap Blocks: exact=16
4. 0.857 0.857 ↑ 3.4 16 1

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

CTE companies_term

6. 1.295 1.763 ↑ 1.3 884 1

Bitmap Heap Scan on companies companies_1 (cost=31.51..4,722.94 rows=1,167 width=4) (actual time=0.565..1.763 rows=884 loops=1)

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

Bitmap Index Scan on companies_tsv_index (cost=0.00..31.22 rows=2,029 width=0) (actual time=0.468..0.468 rows=884 loops=1)

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

CTE dropdown_options_term

9. 0.001 0.046 ↓ 0.0 0 1

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

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

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

11. 0.044 0.044 ↓ 0.0 0 1

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

  • Index Cond: ((organization_id = 7) AND (to_tsvector('simple'::regconfig, COALESCE(text, ''::text)) @@ '''expert'':*'::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.887 420.031 ↑ 666.5 40 1

Sort (cost=176,138.83..176,205.47 rows=26,658 width=308) (actual time=420.019..420.031 rows=40 loops=1)

  • Sort Key: person_smart_attributes.last_event_date DESC NULLS LAST, ((lower(companies.name) = 'expert'::text)) DESC NULLS LAST, ((lower(companies.domain) = 'expert'::text)) DESC NULLS LAST, companies.id
  • Sort Method: top-N heapsort Memory: 35kB
14. 0.993 419.144 ↑ 46.0 580 1

Hash Left Join (cost=143,634.38..175,296.18 rows=26,658 width=308) (actual time=409.294..419.144 rows=580 loops=1)

  • Hash Cond: (companies.id = companies_persons_1.company_id)
15. 1.376 106.994 ↑ 46.0 580 1

Hash Join (cost=45,277.91..76,573.01 rows=26,658 width=274) (actual time=98.105..106.994 rows=580 loops=1)

  • Hash Cond: (companies.id = organizations_relevant_companies.company_id)
16. 1.737 48.094 ↑ 309.6 1,417 1

Nested Loop (cost=29,483.22..58,866.64 rows=438,695 width=278) (actual time=40.141..48.094 rows=1,417 loops=1)

17. 1.043 40.689 ↑ 4.7 1,417 1

HashAggregate (cost=29,482.80..29,548.72 rows=6,592 width=4) (actual time=40.117..40.689 rows=1,417 loops=1)

  • Group Key: companies_term.id
18. 0.623 39.646 ↑ 4.6 1,440 1

Append (cost=284.62..29,466.32 rows=6,592 width=4) (actual time=3.947..39.646 rows=1,440 loops=1)

19. 0.601 4.179 ↑ 1.3 886 1

HashAggregate (cost=284.62..296.30 rows=1,168 width=4) (actual time=3.947..4.179 rows=886 loops=1)

  • Group Key: companies_term.id
20. 0.343 3.578 ↑ 1.3 900 1

Append (cost=0.00..281.69 rows=1,168 width=4) (actual time=0.568..3.578 rows=900 loops=1)

21. 2.243 2.243 ↑ 1.3 884 1

CTE Scan on companies_term (cost=0.00..23.34 rows=1,167 width=4) (actual time=0.568..2.243 rows=884 loops=1)

22. 0.016 0.992 ↓ 16.0 16 1

Nested Loop (cost=0.43..246.68 rows=1 width=4) (actual time=0.877..0.992 rows=16 loops=1)

23. 0.896 0.896 ↑ 3.4 16 1

CTE Scan on persons_term (cost=0.00..1.10 rows=55 width=4) (actual time=0.863..0.896 rows=16 loops=1)

24. 0.080 0.080 ↑ 1.0 1 16

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

  • Index Cond: (person_id = persons_term.id)
  • Filter: (organization_id = 7)
25. 0.214 34.844 ↑ 9.8 554 1

Subquery Scan on t1 (cost=28,983.94..29,092.42 rows=5,424 width=4) (actual time=34.466..34.844 rows=554 loops=1)

26. 0.388 34.630 ↑ 9.8 554 1

HashAggregate (cost=28,983.94..29,038.18 rows=5,424 width=8) (actual time=34.465..34.630 rows=554 loops=1)

  • Group Key: entity_values.company_id, entity_values.list_entry_id
27. 0.235 34.242 ↑ 9.8 554 1

Append (cost=9,040.08..28,956.82 rows=5,424 width=8) (actual time=33.189..34.242 rows=554 loops=1)

28. 0.000 1.704 ↓ 0.0 0 1

HashAggregate (cost=9,040.08..9,040.23 rows=15 width=8) (actual time=1.704..1.704 rows=0 loops=1)

  • Group Key: entity_values.company_id, entity_values.list_entry_id
29. 0.000 1.704 ↓ 0.0 0 1

Append (cost=9,010.87..9,040.01 rows=15 width=8) (actual time=1.704..1.704 rows=0 loops=1)

30. 0.002 1.656 ↓ 0.0 0 1

HashAggregate (cost=9,010.87..9,011.01 rows=14 width=8) (actual time=1.656..1.656 rows=0 loops=1)

  • Group Key: entity_values.company_id, entity_values.list_entry_id
31. 0.000 1.654 ↓ 0.0 0 1

Append (cost=105.46..9,010.80 rows=14 width=8) (actual time=1.654..1.654 rows=0 loops=1)

32. 0.007 0.782 ↓ 0.0 0 1

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

33. 0.007 0.007 ↑ 3.4 16 1

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

34. 0.016 0.768 ↓ 0.0 0 16

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

  • Recheck Cond: ((value_person_id = persons_term_1.id) AND (entity_attribute_id = 13499))
  • Filter: (organization_id = 7)
35. 0.011 0.752 ↓ 0.0 0 16

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

36. 0.032 0.032 ↓ 0.0 0 16

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=16)

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

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.709..0.709 rows=6,773 loops=1)

  • Index Cond: (entity_attribute_id = 13499)
38. 0.177 0.872 ↓ 0.0 0 1

Merge Join (cost=3,087.21..3,097.64 rows=4 width=8) (actual time=0.872..0.872 rows=0 loops=1)

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

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

  • Sort Key: entity_values_1.value_company_id
  • Sort Method: quicksort Memory: 25kB
40. 0.024 0.042 ↑ 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.023..0.042 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. 0.431 0.634 ↑ 1.5 760 1

Sort (cost=82.79..85.71 rows=1,167 width=4) (actual time=0.477..0.634 rows=760 loops=1)

  • Sort Key: companies_term_1.id
  • Sort Method: quicksort Memory: 66kB
43. 0.203 0.203 ↑ 1.3 884 1

CTE Scan on companies_term companies_term_1 (cost=0.00..23.34 rows=1,167 width=4) (actual time=0.000..0.203 rows=884 loops=1)

44. 0.002 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)

45. 0.046 0.046 ↓ 0.0 0 1

CTE Scan on dropdown_options_term (cost=0.00..0.02 rows=1 width=4) (actual time=0.046..0.046 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. 0.886 32.303 ↑ 9.8 554 1

Bitmap Heap Scan on entity_values entity_values_3 (cost=912.97..19,862.20 rows=5,409 width=8) (actual time=31.483..32.303 rows=554 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))) @@ '''expert'':*'::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=522
51. 31.417 31.417 ↑ 9.8 554 1

Bitmap Index Scan on entity_values_tsv_index (cost=0.00..911.62 rows=5,409 width=0) (actual time=31.417..31.417 rows=554 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))) @@ '''expert'':*'::tsquery))
52. 5.668 5.668 ↑ 1.0 1 1,417

Index Scan using companies_pkey on companies (cost=0.43..4.43 rows=1 width=274) (actual time=0.004..0.004 rows=1 loops=1,417)

  • Index Cond: (id = companies_term.id)
  • Filter: ((organization_id = 7) OR (organization_id IS NULL))
53. 26.540 57.524 ↑ 1.0 89,595 1

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

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

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

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

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

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

57. 64.565 272.769 ↓ 107.1 91,161 1

Sort (cost=98,333.07..98,335.20 rows=851 width=16) (actual time=251.378..272.769 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. 60.609 208.204 ↓ 107.1 91,161 1

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

  • Hash Cond: (companies_persons_1.person_id = person_smart_attributes.person_id)
59. 47.631 56.145 ↓ 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.712..56.145 rows=91,310 loops=1)

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

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

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

  • Buckets: 131072 Batches: 1 Memory Usage: 4714kB
62. 51.496 58.817 ↓ 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.798..58.817 rows=101,364 loops=1)

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

  • Index Cond: (organization_id = 7)