explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MAWg

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 6,135.179 ↑ 1.0 1 1

Limit (cost=155,733.41..155,733.42 rows=1 width=8) (actual time=6,135.165..6,135.179 rows=1 loops=1)

2. 9.864 6,135.165 ↑ 1.0 1 1

Aggregate (cost=155,733.41..155,733.42 rows=1 width=8) (actual time=6,135.164..6,135.165 rows=1 loops=1)

3. 28.939 6,125.301 ↓ 250.5 39,078 1

Nested Loop (cost=46,919.10..155,731.46 rows=156 width=248) (actual time=5,210.831..6,125.301 rows=39,078 loops=1)

4.          

CTE persons_term

5. 0.064 12.570 ↓ 1.7 48 1

Bitmap Heap Scan on persons (cost=354.29..467.22 rows=28 width=4) (actual time=12.513..12.570 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
  • Heap Blocks: exact=49
6. 12.506 12.506 ↓ 1.8 49 1

Bitmap Index Scan on persons_new_tsv_index (cost=0.00..354.28 rows=28 width=0) (actual time=12.505..12.506 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))
7.          

CTE companies_term

8. 17.062 25.770 ↑ 1.2 8,960 1

Bitmap Heap Scan on companies companies_1 (cost=229.80..38,425.20 rows=10,961 width=4) (actual time=10.473..25.770 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
9. 8.708 8.708 ↑ 1.1 19,110 1

Bitmap Index Scan on companies_tsv_index (cost=0.00..227.06 rows=20,408 width=0) (actual time=8.707..8.708 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'':*'::tsq
10. 280.781 6,005.048 ↓ 15.2 45,657 1

Hash Join (cost=8,026.24..115,311.99 rows=2,995 width=8) (actual time=5,209.872..6,005.048 rows=45,657 loops=1)

  • Hash Cond: (companies.id = companies_term.id)
11. 514.782 515.051 ↓ 2.0 962,536 1

Seq Scan on companies (cost=71.04..106,101.76 rows=478,108 width=4) (actual time=0.313..515.051 rows=962,536 loops=1)

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

SubPlan (forSeq Scan)

13. 0.269 0.269 ↑ 1.0 72 1

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

  • Filter: (organization_id = 7)
  • Rows Removed by Filter: 3621
14. 15.243 5,209.216 ↓ 4.1 45,683 1

Hash (cost=7,815.79..7,815.79 rows=11,153 width=4) (actual time=5,209.216..5,209.216 rows=45,683 loops=1)

  • Buckets: 65536 (originally 16384) Batches: 1 (originally 1) Memory Usage: 2119kB
15. 43.177 5,193.973 ↓ 4.1 45,683 1

HashAggregate (cost=7,592.73..7,704.26 rows=11,153 width=4) (actual time=5,180.198..5,193.973 rows=45,683 loops=1)

  • Group Key: companies_term.id
16. 27.743 5,150.796 ↓ 6.3 70,578 1

Append (cost=485.32..7,564.85 rows=11,153 width=4) (actual time=50.632..5,150.796 rows=70,578 loops=1)

17. 6.142 53.287 ↑ 1.2 8,972 1

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

  • Group Key: companies_term.id
18. 3.597 47.145 ↑ 1.2 9,017 1

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

19. 30.683 30.683 ↑ 1.2 8,960 1

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

20. 0.028 12.865 ↓ 57.0 57 1

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

21. 12.597 12.597 ↓ 1.7 48 1

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

22. 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)
23. 52.694 5,069.766 ↓ 322.5 61,606 1

Bitmap Heap Scan on entity_values (cost=5,978.35..6,748.75 rows=191 width=4) (actual time=5,021.666..5,069.766 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, ''
  • Heap Blocks: exact=27242
24. 5,017.072 5,017.072 ↓ 322.5 61,606 1

Bitmap Index Scan on entity_values_tsv_index (cost=0.00..5,978.30 rows=191 width=0) (actual time=5,017.072..5,017.072 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
25. 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