explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Q6p

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 1,047.545 ↑ 1.0 40 1

Limit (cost=240,265.78..240,265.88 rows=40 width=308) (actual time=1,047.519..1,047.545 rows=40 loops=1)

2.          

CTE persons_term

3. 0.102 4.291 ↓ 1.2 68 1

Bitmap Heap Scan on persons (cost=24.57..246.02 rows=55 width=4) (actual time=4.199..4.291 rows=68 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")) @@ '''capital'':*'::tsquery))
  • Heap Blocks: exact=67
4. 4.189 4.189 ↓ 1.2 68 1

Bitmap Index Scan on persons_new_tsv_index (cost=0.00..24.55 rows=55 width=0) (actual time=4.189..4.189 rows=68 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")) @@ '''capital'':*'::tsquery))
5.          

CTE companies_term

6. 16.159 21.796 ↓ 1.6 13,920 1

Bitmap Heap Scan on companies companies_1 (cost=141.09..28,541.49 rows=8,655 width=4) (actual time=7.084..21.796 rows=13,920 loops=1)

  • Recheck Cond: ((setweight(to_tsvector('simple'::regconfig, COALESCE(name, ''::text)), 'A'::"char") || setweight(to_tsvector('simple'::regconfig, COALESCE(domain, ''::text)), 'B'::"char")) @@ '''capital'':*'::tsquery)
  • Filter: ((organization_id = 7) OR (organization_id IS NULL))
  • Heap Blocks: exact=9805
7. 5.637 5.637 ↑ 1.1 13,920 1

Bitmap Index Scan on companies_tsv_index (cost=0.00..138.92 rows=15,057 width=0) (actual time=5.637..5.637 rows=13,920 loops=1)

  • Index Cond: ((setweight(to_tsvector('simple'::regconfig, COALESCE(name, ''::text)), 'A'::"char") || setweight(to_tsvector('simple'::regconfig, COALESCE(domain, ''::text)), 'B'::"char")) @@ '''capital'':*'::tsquery)
8.          

CTE dropdown_options_term

9. 0.001 0.051 ↓ 0.0 0 1

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

  • Recheck Cond: ((organization_id = 7) AND (to_tsvector('simple'::regconfig, COALESCE(text, ''::text)) @@ '''capital'':*'::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[])))
10. 0.001 0.050 ↓ 0.0 0 1

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

11. 0.049 0.049 ↓ 0.0 0 1

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

  • Index Cond: ((organization_id = 7) AND (to_tsvector('simple'::regconfig, COALESCE(text, ''::text)) @@ '''capital'':*'::tsquery))
12. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on dropdown_options_entity_attribute_id_index (cost=0.00..88.84 rows=197 width=0) (never executed)

  • 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[]))
13. 13.080 1,047.530 ↑ 666.4 40 1

Sort (cost=211,366.55..211,433.19 rows=26,657 width=308) (actual time=1,047.517..1,047.530 rows=40 loops=1)

  • Sort Key: person_smart_attributes.last_event_date DESC NULLS LAST, ((lower(companies.name) = 'capital'::text)) DESC NULLS LAST, ((lower(companies.domain) = 'capital'::text)) DESC NULLS LAST, companies.id
  • Sort Method: top-N heapsort Memory: 35kB
14. 40.359 1,034.450 ↑ 1.1 23,311 1

Hash Left Join (cost=144,817.79..210,523.93 rows=26,657 width=308) (actual time=770.604..1,034.450 rows=23,311 loops=1)

  • Hash Cond: (companies.id = companies_persons_1.company_id)
15. 23.761 697.783 ↑ 1.1 23,311 1

Hash Join (cost=46,461.32..111,800.77 rows=26,657 width=274) (actual time=474.263..697.783 rows=23,311 loops=1)

  • Hash Cond: (companies.id = organizations_relevant_companies.company_id)
16. 45.364 589.502 ↑ 12.6 34,863 1

Nested Loop (cost=30,666.63..94,094.45 rows=438,686 width=278) (actual time=389.593..589.502 rows=34,863 loops=1)

17. 29.061 404.666 ↓ 2.5 34,868 1

HashAggregate (cost=30,666.20..30,807.23 rows=14,103 width=4) (actual time=389.558..404.666 rows=34,868 loops=1)

  • Group Key: companies_term.id
18. 14.404 375.605 ↓ 2.6 36,509 1

Append (cost=527.98..30,630.95 rows=14,103 width=4) (actual time=45.398..375.605 rows=36,509 loops=1)

19. 10.072 49.812 ↓ 1.6 13,925 1

HashAggregate (cost=527.98..614.54 rows=8,656 width=4) (actual time=45.396..49.812 rows=13,925 loops=1)

  • Group Key: companies_term.id
20. 5.566 39.740 ↓ 1.6 13,987 1

Append (cost=0.00..506.33 rows=8,656 width=4) (actual time=7.088..39.740 rows=13,987 loops=1)

21. 29.484 29.484 ↓ 1.6 13,920 1

CTE Scan on companies_term (cost=0.00..173.10 rows=8,655 width=4) (actual time=7.088..29.484 rows=13,920 loops=1)

22. 0.090 4.690 ↓ 67.0 67 1

Nested Loop (cost=0.43..246.68 rows=1 width=4) (actual time=4.222..4.690 rows=67 loops=1)

23. 4.328 4.328 ↓ 1.2 68 1

CTE Scan on persons_term (cost=0.00..1.10 rows=55 width=4) (actual time=4.201..4.328 rows=68 loops=1)

24. 0.272 0.272 ↑ 1.0 1 68

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.004 rows=1 loops=68)

  • Index Cond: (person_id = persons_term.id)
  • Filter: (organization_id = 7)
25. 8.802 311.389 ↓ 4.1 22,584 1

Subquery Scan on t1 (cost=29,679.88..29,788.82 rows=5,447 width=4) (actual time=295.378..311.389 rows=22,584 loops=1)

26. 20.998 302.587 ↓ 4.1 22,584 1

HashAggregate (cost=29,679.88..29,734.35 rows=5,447 width=8) (actual time=295.378..302.587 rows=22,584 loops=1)

  • Group Key: entity_values.company_id, entity_values.list_entry_id
27. 12.854 281.589 ↓ 6.1 33,275 1

Append (cost=9,735.22..29,652.65 rows=5,447 width=8) (actual time=18.542..281.589 rows=33,275 loops=1)

28. 0.006 18.543 ↑ 12.7 3 1

HashAggregate (cost=9,735.22..9,735.60 rows=38 width=8) (actual time=18.542..18.543 rows=3 loops=1)

  • Group Key: entity_values.company_id, entity_values.list_entry_id
29. 0.002 18.537 ↑ 12.7 3 1

Append (cost=9,705.20..9,735.03 rows=38 width=8) (actual time=18.481..18.537 rows=3 loops=1)

30. 0.009 18.482 ↑ 12.3 3 1

HashAggregate (cost=9,705.20..9,705.57 rows=37 width=8) (actual time=18.480..18.482 rows=3 loops=1)

  • Group Key: entity_values.company_id, entity_values.list_entry_id
31. 0.001 18.473 ↑ 12.3 3 1

Append (cost=105.46..9,705.02 rows=37 width=8) (actual time=14.045..18.473 rows=3 loops=1)

32. 0.070 5.185 ↓ 0.0 0 1

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

33. 0.015 0.015 ↓ 1.2 68 1

CTE Scan on persons_term persons_term_1 (cost=0.00..1.10 rows=55 width=4) (actual time=0.001..0.015 rows=68 loops=1)

34. 0.000 5.100 ↓ 0.0 0 68

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

  • Recheck Cond: ((value_person_id = persons_term_1.id) AND (entity_attribute_id = 13499))
  • Filter: (organization_id = 7)
35. 0.092 5.100 ↓ 0.0 0 68

BitmapAnd (cost=105.46..105.46 rows=1 width=0) (actual time=0.075..0.075 rows=0 loops=68)

36. 0.136 0.136 ↓ 0.0 0 68

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=68)

  • Index Cond: (value_person_id = persons_term_1.id)
37. 4.872 4.872 ↑ 1.0 6,773 8

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.609..0.609 rows=6,773 loops=8)

  • Index Cond: (entity_attribute_id = 13499)
