explain.depesz.com

PostgreSQL's explain analyze made readable

Result: a1z : prod

Settings
# exclusive inclusive rows x rows loops node
1. 0.044 15,756.634 ↑ 1.0 40 1

Limit (cost=222,802.58..222,802.68 rows=40 width=374) (actual time=15,756.583..15,756.634 rows=40 loops=1)

2.          

CTE persons_term

3. 0.111 16.477 ↓ 3.0 84 1

Bitmap Heap Scan on persons (cost=878.29..991.22 rows=28 width=4) (actual time=16.377..16.477 rows=84 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=84
4. 16.366 16.366 ↓ 3.1 88 1

Bitmap Index Scan on persons_new_tsv_index (cost=0.00..878.28 rows=28 width=0) (actual time=16.366..16.366 rows=88 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. 24.633 38.000 ↑ 1.0 15,158 1

Bitmap Heap Scan on companies companies_1 (cost=312.76..50,758.23 rows=15,531 width=4) (actual time=16.009..38.000 rows=15,158 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))
  • Rows Removed by Filter: 14589
  • Heap Blocks: exact=18055
7. 13.367 13.367 ↓ 1.0 29,762 1

Bitmap Index Scan on companies_tsv_index (cost=0.00..308.88 rows=28,917 width=0) (actual time=13.367..13.367 rows=29,762 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 1.881 ↓ 0.0 0 1

Bitmap Heap Scan on dropdown_options (cost=26.12..52.63 rows=1 width=4) (actual time=1.881..1.881 rows=0 loops=1)

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

Bitmap Index Scan on dropdown_options_tsv_index (cost=0.00..26.12 rows=12 width=0) (actual time=1.880..1.880 rows=0 loops=1)

  • Index Cond: ((organization_id = 7) AND (to_tsvector('simple'::regconfig, COALESCE(text, ''::text)) @@ '''capital'':*'::tsquery))
11. 12.691 15,756.590 ↑ 11.0 40 1

Sort (cost=171,000.50..171,001.60 rows=440 width=374) (actual time=15,756.582..15,756.590 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: 38kB
12. 35.725 15,743.899 ↓ 55.2 24,273 1

Hash Left Join (cost=145,275.69..170,986.59 rows=440 width=374) (actual time=15,564.638..15,743.899 rows=24,273 loops=1)

  • Hash Cond: (companies.id = companies_persons_1.company_id)
13. 10.803 15,385.415 ↓ 55.2 24,273 1

Nested Loop (cost=43,781.06..69,486.41 rows=440 width=340) (actual time=15,241.861..15,385.415 rows=24,273 loops=1)

14. 60.013 15,301.775 ↓ 2.1 24,279 1

Hash Join (cost=43,780.63..46,353.55 rows=11,740 width=8) (actual time=15,241.845..15,301.775 rows=24,279 loops=1)

  • Hash Cond: (organizations_relevant_companies.company_id = companies_term.id)
  • -> Index Only Scan using organizations_relevant_companies_organization_id_company_id_idx on organizations_relevant_companies (cost=0.43..2330.42 rows=92537 width=4) (actual time=0.014..24.647 rows=94222 loops=
  • Index Cond: (organization_id = 7)
  • Heap Fetches: 36
15. 11.066 15,241.762 ↓ 2.3 36,585 1

Hash (cost=43,583.09..43,583.09 rows=15,769 width=4) (actual time=15,241.762..15,241.762 rows=36,585 loops=1)

  • Buckets: 65536 (originally 16384) Batches: 1 (originally 1) Memory Usage: 1799kB
16. 25.427 15,230.696 ↓ 2.3 36,585 1

HashAggregate (cost=43,267.71..43,425.40 rows=15,769 width=4) (actual time=15,220.234..15,230.696 rows=36,585 loops=1)

  • Group Key: companies_term.id
17. 14.290 15,205.269 ↓ 2.4 38,551 1

Append (cost=633.85..43,228.29 rows=15,769 width=4) (actual time=74.216..15,205.269 rows=38,551 loops=1)

18. 9.936 78.512 ↑ 1.0 15,163 1

HashAggregate (cost=633.85..789.17 rows=15,532 width=4) (actual time=74.215..78.512 rows=15,163 loops=1)

  • Group Key: companies_term.id
19. 5.691 68.576 ↑ 1.0 15,238 1

Append (cost=0.00..595.02 rows=15,532 width=4) (actual time=16.012..68.576 rows=15,238 loops=1)

20. 45.780 45.780 ↑ 1.0 15,158 1

CTE Scan on companies_term (cost=0.00..310.62 rows=15,531 width=4) (actual time=16.011..45.780 rows=15,158 loops=1)

21. 0.076 17.105 ↓ 80.0 80 1

Nested Loop (cost=0.56..129.08 rows=1 width=4) (actual time=16.405..17.105 rows=80 loops=1)

22. 16.525 16.525 ↓ 3.0 84 1

CTE Scan on persons_term (cost=0.00..0.56 rows=28 width=4) (actual time=16.380..16.525 rows=84 loops=1)

23. 0.504 0.504 ↑ 1.0 1 84

Index Scan using companies_persons_person_id_index on companies_persons (cost=0.56..4.58 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=84)

  • Index Cond: (person_id = persons_term.id)
  • Filter: (organization_id = 7)
24. 9.014 15,112.467 ↓ 98.7 23,388 1

Subquery Scan on t1 (cost=42,121.37..42,126.11 rows=237 width=4) (actual time=15,095.700..15,112.467 rows=23,388 loops=1)

25. 28.988 15,103.453 ↓ 98.7 23,388 1

HashAggregate (cost=42,121.37..42,123.74 rows=237 width=8) (actual time=15,095.698..15,103.453 rows=23,388 loops=1)

  • Group Key: entity_values.company_id, entity_values.list_entry_id
26. 15.699 15,074.465 ↓ 145.1 34,381 1

Append (cost=37,069.33..42,120.18 rows=237 width=8) (actual time=2,689.256..15,074.465 rows=34,381 loops=1)

27. 0.009 2,689.256 ↑ 7.0 3 1

HashAggregate (cost=37,069.33..37,069.54 rows=21 width=8) (actual time=2,689.255..2,689.256 rows=3 loops=1)

  • Group Key: entity_values.company_id, entity_values.list_entry_id
28. 0.003 2,689.247 ↑ 7.0 3 1

Append (cost=36,645.23..37,069.22 rows=21 width=8) (actual time=2,687.359..2,689.247 rows=3 loops=1)

29. 0.011 2,687.360 ↑ 6.7 3 1

HashAggregate (cost=36,645.23..36,645.43 rows=20 width=8) (actual time=2,687.359..2,687.360 rows=3 loops=1)

  • Group Key: entity_values.company_id, entity_values.list_entry_id
30. 0.003 2,687.349 ↑ 6.7 3 1

Append (cost=169.30..36,645.13 rows=20 width=8) (actual time=54.947..2,687.349 rows=3 loops=1)

31. 0.173 68.940 ↓ 2.0 2 1

Nested Loop (cost=169.30..4,797.62 rows=1 width=8) (actual time=54.946..68.940 rows=2 loops=1)

32. 0.055 0.055 ↓ 3.0 84 1

CTE Scan on persons_term persons_term_1 (cost=0.00..0.56 rows=28 width=4) (actual time=0.001..0.055 rows=84 loops=1)

33. 0.504 68.712 ↓ 0.0 0 84

Bitmap Heap Scan on entity_values (cost=169.30..171.31 rows=1 width=12) (actual time=0.818..0.818 rows=0 loops=84)

  • Recheck Cond: ((value_person_id = persons_term_1.id) AND (entity_attribute_id = 13499))
  • Filter: (organization_id = 7)
  • Heap Blocks: exact=2
34. 68.208 68.208 ↓ 0.0 0 84

BitmapAnd (cost=169.30..169.30 rows=1 width=0) (actual time=0.812..0.812 rows=0 loops=84)

  • -> Bitmap Index Scan on entity_values_value_person_id_index (cost=0.00..38.56 rows=1066 width=0) (actual time=0.465..0.465 rows=0 l
  • Index Cond: (value_person_id = persons_term_1.id)
  • -> Bitmap Index Scan on entity_values_entity_attribute_id_company_id_index (cost=0.00..130.45 rows=6385 width=0) (actual time=2.641
  • Index Cond: (entity_attribute_id = 13499)
35. 0.041 2,618.406 ↑ 19.0 1 1

Hash Join (cost=30,070.22..31,847.32 rows=19 width=8) (actual time=2,618.396..2,618.406 rows=1 loops=1)

  • Hash Cond: (entity_values_1.value_company_id = companies_term_1.id)
  • -> Bitmap Heap Scan on entity_values entity_values_1 (cost=29565.46..30465.74 rows=451 width=12) (actual time=2611.148..2611.156 rows=2 loops=1
  • Recheck Cond: ((entity_attribute_id = ANY ('{120416,77661,115418,118588}'::integer[])) AND (organization_id = 7))
  • Heap Blocks: exact=2
36. 2,611.134 2,611.134 ↓ 0.0 0 1

BitmapAnd (cost=29,565.46..29,565.46 rows=451 width=0) (actual time=2,611.133..2,611.134 rows=0 loops=1)

  • -> Bitmap Index Scan on entity_values_entity_attribute_id_company_id_index (cost=0.00..521.82 rows=25540 width=0) (actual time=7.33
  • Index Cond: (entity_attribute_id = ANY ('{120416,77661,115418,118588}'::integer[]))
  • -> Bitmap Index Scan on entity_values_organization_id_company_id_index (cost=0.00..29043.17 rows=1447813 width=0) (actual time=2600
  • Index Cond: (organization_id = 7)
37. 3.719 7.231 ↑ 1.0 15,158 1

Hash (cost=310.62..310.62 rows=15,531 width=4) (actual time=7.231..7.231 rows=15,158 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 661kB
38. 3.512 3.512 ↑ 1.0 15,158 1

CTE Scan on companies_term companies_term_1 (cost=0.00..310.62 rows=15,531 width=4) (actual time=0.001..3.512 rows=15,158 loops=1)

39. 0.001 1.884 ↓ 0.0 0 1

Nested Loop (cost=421.33..423.38 rows=1 width=8) (actual time=1.884..1.884 rows=0 loops=1)

40. 1.883 1.883 ↓ 0.0 0 1

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

41. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on entity_values entity_values_2 (cost=421.33..423.35 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)
42. 0.000 0.000 ↓ 0.0 0

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

43. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on entity_values_value_dropdown_option_id_index (cost=0.00..29.63 rows=1,760 width=0) (never executed)

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

Bitmap Index Scan on entity_values_entity_attribute_id_company_id_index (cost=0.00..391.37 rows=19,155 width=0) (never executed)

  • Index Cond: (entity_attribute_id = ANY ('{105482,108622,117170}'::integer[]))
45. 2,235.551 12,369.510 ↓ 159.2 34,378 1

Bitmap Heap Scan on entity_values entity_values_3 (cost=4,172.68..5,048.07 rows=216 width=8) (actual time=10,136.957..12,369.510 rows=34,378 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,
  • Heap Blocks: exact=20213
46. 10,133.959 10,133.959 ↓ 162.5 35,097 1

Bitmap Index Scan on entity_values_tsv_index (cost=0.00..4,172.62 rows=216 width=0) (actual time=10,133.959..10,133.959 rows=35,097 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,115
47. 72.837 72.837 ↑ 1.0 1 24,279

Index Scan using companies_pkey on companies (cost=0.43..1.97 rows=1 width=340) (actual time=0.003..0.003 rows=1 loops=24,279)

  • Index Cond: (id = organizations_relevant_companies.company_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,15834425
  • Rows Removed by Filter: 0
48. 10.930 322.759 ↓ 42.5 40,004 1

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

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1968kB
49. 31.189 311.829 ↓ 42.5 40,004 1

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

50. 64.096 280.640 ↓ 109.1 102,684 1

Sort (cost=101,468.75..101,471.10 rows=941 width=16) (actual time=261.107..280.640 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
51. 93.918 216.544 ↓ 109.1 102,684 1

Hash Join (cost=95,409.26..101,422.27 rows=941 width=16) (actual time=122.775..216.544 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.012..37.167 ro
  • Index Cond: (organization_id = 7)
  • Heap Fetches: 16859
52. 36.253 122.626 ↓ 1.1 115,943 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 5256kB
53. 76.408 86.373 ↓ 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.669..86.373 rows=115,943 loops=1)

  • Recheck Cond: (organization_id = 7)
  • Heap Blocks: exact=35823
54. 9.965 9.965 ↓ 1.1 115,943 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=9.965..9.965 rows=115,943 loops=1)

  • Index Cond: (organization_id = 7)