explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nQhM : third time

Settings
# exclusive inclusive rows x rows loops node
1. 0.031 7,044.204 ↑ 1.0 40 1

Limit (cost=258,044.15..258,044.25 rows=40 width=354) (actual time=7,044.166..7,044.204 rows=40 loops=1)

2.          

CTE persons_term

3. 0.065 15.876 ↓ 1.7 48 1

Bitmap Heap Scan on persons (cost=866.29..979.22 rows=28 width=4) (actual time=15.819..15.876 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"))
  • Heap Blocks: exact=49
4. 15.811 15.811 ↓ 1.8 49 1

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

CTE companies_term

6. 16.501 24.967 ↑ 1.2 8,961 1

Bitmap Heap Scan on companies companies_1 (cost=241.80..38,437.20 rows=10,961 width=4) (actual time=10.196..24.967 rows=8,961 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: 10149
  • Heap Blocks: exact=12929
7. 8.466 8.466 ↑ 1.1 19,114 1

Bitmap Index Scan on companies_tsv_index (cost=0.00..239.06 rows=20,408 width=0) (actual time=8.465..8.466 rows=19,114 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. 19.004 7,044.173 ↑ 3.9 40 1

Sort (cost=218,627.73..218,628.12 rows=156 width=354) (actual time=7,044.165..7,044.173 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. 60.640 7,025.169 ↓ 250.5 39,082 1

Hash Left Join (cost=109,807.50..218,622.80 rows=156 width=354) (actual time=6,078.031..7,025.169 rows=39,082 loops=1)

  • Hash Cond: (companies.id = last_event_subquery.distinct_col)
10. 29.982 6,622.637 ↓ 250.5 39,082 1

Nested Loop (cost=8,312.88..117,126.20 rows=156 width=340) (actual time=5,736.120..6,622.637 rows=39,082 loops=1)

11. 276.311 6,501.333 ↓ 15.2 45,661 1

Hash Join (cost=8,312.45..115,598.19 rows=2,996 width=344) (actual time=5,735.255..6,501.333 rows=45,661 loops=1)

  • Hash Cond: (companies.id = companies_term.id)
12. 490.081 490.353 ↓ 2.0 962,769 1

Seq Scan on companies (cost=71.04..106,101.76 rows=478,108 width=340) (actual time=0.304..490.353 rows=962,769 loops=1)

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

SubPlan (forSeq Scan)

14. 0.272 0.272 ↓ 1.0 73 1

Seq Scan on hidden_globals (cost=0.00..70.86 rows=72 width=4) (actual time=0.005..0.272 rows=73 loops=1)

  • Filter: (organization_id = 7)
  • Rows Removed by Filter: 3624
15. 13.653 5,734.669 ↓ 4.1 45,687 1

Hash (cost=8,101.97..8,101.97 rows=11,155 width=4) (actual time=5,734.669..5,734.669 rows=45,687 loops=1)

  • Buckets: 65536 (originally 16384) Batches: 1 (originally 1) Memory Usage: 2119kB
16. 41.776 5,721.016 ↓ 4.1 45,687 1

HashAggregate (cost=7,878.87..7,990.42 rows=11,155 width=4) (actual time=5,708.006..5,721.016 rows=45,687 loops=1)

  • Group Key: companies_term.id
17. 26.291 5,679.240 ↓ 6.3 70,594 1

Append (cost=485.32..7,850.98 rows=11,155 width=4) (actual time=52.574..5,679.240 rows=70,594 loops=1)

18. 5.888 55.092 ↑ 1.2 8,973 1

HashAggregate (cost=485.32..594.94 rows=10,962 width=4) (actual time=52.574..55.092 rows=8,973 loops=1)

  • Group Key: companies_term.id
19. 3.441 49.204 ↑ 1.2 9,018 1

Append (cost=0.00..457.92 rows=10,962 width=4) (actual time=10.198..49.204 rows=9,018 loops=1)

20. 29.594 29.594 ↑ 1.2 8,961 1

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

21. 0.074 16.169 ↓ 57.0 57 1

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

22. 15.903 15.903 ↓ 1.7 48 1

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

23. 0.192 0.192 ↑ 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.004 rows=1 loops=48)

  • Index Cond: (person_id = persons_term.id)
  • Filter: (organization_id = 7)
24. 51.836 5,597.857 ↓ 319.3 61,621 1

Bitmap Heap Scan on entity_values (cost=6,256.39..7,034.86 rows=193 width=4) (actual time=5,550.493..5,597.857 rows=61,621 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_te
  • Heap Blocks: exact=27266
25. 5,546.021 5,546.021 ↓ 319.4 61,638 1

Bitmap Index Scan on entity_values_tsv_index (cost=0.00..6,256.34 rows=193 width=0) (actual time=5,546.021..5,546.021 rows=61,638 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(valu
26. 91.322 91.322 ↑ 1.0 1 45,661

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,661)

  • Index Cond: ((organization_id = 7) AND (company_id = companies.id))
  • Heap Fetches: 8
27. 11.633 341.892 ↓ 42.5 40,004 1

Hash (cost=101,482.86..101,482.86 rows=941 width=16) (actual time=341.892..341.892 rows=40,004 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1984kB
28. 16.632 330.259 ↓ 42.5 40,004 1

Subquery Scan on last_event_subquery (cost=101,468.75..101,482.86 rows=941 width=16) (actual time=262.704..330.259 rows=40,004 loops=1)

29. 31.043 313.627 ↓ 42.5 40,004 1

Unique (cost=101,468.75..101,473.45 rows=941 width=16) (actual time=262.702..313.627 rows=40,004 loops=1)

30. 64.423 282.584 ↓ 109.1 102,684 1

Sort (cost=101,468.75..101,471.10 rows=941 width=16) (actual time=262.700..282.584 rows=102,684 loops=1)

  • Sort Key: companies_persons_1.company_id, person_smart_attributes.last_event_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 7886kB
31. 93.632 218.161 ↓ 109.1 102,684 1

Hash Join (cost=95,409.26..101,422.27 rows=941 width=16) (actual time=124.698..218.161 rows=102,684 loops=1)

  • Hash Cond: (companies_persons_1.person_id = person_smart_attributes.person_id)
  • -> Index Only Scan using companies_persons_organization_id_company_id_person_id_index on companies_persons companies_persons_1 (cost=0.56..5742.16 rows=103397 width=8) (actual time=0.011..37.
  • Index Cond: (organization_id = 7)
  • Heap Fetches: 16859
32. 36.556 124.529 ↓ 1.1 115,943 1

Hash (cost=94,046.21..94,046.21 rows=108,999 width=16) (actual time=124.528..124.529 rows=115,943 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 5256kB
33. 77.894 87.973 ↓ 1.1 115,943 1

Bitmap Heap Scan on person_smart_attributes (cost=2,267.18..94,046.21 rows=108,999 width=16) (actual time=15.954..87.973 rows=115,943 loops=1)

  • Recheck Cond: (organization_id = 7)
  • Heap Blocks: exact=37443
34. 10.079 10.079 ↓ 1.1 120,324 1

Bitmap Index Scan on person_smart_attributes_temp_organization_id_person_id_idx1 (cost=0.00..2,239.93 rows=108,999 width=0) (actual time=10.079..10.079 rows=120,324 loops=1)

  • Index Cond: (organization_id = 7)