explain.depesz.com

PostgreSQL's explain analyze made readable

Result: elwZ : hg at end

Settings
# exclusive inclusive rows x rows loops node
1. 0.018 1,207.579 ↑ 1.0 40 1

Limit (cost=229,350.08..229,350.18 rows=40 width=308) (actual time=1,207.556..1,207.579 rows=40 loops=1)

2.          

CTE persons_term

3. 0.004 0.266 ↑ 55.0 1 1

Bitmap Heap Scan on persons (cost=24.57..246.02 rows=55 width=4) (actual time=0.266..0.266 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.262 0.262 ↑ 55.0 1 1

Bitmap Index Scan on persons_new_tsv_index (cost=0.00..24.55 rows=55 width=0) (actual time=0.262..0.262 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.046 0.087 ↑ 3.8 23 1

Bitmap Heap Scan on companies companies_1 (cost=17.17..395.45 rows=88 width=4) (actual time=0.052..0.087 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.041 0.041 ↑ 6.7 23 1

Bitmap Index Scan on companies_tsv_index (cost=0.00..17.14 rows=153 width=0) (actual time=0.041..0.041 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.052 ↓ 0.0 0 1

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

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

11. 0.050 0.050 ↓ 0.0 0 1

Bitmap Index Scan on dropdown_options_tsv_index (cost=0.00..20.32 rows=232 width=0) (actual time=0.050..0.050 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.413 1,207.561 ↑ 333.2 40 1

Sort (cost=228,596.88..228,630.21 rows=13,329 width=308) (actual time=1,207.554..1,207.561 rows=40 loops=1)

  • Sort Key: person_smart_attributes.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.475 1,207.148 ↑ 114.9 116 1

Hash Left Join (cost=141,972.25..228,175.56 rows=13,329 width=308) (actual time=419.978..1,207.148 rows=116 loops=1)

  • Hash Cond: (companies.id = companies_persons_1.company_id)
15. 23.332 915.644 ↑ 114.9 116 1

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

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

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

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

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

  • Filter: (((organization_id = 7) OR (organization_id IS NULL)) 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.697 57.247 ↑ 1.0 89,595 1

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

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

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

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

  • Buckets: 8192 Batches: 1 Memory Usage: 69kB
23. 0.110 18.346 ↑ 40.2 137 1

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

  • Group Key: companies_term.id
24. 0.045 18.236 ↑ 39.6 139 1

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

25. 0.030 0.431 ↑ 3.9 23 1

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

  • Group Key: companies_term.id
26. 0.010 0.401 ↑ 3.7 24 1

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

27. 0.103 0.103 ↑ 3.8 23 1

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

28. 0.008 0.288 ↑ 1.0 1 1

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

29. 0.267 0.267 ↑ 55.0 1 1

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

30. 0.013 0.013 ↑ 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.013 rows=1 loops=1)

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

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

32. 0.092 17.710 ↑ 46.7 116 1

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

  • Group Key: entity_values.company_id, entity_values.list_entry_id
33. 0.047 17.618 ↑ 46.7 116 1

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

34. 0.002 0.142 ↓ 0.0 0 1

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

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

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

36. 0.002 0.085 ↓ 0.0 0 1

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

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

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

38. 0.007 0.018 ↓ 0.0 0 1

Nested Loop (cost=105.46..5,913.02 rows=10 width=8) (actual time=0.018..0.018 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.003 0.011 ↓ 0.0 0 1

Bitmap Heap Scan on entity_values (cost=105.46..107.48 rows=1 width=12) (actual time=0.011..0.011 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.010 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.007 0.007 ↑ 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.007 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.054 ↓ 0.0 0 1

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

48. 0.054 0.054 ↓ 0.0 0 1

CTE Scan on dropdown_options_term (cost=0.00..0.02 rows=1 width=4) (actual time=0.054..0.054 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.196 17.429 ↑ 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.247..17.429 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.233 17.233 ↑ 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.233..17.233 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.345 291.029 ↓ 40.9 34,810 1

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

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

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

57. 64.903 252.777 ↓ 107.1 91,161 1

Sort (cost=98,333.07..98,335.20 rows=851 width=16) (actual time=231.666..252.777 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. 58.053 187.874 ↓ 107.1 91,161 1

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

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

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

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

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

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

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

  • Index Cond: (organization_id = 7)