explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4vmo

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 44,866.799 ↑ 1.0 1 1

Limit (cost=1,116,273.14..1,116,273.15 rows=1 width=8) (actual time=44,866.787..44,866.799 rows=1 loops=1)

2. 6.069 44,866.798 ↑ 1.0 1 1

Aggregate (cost=1,116,273.14..1,116,273.15 rows=1 width=8) (actual time=44,866.787..44,866.798 rows=1 loops=1)

3. 12.484 44,860.729 ↓ 118.6 121,465 1

Subquery Scan on companies (cost=1,116,244.98..1,116,260.34 rows=1,024 width=248) (actual time=44,817.935..44,860.729 rows=121,465 loops=1)

4. 18.428 44,848.245 ↓ 118.6 121,465 1

Unique (cost=1,116,244.98..1,116,250.10 rows=1,024 width=248) (actual time=44,817.932..44,848.245 rows=121,465 loops=1)

5. 51.527 44,829.817 ↓ 118.6 121,465 1

Sort (cost=1,116,244.98..1,116,247.54 rows=1,024 width=248) (actual time=44,817.931..44,829.817 rows=121,465 loops=1)

  • Sort Key: companies_1.id
  • Sort Method: quicksort Memory: 8,766kB
6. 0.000 44,778.290 ↓ 118.6 121,465 1

Nested Loop (cost=824,033.60..1,116,193.78 rows=1,024 width=248) (actual time=42,292.097..44,778.290 rows=121,465 loops=1)

7. 583.214 43,917.683 ↓ 11.0 493,338 1

Hash Join (cost=824,033.16..1,079,548.71 rows=44,807 width=8) (actual time=42,292.001..43,917.683 rows=493,338 loops=1)

  • Hash Cond: (companies_1.id = companies_2.id)
8. 1,044.598 1,057.006 ↓ 1.9 2,047,213 1

Seq Scan on companies companies_1 (cost=3,613.65..256,241.56 rows=1,100,053 width=4) (actual time=12.747..1,057.006 rows=2,047,213 loops=1)

  • Filter: (((org_id IS NULL) OR (org_id = 7)) AND (NOT (hashed SubPlan 1)))
  • Rows Removed by Filter: 4,579,700
9.          

SubPlan (for Seq Scan)

10. 12.408 12.408 ↓ 1.1 1,705 1

Seq Scan on hidden_globals (cost=0.00..3,609.62 rows=1,610 width=4) (actual time=0.008..12.408 rows=1,705 loops=1)

  • Filter: (org_id = 7)
  • Rows Removed by Filter: 172,079
11. 75.874 42,277.463 ↓ 1.7 493,986 1

