explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OYP : fast count

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 844.915 ↑ 1.0 1 1

Limit (cost=121,605.44..121,605.45 rows=1 width=8) (actual time=844.914..844.915 rows=1 loops=1)

2. 9.474 844.914 ↑ 1.0 1 1

Aggregate (cost=121,605.44..121,605.45 rows=1 width=8) (actual time=844.914..844.914 rows=1 loops=1)

3. 34.321 835.440 ↓ 1.4 38,367 1

Hash Join (cost=67,778.77..121,272.21 rows=26,658 width=249) (actual time=593.193..835.440 rows=38,367 loops=1)

  • Hash Cond: (companies.id = organizations_relevant_companies.company_id)
4.          

CTE persons_term

5. 0.074 4.216 ↑ 1.2 44 1

Bitmap Heap Scan on persons (cost=24.57..246.02 rows=55 width=4) (actual time=4.150..4.216 rows=44 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=44
6. 4.142 4.142 ↑ 1.2 44 1

Bitmap Index Scan on persons_new_tsv_index (cost=0.00..24.55 rows=55 width=0) (actual time=4.142..4.142 rows=44 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))
7.          

CTE companies_term

8. 10.587 14.640 ↓ 1.4 8,451 1

Bitmap Heap Scan on companies companies_1 (cost=103.53..21,368.04 rows=6,132 width=4) (actual time=5.042..14.640 rows=8,451 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))
  • Heap Blocks: exact=6757
9. 4.053 4.053 ↑ 1.3 8,451 1

Bitmap Index Scan on companies_tsv_index (cost=0.00..102.00 rows=10,667 width=0) (actual time=4.053..4.053 rows=8,451 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)
10.          

CTE dropdown_options_term

11. 0.002 0.138 ↓ 0.0 0 1