38. 2.312 13.287 ↑ 9.0 3 1

Merge Join (cost=3,743.53..3,791.62 rows=27 width=8) (actual time=8.859..13.287 rows=3 loops=1)

  • Merge Cond: (entity_values_1.value_company_id = companies_term_1.id)
39. 0.018 0.064 ↑ 65.0 14 1

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

  • Sort Key: entity_values_1.value_company_id
  • Sort Method: quicksort Memory: 25kB
40. 0.028 0.046 ↑ 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.027..0.046 rows=14 loops=1)

  • Recheck Cond: (entity_attribute_id = ANY ('{120416,77661,115418,118588}'::integer[]))
  • Filter: (organization_id = 7)
  • Heap Blocks: exact=12
41. 0.018 0.018 ↑ 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.018..0.018 rows=14 loops=1)

  • Index Cond: (entity_attribute_id = ANY ('{120416,77661,115418,118588}'::integer[]))
42. 7.596 10.911 ↓ 1.2 10,478 1

Sort (cost=739.11..760.74 rows=8,655 width=4) (actual time=8.496..10.911 rows=10,478 loops=1)

  • Sort Key: companies_term_1.id
  • Sort Method: quicksort Memory: 1037kB
43. 3.315 3.315 ↓ 1.6 13,920 1