Hash (cost=816,783.37..816,783.37 rows=290,891 width=4) (actual time=42,277.455..42,277.463 rows=493,986 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 21,463kB
12. 271.597 42,201.589 ↓ 1.7 493,986 1

HashAggregate (cost=810,965.55..813,874.46 rows=290,891 width=4) (actual time=42,097.616..42,201.589 rows=493,986 loops=1)

  • Group Key: companies_2.id
13. 39.288 41,929.992 ↓ 1.8 515,696 1

Append (cost=8,047.25..810,238.32 rows=290,891 width=4) (actual time=293.087..41,929.992 rows=515,696 loops=1)

14. 372.599 641.753 ↓ 1.4 407,365 1

Bitmap Heap Scan on companies companies_2 (cost=8,047.25..646,552.99 rows=290,677 width=4) (actual time=293.086..641.753 rows=407,365 loops=1)

  • Recheck Cond: ((setweight(to_tsvector('simple'::regconfig, COALESCE(name, ''::text)), 'A'::"char") || setweight(to_tsvector('simple'::regconfig, COALESCE(domain, ''::text)), 'B'::"char")) @@ '''c'':*'::tsquery)
  • Filter: ((org_id = 7) OR (org_id IS NULL))
  • Rows Removed by Filter: 690,663
  • Heap Blocks: exact=133,465
15. 269.154 269.154 ↓ 1.2 1,098,241 1

Bitmap Index Scan on companies_tsv_index (cost=0.00..7,974.58 rows=943,544 width=0) (actual time=269.154..269.154 rows=1,098,241 loops=1)

  • Index Cond: ((setweight(to_tsvector('simple'::regconfig, COALESCE(name, ''::text)), 'A'::"char") || setweight(to_tsvector('simple'::regconfig, COALESCE(domain, ''::text)), 'B'::"char")) @@ '''c'':*'::tsquery)
16. 8.476 41,248.951 ↓ 506.2 108,331 1

Subquery Scan on t1 (cost=159,317.69..159,321.97 rows=214 width=4) (actual time=41,217.823..41,248.951 rows=108,331 loops=1)

17. 120.532 41,240.475 ↓ 506.2 108,331 1

HashAggregate (cost=159,317.69..159,319.83 rows=214 width=8) (actual time=41,217.822..41,240.475 rows=108,331 loops=1)

  • Group Key: entity_values.company_id, entity_values.list_entry_id
18. 25.662 41,119.943 ↓ 1,227.3 262,643 1

Append (cost=157,731.68..159,316.62 rows=214 width=8) (actual time=4,316.764..41,119.943 rows=262,643 loops=1)

19. 3.820 4,353.618 ↓ 2,193.3 6,580 1

Hash Join (cost=157,731.68..157,900.48 rows=3 width=8) (actual time=4,316.763..4,353.618 rows=6,580 loops=1)

  • Hash Cond: (entity_values.value_person_id = persons.id)
20. 35.726 129.387 ↑ 1.0 25,756 1

Bitmap Heap Scan on entity_values (cost=10,349.96..10,450.54 rows=25,993 width=12) (actual time=96.284..129.387 rows=25,756 loops=1)

  • Recheck Cond: ((entity_attribute_id = 13,499) AND (org_id = 7) AND (value_person_id IS NOT NULL))
  • Heap Blocks: exact=18,831
21. 1.052 93.661 ↓ 0.0 0 1

BitmapAnd (cost=10,349.96..10,349.96 rows=50 width=0) (actual time=93.660..93.661 rows=0 loops=1)

22. 2.893 2.893 ↑ 1.0 25,765 1

Bitmap Index Scan on entity_values_entity_attribute_id_company_id_index (cost=0.00..435.52 rows=25,993 width=0) (actual time=2.893..2.893 rows=25,765 loops=1)

  • Index Cond: (entity_attribute_id = 13,499)
23. 89.716 89.716 ↓ 2.1 1,286,503 1

Bitmap Index Scan on entity_values_org_id_value_person_id_index (cost=0.00..9,901.20 rows=625,151 width=0) (actual time=89.716..89.716 rows=1,286,503 loops=1)

  • Index Cond: (org_id = 7)
24. 14.934 4,220.411 ↓ 1.4 51,838 1

Hash (cost=146,911.61..146,911.61 rows=37,608 width=4) (actual time=4,220.410..4,220.411 rows=51,838 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,335kB
25. 1,952.604 4,205.477 ↓ 1.4 51,838 1

Bitmap Heap Scan on persons (cost=981.49..146,911.61 rows=37,608 width=4) (actual time=2,268.161..4,205.477 rows=51,838 loops=1)

  • Recheck Cond: ((org_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")) @@ '''c'':*'::tsquery))
  • Rows Removed by Index Recheck: 96,042
  • Heap Blocks: exact=94,271
26. 2,252.873 2,252.873 ↓ 3.9 147,890 1

Bitmap Index Scan on persons_new_tsv_index (cost=0.00..972.08 rows=37,608 width=0) (actual time=2,252.873..2,252.873 rows=147,890 loops=1)

  • Index Cond: ((org_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")) @@ '''c'':*'::tsquery))
27. 5,859.311 36,740.663 ↓ 1,213.6 256,063 1

Bitmap Heap Scan on entity_values entity_values_1 (cost=560.17..1,412.93 rows=211 width=8) (actual time=30,937.105..36,740.663 rows=256,063 loops=1)

  • Recheck Cond: ((org_id = 7) AND (entity_attribute_id = ANY ('{338,291,1286,1287,1285,405001,2989,37445,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))) @@ '''c'':*'::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))))
  • Rows Removed by Index Recheck: 745,513
  • Heap Blocks: exact=274,367
28. 30,881.352 30,881.352 ↓ 4,748.8 1,002,006 1

Bitmap Index Scan on entity_values_tsv_index (cost=0.00..560.11 rows=211 width=0) (actual time=30,881.351..30,881.352 rows=1,002,006 loops=1)

  • Index Cond: ((org_id = 7) AND (entity_attribute_id = ANY ('{338,291,1286,1287,1285,405001,2989,37445,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))) @@ '''c'':*'::tsquery))
29. 986.676 986.676 ↓ 0.0 0 493,338

Index Only Scan using organizations_relevant_companies_org_id_company_id_key on organizations_relevant_companies (cost=0.44..0.82 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=493,338)

  • Index Cond: ((org_id = 7) AND (company_id = companies_1.id))
  • Heap Fetches: 121,465
Planning time : 4.831 ms
Execution time : 44,899.700 ms