Bitmap Heap Scan on dropdown_options (cost=109.40..111.72 rows=1 width=4) (actual time=0.138..0.138 rows=0 loops=1)

  • Recheck Cond: ((organization_id = 7) AND (to_tsvector('simple'::regconfig, COALESCE(text, ''::text)) @@ '''venture'':*'::tsquery) AND (entity_attribute_id = ANY ('{37445,1286,291,338,1287,2988,2989,1285,1288,130660,120416,73481,60778,10900,13499,1366,77661,105061,105482,107467,107738,108622,113138,114986,115312,115418,116564,117170,118588,118623,121602,123265,123266,123316,125445,129018,129935}'::integer[])))
12. 0.002 0.136 ↓ 0.0 0 1

BitmapAnd (cost=109.40..109.40 rows=1 width=0) (actual time=0.136..0.136 rows=0 loops=1)

13. 0.055 0.055 ↑ 232.0 1 1

Bitmap Index Scan on dropdown_options_tsv_index (cost=0.00..20.32 rows=232 width=0) (actual time=0.055..0.055 rows=1 loops=1)

  • Index Cond: ((organization_id = 7) AND (to_tsvector('simple'::regconfig, COALESCE(text, ''::text)) @@ '''venture'':*'::tsquery))
14. 0.079 0.079 ↑ 19.7 10 1

Bitmap Index Scan on dropdown_options_entity_attribute_id_index (cost=0.00..88.84 rows=197 width=0) (actual time=0.079..0.079 rows=10 loops=1)

  • Index Cond: (entity_attribute_id = ANY ('{37445,1286,291,338,1287,2988,2989,1285,1288,130660,120416,73481,60778,10900,13499,1366,77661,105061,105482,107467,107738,108622,113138,114986,115312,115418,116564,117170,118588,118623,121602,123265,123266,123316,125445,129018,129935}'::integer[]))
15. 55.740 743.027 ↑ 9.8 44,749 1

Nested Loop (cost=30,258.30..81,840.07 rows=438,695 width=8) (actual time=534.938..743.027 rows=44,749 loops=1)

16. 36.303 553.040 ↓ 3.9 44,749 1

HashAggregate (cost=30,257.88..30,373.60 rows=11,572 width=4) (actual time=534.906..553.040 rows=44,749 loops=1)

  • Group Key: companies_term.id
17. 17.971 516.737 ↓ 4.0 46,574 1

Append (cost=445.98..30,228.95 rows=11,572 width=4) (actual time=30.463..516.737 rows=46,574 loops=1)

18. 5.882 32.945 ↓ 1.4 8,463 1

HashAggregate (cost=445.98..507.31 rows=6,133 width=4) (actual time=30.463..32.945 rows=8,463 loops=1)

  • Group Key: companies_term.id
19. 3.273 27.063 ↓ 1.4 8,508 1

Append (cost=0.00..430.64 rows=6,133 width=4) (actual time=5.046..27.063 rows=8,508 loops=1)

20. 19.285 19.285 ↓ 1.4 8,451 1

CTE Scan on companies_term (cost=0.00..122.64 rows=6,132 width=4) (actual time=5.045..19.285 rows=8,451 loops=1)

21. 0.048 4.505 ↓ 57.0 57 1

Nested Loop (cost=0.43..246.68 rows=1 width=4) (actual time=4.171..4.505 rows=57 loops=1)

22. 4.237 4.237 ↑ 1.2 44 1

CTE Scan on persons_term (cost=0.00..1.10 rows=55 width=4) (actual time=4.151..4.237 rows=44 loops=1)

23. 0.220 0.220 ↑ 1.0 1 44

Index Scan using companies_persons_person_id_index on companies_persons (cost=0.43..4.46 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=44)

  • Index Cond: (person_id = persons_term.id)
  • Filter: (organization_id = 7)
24. 15.278 465.821 ↓ 7.0 38,111 1

Subquery Scan on t1 (cost=29,435.81..29,544.59 rows=5,439 width=4) (actual time=438.905..465.821 rows=38,111 loops=1)

25. 37.176 450.543 ↓ 7.0 38,111 1

HashAggregate (cost=29,435.81..29,490.20 rows=5,439 width=8) (actual time=438.903..450.543 rows=38,111 loops=1)

  • Group Key: entity_values.company_id, entity_values.list_entry_id
26. 23.007 413.367 ↓ 11.1 60,407 1

Append (cost=9,491.42..29,408.61 rows=5,439 width=8) (actual time=10.733..413.367 rows=60,407 loops=1)

27. 0.004 10.733 ↑ 5.0 6 1

HashAggregate (cost=9,491.42..9,491.72 rows=30 width=8) (actual time=10.733..10.733 rows=6 loops=1)

  • Group Key: entity_values.company_id, entity_values.list_entry_id
28. 0.003 10.729 ↑ 5.0 6 1

Append (cost=9,461.69..9,491.27 rows=30 width=8) (actual time=10.583..10.729 rows=6 loops=1)

29. 0.012 10.587 ↑ 4.8 6 1

HashAggregate (cost=9,461.69..9,461.98 rows=29 width=8) (actual time=10.583..10.587 rows=6 loops=1)

  • Group Key: entity_values.company_id, entity_values.list_entry_id
30. 0.004 10.575 ↑ 4.8 6 1

Append (cost=105.46..9,461.54 rows=29 width=8) (actual time=2.026..10.575 rows=6 loops=1)

31. 0.043 2.120 ↑ 10.0 1 1

Nested Loop (cost=105.46..5,913.02 rows=10 width=8) (actual time=2.025..2.120 rows=1 loops=1)

32. 0.009 0.009 ↑ 1.2 44 1

CTE Scan on persons_term persons_term_1 (cost=0.00..1.10 rows=55 width=4) (actual time=0.000..0.009 rows=44 loops=1)

33. 0.044 2.068 ↓ 0.0 0 44

Bitmap Heap Scan on entity_values (cost=105.46..107.48 rows=1 width=12) (actual time=0.047..0.047 rows=0 loops=44)

  • Recheck Cond: ((value_person_id = persons_term_1.id) AND (entity_attribute_id = 13499))
  • Filter: (organization_id = 7)
  • Heap Blocks: exact=1
34. 0.016 2.024 ↓ 0.0 0 44

BitmapAnd (cost=105.46..105.46 rows=1 width=0) (actual time=0.046..0.046 rows=0 loops=44)

35. 0.088 0.088 ↓ 0.0 0 44

Bitmap Index Scan on entity_values_value_person_id_index (cost=0.00..3.26 rows=111 width=0) (actual time=0.002..0.002 rows=0 loops=44)

  • Index Cond: (value_person_id = persons_term_1.id)
36. 1.920 1.920 ↑ 1.0 6,773 3

Bitmap Index Scan on entity_values_entity_attribute_id_company_id_index (cost=0.00..101.01 rows=7,011 width=0) (actual time=0.640..0.640 rows=6,773 loops=3)

  • Index Cond: (entity_attribute_id = 13499)
37. 1.618 8.451 ↑ 3.8 5 1

Merge Join (cost=3,512.83..3,548.23 rows=19 width=8) (actual time=5.159..8.451 rows=5 loops=1)

  • Merge Cond: (entity_values_1.value_company_id = companies_term_1.id)
38. 0.016 0.059 ↑ 65.0 14 1

Sort (cost=3,004.42..3,006.70 rows=910 width=12) (actual time=0.057..0.059 rows=14 loops=1)

  • Sort Key: entity_values_1.value_company_id
  • Sort Method: quicksort Memory: 25kB
39. 0.024 0.043 ↑ 65.0 14 1

Bitmap Heap Scan on entity_values entity_values_1 (cost=30.73..2,959.70 rows=910 width=12) (actual time=0.024..0.043 rows=14 loops=1)

  • Recheck Cond: (entity_attribute_id = ANY ('{120416,77661,115418,118588}'::integer[]))
  • Filter: (organization_id = 7)
  • Heap Blocks: exact=12
40. 0.019 0.019 ↑ 121.6 14 1

Bitmap Index Scan on entity_values_entity_attribute_id_company_id_index (cost=0.00..30.50 rows=1,703 width=0) (actual time=0.019..0.019 rows=14 loops=1)

  • Index Cond: (entity_attribute_id = ANY ('{120416,77661,115418,118588}'::integer[]))
41. 4.822 6.774 ↓ 1.2 7,330 1

Sort (cost=508.41..523.74 rows=6,132 width=4) (actual time=5.095..6.774 rows=7,330 loops=1)

  • Sort Key: companies_term_1.id
  • Sort Method: quicksort Memory: 781kB
42. 1.952 1.952 ↓ 1.4 8,451 1

CTE Scan on companies_term companies_term_1 (cost=0.00..122.64 rows=6,132 width=4) (actual time=0.001..1.952 rows=8,451 loops=1)

43. 0.001 0.139 ↓ 0.0 0 1

Nested Loop (cost=26.66..28.71 rows=1 width=8) (actual time=0.139..0.139 rows=0 loops=1)

44. 0.138 0.138 ↓ 0.0 0 1

CTE Scan on dropdown_options_term (cost=0.00..0.02 rows=1 width=4) (actual time=0.138..0.138 rows=0 loops=1)

45. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on entity_values entity_values_2 (cost=26.66..28.68 rows=1 width=12) (never executed)

  • Recheck Cond: ((value_dropdown_option_id = dropdown_options_term.id) AND (entity_attribute_id = ANY ('{105482,108622,117170}'::integer[])))
  • Filter: (organization_id = 7)
46. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=26.66..26.66 rows=1 width=0) (never executed)

47. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on entity_values_value_dropdown_option_id_index (cost=0.00..3.36 rows=143 width=0) (never executed)

  • Index Cond: (value_dropdown_option_id = dropdown_options_term.id)
48. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on entity_values_entity_attribute_id_company_id_index (cost=0.00..22.88 rows=1,278 width=0) (never executed)

  • Index Cond: (entity_attribute_id = ANY ('{105482,108622,117170}'::integer[]))
49. 43.105 379.627 ↓ 11.2 60,401 1

Bitmap Heap Scan on entity_values entity_values_3 (cost=912.97..19,862.20 rows=5,409 width=8) (actual time=338.862..379.627 rows=60,401 loops=1)

  • Recheck Cond: ((organization_id = 7) AND (entity_attribute_id = ANY ('{37445,1286,291,338,1287,2989,1285,1288,73481,10900,1366,105061,107467,107738,113138,114986,115312,116564,118623,121602,123316,125445,129018,129935}'::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=12040
50. 336.522 336.522 ↓ 11.2 60,402 1

Bitmap Index Scan on entity_values_tsv_index (cost=0.00..911.62 rows=5,409 width=0) (actual time=336.522..336.522 rows=60,402 loops=1)

  • Index Cond: ((organization_id = 7) AND (entity_attribute_id = ANY ('{37445,1286,291,338,1287,2989,1285,1288,73481,10900,1366,105061,107467,107738,113138,114986,115312,116564,118623,121602,123316,125445,129018,129935}'::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))
51. 134.247 134.247 ↑ 1.0 1 44,749

Index Scan using companies_pkey on companies (cost=0.43..4.43 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=44,749)

  • Index Cond: (id = companies_term.id)
  • Filter: ((organization_id = 7) OR (organization_id IS NULL))
52. 27.116 58.092 ↑ 1.0 89,595 1

Hash (cost=14,635.36..14,635.36 rows=92,746 width=4) (actual time=58.092..58.092 rows=89,595 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 4174kB
53. 30.976 30.976 ↑ 1.0 89,595 1

Index Only Scan using organizations_relevant_companies_organization_id_company_id_idx on organizations_relevant_companies (cost=0.43..14,635.36 rows=92,746 width=4) (actual time=0.018..30.976 rows=89,595 loops=1)

  • Index Cond: (organization_id = 7)
  • Heap Fetches: 89595