CTE Scan on companies_term companies_term_1 (cost=0.00..173.10 rows=8,655 width=4) (actual time=0.000..3.315 rows=13,920 loops=1)

44. 0.001 0.053 ↓ 0.0 0 1

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

45. 0.052 0.052 ↓ 0.0 0 1

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

46. 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)
47. 0.000 0.000 ↓ 0.0 0

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

48. 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)
49. 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[]))
50. 26.723 250.192 ↓ 6.2 33,272 1

Bitmap Heap Scan on entity_values entity_values_3 (cost=912.97..19,862.20 rows=5,409 width=8) (actual time=225.090..250.192 rows=33,272 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))) @@ '''capital'':*'::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=8792
51. 223.469 223.469 ↓ 6.2 33,276 1

Bitmap Index Scan on entity_values_tsv_index (cost=0.00..911.62 rows=5,409 width=0) (actual time=223.469..223.469 rows=33,276 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))) @@ '''capital'':*'::tsquery))
52. 139.472 139.472 ↑ 1.0 1 34,868

Index Scan using companies_pkey on companies (cost=0.43..4.47 rows=1 width=274) (actual time=0.004..0.004 rows=1 loops=34,868)

  • Index Cond: (id = companies_term.id)
  • Filter: (((organization_id = 7) OR (organization_id IS NULL)) AND (id <> ALL ('{161800872,162557504,58730420,148821180,164006542,1590904,136380421,161850747,161878208,197473816,1780258,164006542,1630399,158344254,1553029,1733171,161818105,149474826,2167322,1514731,148821510,1536609,1527003,1666699,163278493,161870196,1630706,1584523,198234314,113832612,1586770,1706391}'::integer[])))
  • Rows Removed by Filter: 0
53. 42.081 84.520 ↑ 1.0 89,595 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 4174kB
54. 42.439 42.439 ↑ 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..42.439 rows=89,595 loops=1)

  • Index Cond: (organization_id = 7)
  • Heap Fetches: 89595
55. 10.277 296.308 ↓ 40.9 34,810 1

Hash (cost=98,345.83..98,345.83 rows=851 width=16) (actual time=296.308..296.308 rows=34,810 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1778kB
56. 27.807 286.031 ↓ 40.9 34,810 1

Unique (cost=98,333.07..98,337.32 rows=851 width=16) (actual time=235.226..286.031 rows=34,810 loops=1)

57. 66.905 258.224 ↓ 107.1 91,161 1

Sort (cost=98,333.07..98,335.20 rows=851 width=16) (actual time=235.223..258.224 rows=91,161 loops=1)

  • Sort Key: companies_persons_1.company_id, person_smart_attributes.last_event_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 7346kB
58. 61.022 191.319 ↓ 107.1 91,161 1

Hash Join (cost=50,643.16..98,291.66 rows=851 width=16) (actual time=91.658..191.319 rows=91,161 loops=1)

  • Hash Cond: (companies_persons_1.person_id = person_smart_attributes.person_id)
59. 41.305 49.315 ↓ 1.0 91,310 1

Bitmap Heap Scan on companies_persons companies_persons_1 (cost=1,411.08..48,709.14 rows=91,180 width=8) (actual time=10.214..49.315 rows=91,310 loops=1)

  • Recheck Cond: (organization_id = 7)
  • Heap Blocks: exact=13563
60. 8.010 8.010 ↓ 1.0 91,312 1

Bitmap Index Scan on companies_persons_organization_id_company_id_person_id_index (cost=0.00..1,388.28 rows=91,180 width=0) (actual time=8.010..8.010 rows=91,312 loops=1)

  • Index Cond: (organization_id = 7)
61. 31.911 80.982 ↓ 1.1 101,364 1

Hash (cost=48,033.74..48,033.74 rows=95,867 width=16) (actual time=80.982..80.982 rows=101,364 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 4714kB
62. 42.036 49.071 ↓ 1.1 101,364 1

Bitmap Heap Scan on person_smart_attributes (cost=1,269.40..48,033.74 rows=95,867 width=16) (actual time=8.498..49.071 rows=101,364 loops=1)

  • Recheck Cond: (organization_id = 7)
  • Heap Blocks: exact=9606
63. 7.035 7.035 ↓ 1.1 101,364 1

Bitmap Index Scan on person_smart_attributes_temp_organization_id_person_id_idx1 (cost=0.00..1,245.44 rows=95,867 width=0) (actual time=7.035..7.035 rows=101,364 loops=1)

  • Index Cond: (organization_id = 7)