explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KahA : prod normal

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

Limit (cost=265,036.39..265,036.49 rows=40 width=354) (actual time=6,986.707..6,986.746 rows=40 loops=1)

2.          

CTE persons_term

3. 0.064 16.062 ↓ 1.7 48 1

Bitmap Heap Scan on persons (cost=910.29..1,023.22 rows=28 width=4) (actual time=16.006..16.062 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.998 15.998 ↓ 1.8 49 1

Bitmap Index Scan on persons_new_tsv_index (cost=0.00..910.28 rows=28 width=0) (actual time=15.998..15.998 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.422 24.950 ↑ 1.2 8,961 1

Bitmap Heap Scan on companies companies_1 (cost=243.80..38,439.20 rows=10,961 width=4) (actual time=10.239..24.950 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.528 8.528 ↑ 1.1 19,114 1

Bitmap Index Scan on companies_tsv_index (cost=0.00..241.06 rows=20,408 width=0) (actual time=8.528..8.528 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.025 6,986.715 ↑ 3.9 40 1

Sort (cost=225,573.97..225,574.36 rows=156 width=354) (actual time=6,986.706..6,986.715 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.894 6,967.690 ↓ 250.5 39,082 1

Hash Left Join (cost=116,753.64..225,569.04 rows=156 width=354) (actual time=6,024.932..6,967.690 rows=39,082 loops=1)

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

Nested Loop (cost=9,186.88..118,000.30 rows=156 width=340) (actual time=5,686.556..6,568.477 rows=39,082 loops=1)

11. 276.377 6,447.239 ↓ 15.2 45,661 1

Hash Join (cost=9,186.45..116,472.20 rows=2,996 width=344) (actual time=5,685.676..6,447.239 rows=45,661 loops=1)

  • Hash Cond: (companies.id = companies_term.id)
12. 485.544 485.810 ↓ 2.0 962,809 1

Seq Scan on companies (cost=71.04..106,101.76 rows=478,108 width=340) (actual time=0.301..485.810 rows=962,809 loops=1)

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

SubPlan (forSeq Scan)

14. 0.266 0.266 ↓ 1.0 73 1

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

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

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

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

HashAggregate (cost=8,752.87..8,864.42 rows=11,155 width=4) (actual time=5,658.259..5,671.124 rows=45,687 loops=1)

  • Group Key: companies_term.id
17. 26.307 5,630.074 ↓ 6.3 70,594 1

Append (cost=485.32..8,724.98 rows=11,155 width=4) (actual time=52.787..5,630.074 rows=70,594 loops=1)

18. 5.940 55.311 ↑ 1.2 8,973 1

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

  • Group Key: companies_term.id
19. 3.417 49.371 ↑ 1.2 9,018 1

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

20. 29.605 29.605 ↑ 1.2 8,961 1

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

21. 0.069 16.349 ↓ 57.0 57 1

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

22. 16.088 16.088 ↓ 1.7 48 1

CTE Scan on persons_term (cost=0.00..0.56 rows=28 width=4) (actual time=16.007..16.088 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. 50.184 5,548.456 ↓ 319.3 61,621 1

Bitmap Heap Scan on entity_values (cost=7,130.39..7,908.86 rows=193 width=4) (actual time=5,502.693..5,548.456 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,498.272 5,498.272 ↓ 319.4 61,638 1

Bitmap Index Scan on entity_values_tsv_index (cost=0.00..7,130.34 rows=193 width=0) (actual time=5,498.272..5,498.272 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.499 338.319 ↓ 42.5 40,004 1

Hash (cost=107,555.00..107,555.00 rows=941 width=16) (actual time=338.319..338.319 rows=40,004 loops=1)

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

Subquery Scan on last_event_subquery (cost=107,540.89..107,555.00 rows=941 width=16) (actual time=258.704..326.820 rows=40,004 loops=1)

29. 31.368 310.057 ↓ 42.5 40,004 1

Unique (cost=107,540.89..107,545.59 rows=941 width=16) (actual time=258.702..310.057 rows=40,004 loops=1)

30. 64.167 278.689 ↓ 109.1 102,684 1

Sort (cost=107,540.89..107,543.24 rows=941 width=16) (actual time=258.700..278.689 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.153 214.522 ↓ 109.1 102,684 1

Hash Join (cost=101,481.40..107,494.41 rows=941 width=16) (actual time=121.540..214.522 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.020..37.
  • Index Cond: (organization_id = 7)
  • Heap Fetches: 16859
32. 35.789 121.369 ↑ 1.1 115,944 1

Hash (cost=99,922.04..99,922.04 rows=124,704 width=16) (actual time=121.369..121.369 rows=115,944 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 5256kB
33. 75.601 85.580 ↑ 1.1 115,944 1

Bitmap Heap Scan on person_smart_attributes (cost=2,388.89..99,922.04 rows=124,704 width=16) (actual time=15.645..85.580 rows=115,944 loops=1)

  • Recheck Cond: (organization_id = 7)
  • Heap Blocks: exact=36159
34. 9.979 9.979 ↑ 1.1 116,998 1

Bitmap Index Scan on person_smart_attributes_temp_organization_id_person_id_idx1 (cost=0.00..2,357.71 rows=124,704 width=0) (actual time=9.979..9.979 rows=116,998 loops=1)

  • Index Cond: (organization_id = 7)