explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OaVh

Settings
# exclusive inclusive rows x rows loops node
1. 0.031 6,400.388 ↑ 1.0 40 1

Limit (cost=263,971.13..263,971.23 rows=40 width=354) (actual time=6,400.349..6,400.388 rows=40 loops=1)

2.          

CTE persons_term

3. 0.066 10.677 ↓ 1.7 48 1

Bitmap Heap Scan on persons (cost=250.29..363.22 rows=28 width=4) (actual time=10.619..10.677 rows=48 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=49
4. 10.611 10.611 ↓ 1.8 49 1

Bitmap Index Scan on persons_new_tsv_index (cost=0.00..250.28 rows=28 width=0) (actual time=10.611..10.611 rows=49 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. 16.896 24.401 ↑ 1.2 8,960 1

Bitmap Heap Scan on companies companies_1 (cost=227.80..38,423.20 rows=10,961 width=4) (actual time=9.247..24.401 rows=8,960 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))
  • Rows Removed by Filter: 10148
  • Heap Blocks: exact=12927
7. 7.505 7.505 ↑ 1.1 19,110 1

Bitmap Index Scan on companies_tsv_index (cost=0.00..225.06 rows=20,408 width=0) (actual time=7.505..7.505 rows=19,110 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. 20.218 6,400.357 ↑ 3.9 40 1

Sort (cost=225,184.70..225,185.09 rows=156 width=354) (actual time=6,400.348..6,400.357 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: 38kB
9. 64.986 6,380.139 ↓ 250.5 39,078 1

Hash Left Join (cost=116,365.44..225,179.77 rows=156 width=354) (actual time=5,356.764..6,380.139 rows=39,078 loops=1)

  • Hash Cond: (companies.id = last_event_subquery.distinct_col)
10. 37.967 5,928.453 ↓ 250.5 39,078 1

Nested Loop (cost=10,344.67..119,157.04 rows=156 width=340) (actual time=4,970.044..5,928.453 rows=39,078 loops=1)

11. 298.601 5,799.172 ↓ 15.2 45,657 1

Hash Join (cost=10,344.24..117,629.99 rows=2,995 width=344) (actual time=4,969.091..5,799.172 rows=45,657 loops=1)

  • Hash Cond: (companies.id = companies_term.id)
12. 531.815 532.094 ↓ 2.0 962,524 1

Seq Scan on companies (cost=71.04..106,101.76 rows=478,108 width=340) (actual time=0.311..532.094 rows=962,524 loops=1)

  • Filter: (((organization_id IS NULL) OR (organization_id = 7)) AND (NOT (hashed SubPlan 3)))
  • Rows Removed by Filter: 825649
13.          

SubPlan (forSeq Scan)

14. 0.279 0.279 ↑ 1.0 72 1

Seq Scan on hidden_globals (cost=0.00..70.86 rows=72 width=4) (actual time=0.003..0.279 rows=72 loops=1)

  • Filter: (organization_id = 7)
  • Rows Removed by Filter: 3619
15. 15.358 4,968.477 ↓ 4.1 45,683 1

Hash (cost=10,133.79..10,133.79 rows=11,153 width=4) (actual time=4,968.477..4,968.477 rows=45,683 loops=1)

  • Buckets: 65536 (originally 16384) Batches: 1 (originally 1) Memory Usage: 2119kB
16. 45.421 4,953.119 ↓ 4.1 45,683 1

HashAggregate (cost=9,910.73..10,022.26 rows=11,153 width=4) (actual time=4,938.534..4,953.119 rows=45,683 loops=1)

  • Group Key: companies_term.id
17. 28.553 4,907.698 ↓ 6.3 70,578 1

Append (cost=485.32..9,882.85 rows=11,153 width=4) (actual time=46.969..4,907.698 rows=70,578 loops=1)

18. 5.900 49.477 ↑ 1.2 8,972 1

HashAggregate (cost=485.32..594.94 rows=10,962 width=4) (actual time=46.968..49.477 rows=8,972 loops=1)

  • Group Key: companies_term.id
19. 3.462 43.577 ↑ 1.2 9,017 1

Append (cost=0.00..457.92 rows=10,962 width=4) (actual time=9.249..43.577 rows=9,017 loops=1)

20. 29.127 29.127 ↑ 1.2 8,960 1

CTE Scan on companies_term (cost=0.00..219.22 rows=10,961 width=4) (actual time=9.249..29.127 rows=8,960 loops=1)

21. 0.030 10.988 ↓ 57.0 57 1

Nested Loop (cost=0.56..129.08 rows=1 width=4) (actual time=10.651..10.988 rows=57 loops=1)

22. 10.718 10.718 ↓ 1.7 48 1

CTE Scan on persons_term (cost=0.00..0.56 rows=28 width=4) (actual time=10.635..10.718 rows=48 loops=1)

23. 0.240 0.240 ↑ 1.0 1 48

Index Scan using companies_persons_person_id_index on companies_persons (cost=0.56..4.58 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=48)

  • Index Cond: (person_id = persons_term.id)
  • Filter: (organization_id = 7)
24. 57.489 4,829.668 ↓ 322.5 61,606 1

Bitmap Heap Scan on entity_values (cost=8,296.35..9,066.75 rows=191 width=4) (actual time=4,776.674..4,829.668 rows=61,606 loops=1)

  • Recheck Cond: ((organization_id = 7) AND (entity_attribute_id = ANY ('{1286,37445,291,338,1287,2989,1285,1288}'::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=27242
25. 4,772.179 4,772.179 ↓ 322.5 61,606 1

Bitmap Index Scan on entity_values_tsv_index (cost=0.00..8,296.30 rows=191 width=0) (actual time=4,772.179..4,772.179 rows=61,606 loops=1)

  • Index Cond: ((organization_id = 7) AND (entity_attribute_id = ANY ('{1286,37445,291,338,1287,2989,1285,1288}'::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))
26. 91.314 91.314 ↑ 1.0 1 45,657

Index Only Scan using organizations_relevant_companies_organization_id_company_id_idx on organizations_relevant_companies (cost=0.43..0.51 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=45,657)

  • Index Cond: ((organization_id = 7) AND (company_id = companies.id))
  • Heap Fetches: 5
27. 13.750 386.700 ↓ 38.4 39,995 1

Hash (cost=106,007.75..106,007.75 rows=1,041 width=16) (actual time=386.699..386.700 rows=39,995 loops=1)

  • Buckets: 65536 (originally 2048) Batches: 1 (originally 1) Memory Usage: 1984kB
28. 19.090 372.950 ↓ 38.4 39,995 1

Subquery Scan on last_event_subquery (cost=105,992.14..106,007.75 rows=1,041 width=16) (actual time=294.576..372.950 rows=39,995 loops=1)

29. 35.817 353.860 ↓ 38.4 39,995 1

Unique (cost=105,992.14..105,997.34 rows=1,041 width=16) (actual time=294.574..353.860 rows=39,995 loops=1)

30. 71.006 318.043 ↓ 98.6 102,658 1

Sort (cost=105,992.14..105,994.74 rows=1,041 width=16) (actual time=294.572..318.043 rows=102,658 loops=1)

  • Sort Key: companies_persons_1.company_id, person_smart_attributes.last_event_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 7885kB
31. 65.465 247.037 ↓ 98.6 102,658 1

Hash Join (cost=99,926.95..105,939.96 rows=1,041 width=16) (actual time=140.637..247.037 rows=102,658 loops=1)

  • Hash Cond: (companies_persons_1.person_id = person_smart_attributes.person_id)
32. 41.079 41.079 ↑ 1.0 102,858 1

Index Only Scan using companies_persons_organization_id_company_id_person_id_index on companies_persons companies_persons_1 (cost=0.56..5,742.16 rows=103,397 width=8) (actual time=0.011..41.079 rows=102,858 loops=1)

  • Index Cond: (organization_id = 7)
  • Heap Fetches: 16402
33. 41.949 140.493 ↑ 1.0 115,908 1

Hash (cost=98,425.60..98,425.60 rows=120,063 width=16) (actual time=140.492..140.493 rows=115,908 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 5255kB
34. 88.082 98.544 ↑ 1.0 115,908 1

Bitmap Heap Scan on person_smart_attributes (cost=2,502.92..98,425.60 rows=120,063 width=16) (actual time=16.598..98.544 rows=115,908 loops=1)

  • Recheck Cond: (organization_id = 7)
  • Heap Blocks: exact=36562
35. 10.462 10.462 ↑ 1.0 118,261 1

Bitmap Index Scan on person_smart_attributes_temp_organization_id_person_id_idx1 (cost=0.00..2,472.91 rows=120,063 width=0) (actual time=10.462..10.462 rows=118,261 loops=1)

  • Index Cond: (organization_id = 7)