explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Rmkc : prod 2

Settings
# exclusive inclusive rows x rows loops node
1. 0.045 10,868.174 ↑ 1.0 40 1

Limit (cost=223,786.79..223,786.89 rows=40 width=374) (actual time=10,868.122..10,868.174 rows=40 loops=1)

2.          

CTE persons_term

3. 0.108 16.420 ↓ 3.0 84 1

Bitmap Heap Scan on persons (cost=884.29..997.22 rows=28 width=4) (actual time=16.323..16.420 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.312 16.312 ↓ 3.1 88 1

Bitmap Index Scan on persons_new_tsv_index (cost=0.00..884.28 rows=28 width=0) (actual time=16.311..16.312 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.782 38.067 ↑ 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=15.917..38.067 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.285 13.285 ↓ 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.284..13.285 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.002 0.227 ↓ 0.0 0 1

Bitmap Heap Scan on dropdown_options (cost=26.12..52.63 rows=1 width=4) (actual time=0.226..0.227 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. 0.225 0.225 ↓ 0.0 0 1

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

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

Sort (cost=171,978.71..171,979.81 rows=440 width=374) (actual time=10,868.121..10,868.129 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.970 10,855.303 ↓ 55.2 24,273 1

Hash Left Join (cost=146,253.90..171,964.80 rows=440 width=374) (actual time=10,678.107..10,855.303 rows=24,273 loops=1)

  • Hash Cond: (companies.id = companies_persons_1.company_id)
13. 32.654 10,495.995 ↓ 55.2 24,273 1

Nested Loop (cost=44,759.27..70,464.62 rows=440 width=340) (actual time=10,354.749..10,495.995 rows=24,273 loops=1)

14. 60.133 10,414.783 ↓ 2.1 24,279 1

Hash Join (cost=44,758.84..47,331.76 rows=11,740 width=8) (actual time=10,354.734..10,414.783 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.015..24.784 rows=94222 loops=
  • Index Cond: (organization_id = 7)
  • Heap Fetches: 36
15. 10.945 10,354.650 ↓ 2.3 36,585 1

Hash (cost=44,561.30..44,561.30 rows=15,769 width=4) (actual time=10,354.649..10,354.650 rows=36,585 loops=1)

  • Buckets: 65536 (originally 16384) Batches: 1 (originally 1) Memory Usage: 1799kB
16. 25.295 10,343.705 ↓ 2.3 36,585 1

HashAggregate (cost=44,245.92..44,403.61 rows=15,769 width=4) (actual time=10,333.183..10,343.705 rows=36,585 loops=1)

  • Group Key: companies_term.id
17. 14.154 10,318.410 ↓ 2.4 38,551 1

Append (cost=633.85..44,206.50 rows=15,769 width=4) (actual time=74.289..10,318.410 rows=38,551 loops=1)

18. 10.206 78.694 ↑ 1.0 15,163 1

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

  • Group Key: companies_term.id
19. 5.740 68.488 ↑ 1.0 15,238 1

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

20. 45.850 45.850 ↑ 1.0 15,158 1

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

21. 0.092 16.898 ↓ 80.0 80 1

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

22. 16.470 16.470 ↓ 3.0 84 1

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

23. 0.336 0.336 ↑ 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.004..0.004 rows=1 loops=84)

  • Index Cond: (person_id = persons_term.id)
  • Filter: (organization_id = 7)
24. 8.988 10,225.562 ↓ 98.7 23,388 1

Subquery Scan on t1 (cost=43,099.58..43,104.32 rows=237 width=4) (actual time=10,209.435..10,225.562 rows=23,388 loops=1)

25. 21.380 10,216.574 ↓ 98.7 23,388 1

HashAggregate (cost=43,099.58..43,101.95 rows=237 width=8) (actual time=10,209.433..10,216.574 rows=23,388 loops=1)

  • Group Key: entity_values.company_id, entity_values.list_entry_id
26. 12.917 10,195.194 ↓ 145.1 34,381 1

Append (cost=37,069.54..43,098.39 rows=237 width=8) (actual time=154.216..10,195.194 rows=34,381 loops=1)

27. 0.009 154.216 ↑ 7.0 3 1

HashAggregate (cost=37,069.54..37,069.75 rows=21 width=8) (actual time=154.215..154.216 rows=3 loops=1)

  • Group Key: entity_values.company_id, entity_values.list_entry_id
28. 0.004 154.207 ↑ 7.0 3 1

Append (cost=36,645.44..37,069.43 rows=21 width=8) (actual time=153.970..154.207 rows=3 loops=1)

29. 0.013 153.973 ↑ 6.7 3 1

HashAggregate (cost=36,645.44..36,645.64 rows=20 width=8) (actual time=153.968..153.973 rows=3 loops=1)

  • Group Key: entity_values.company_id, entity_values.list_entry_id
30. 0.002 153.960 ↑ 6.7 3 1

Append (cost=169.30..36,645.34 rows=20 width=8) (actual time=6.221..153.960 rows=3 loops=1)

31. 0.062 9.579 ↓ 2.0 2 1

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

32. 0.025 0.025 ↓ 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.025 rows=84 loops=1)

33. 0.084 9.492 ↓ 0.0 0 84

Bitmap Heap Scan on entity_values (cost=169.30..171.31 rows=1 width=12) (actual time=0.113..0.113 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. 9.408 9.408 ↓ 0.0 0 84

BitmapAnd (cost=169.30..169.30 rows=1 width=0) (actual time=0.112..0.112 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.003..0.003 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=0.831
  • Index Cond: (entity_attribute_id = 13499)
35. 0.019 144.379 ↑ 19.0 1 1

Hash Join (cost=30,070.43..31,847.53 rows=19 width=8) (actual time=144.373..144.379 rows=1 loops=1)

  • Hash Cond: (entity_values_1.value_company_id = companies_term_1.id)
36. 0.012 137.290 ↑ 225.5 2 1

Bitmap Heap Scan on entity_values entity_values_1 (cost=29,565.67..30,465.95 rows=451 width=12) (actual time=137.287..137.290 rows=2 loops=1)

  • Recheck Cond: ((entity_attribute_id = ANY ('{120416,77661,115418,118588}'::integer[])) AND (organization_id = 7))
  • Heap Blocks: exact=2
37. 137.278 137.278 ↓ 0.0 0 1

BitmapAnd (cost=29,565.67..29,565.67 rows=451 width=0) (actual time=137.278..137.278 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=0.02
  • 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.38 rows=1447841 width=0) (actual time=134.
  • Index Cond: (organization_id = 7)
38. 3.675 7.070 ↑ 1.0 15,158 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 661kB
39. 3.395 3.395 ↑ 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.395 rows=15,158 loops=1)

40. 0.001 0.230 ↓ 0.0 0 1

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

41. 0.229 0.229 ↓ 0.0 0 1

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

42. 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)
43. 0.000 0.000 ↓ 0.0 0

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

44. 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)
45. 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[]))
46. 36.298 10,028.061 ↓ 159.2 34,378 1

Bitmap Heap Scan on entity_values entity_values_3 (cost=5,150.68..6,026.07 rows=216 width=8) (actual time=9,994.743..10,028.061 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
47. 9,991.763 9,991.763 ↓ 162.5 35,097 1

Bitmap Index Scan on entity_values_tsv_index (cost=0.00..5,150.62 rows=216 width=0) (actual time=9,991.762..9,991.763 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
48. 48.558 48.558 ↑ 1.0 1 24,279

Index Scan using companies_pkey on companies (cost=0.43..1.97 rows=1 width=340) (actual time=0.002..0.002 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
49. 10.961 323.338 ↓ 42.5 40,004 1

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

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1968kB
50. 31.300 312.377 ↓ 42.5 40,004 1

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

51. 64.419 281.077 ↓ 109.1 102,684 1

Sort (cost=101,468.75..101,471.10 rows=941 width=16) (actual time=261.367..281.077 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
52. 95.264 216.658 ↓ 109.1 102,684 1

Hash Join (cost=95,409.26..101,422.27 rows=941 width=16) (actual time=121.537..216.658 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.801 ro
  • Index Cond: (organization_id = 7)
  • Heap Fetches: 16859
53. 35.844 121.394 ↓ 1.1 115,943 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 5256kB
54. 75.826 85.550 ↓ 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.354..85.550 rows=115,943 loops=1)

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

  • Index Cond: (organization_id = 7)