explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JYun

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 43,487.403 ↓ 0.0 0 1

Limit (cost=19,265,913.07..19,265,913.16 rows=36 width=290) (actual time=43,487.403..43,487.403 rows=0 loops=1)

2. 0.009 43,487.403 ↓ 0.0 0 1

Sort (cost=19,265,913.07..19,265,913.16 rows=36 width=290) (actual time=43,487.403..43,487.403 rows=0 loops=1)

  • Sort Key: temp.full_name
  • Sort Method: quicksort Memory: 25kB
3. 948.487 43,487.394 ↓ 0.0 0 1

Subquery Scan on temp (cost=378,768.62..19,265,912.14 rows=36 width=290) (actual time=43,487.394..43,487.394 rows=0 loops=1)

  • Filter: (temp.suggested_name ~~* 'Preston%'::text)
  • Rows Removed by Filter: 359,721
4. 284.419 42,538.907 ↓ 1.0 359,721 1

Unique (cost=378,768.62..19,261,416.15 rows=359,679 width=290) (actual time=3,389.622..42,538.907 rows=359,721 loops=1)

5. 784.496 42,254.488 ↑ 1,009.7 359,721 1

Merge Left Join (cost=378,768.62..18,353,396.15 rows=363,208,000 width=290) (actual time=3,389.621..42,254.488 rows=359,721 loops=1)

  • Merge Cond: (can.id = cd_1.candidate_id)
6. 155.579 39,820.484 ↑ 1,009.7 359,721 1

Merge Left Join (cost=227,223.62..8,441,961.74 rows=363,208,000 width=207) (actual time=2,292.850..39,820.484 rows=359,721 loops=1)

  • Merge Cond: (can.id = asd.internal_id)
7. 290.711 39,664.868 ↑ 1,009.7 359,721 1

Nested Loop Left Join (cost=227,212.79..7,533,920.93 rows=363,208,000 width=203) (actual time=2,292.811..39,664.868 rows=359,721 loops=1)

8. 282.093 4,121.499 ↑ 1.0 359,721 1

Merge Left Join (cost=227,212.54..269,760.68 rows=363,208 width=203) (actual time=2,292.606..4,121.499 rows=359,721 loops=1)

  • Merge Cond: (can.id = cce.candidate_id)
9. 241.033 2,820.678 ↑ 1.0 359,721 1

Merge Left Join (cost=227,208.42..230,684.21 rows=362,975 width=158) (actual time=2,292.352..2,820.678 rows=359,721 loops=1)

  • Merge Cond: (can.id = cd.candidate_id)
10. 452.381 2,221.005 ↑ 1.0 359,721 1

Sort (cost=185,632.35..186,539.79 rows=362,975 width=117) (actual time=2,010.534..2,221.005 rows=359,721 loops=1)

  • Sort Key: can.id
  • Sort Method: external merge Disk: 30,736kB
11. 62.741 1,768.624 ↑ 1.0 359,721 1

Hash Left Join (cost=38,006.28..129,779.00 rows=362,975 width=117) (actual time=560.213..1,768.624 rows=359,721 loops=1)

  • Hash Cond: ((loc.country_code)::text = (co.code)::text)
12. 56.634 1,705.780 ↑ 1.0 359,721 1

Hash Left Join (cost=37,997.59..128,808.54 rows=362,975 width=109) (actual time=560.100..1,705.780 rows=359,721 loops=1)

  • Hash Cond: (can.contact_id = con.id)
13. 347.052 1,329.286 ↑ 1.0 359,721 1

Hash Left Join (cost=17,781.78..97,984.53 rows=362,975 width=71) (actual time=239.940..1,329.286 rows=359,721 loops=1)

  • Hash Cond: (can.id = loc.current_location_candidate_id)
14. 747.315 747.315 ↓ 1.0 359,721 1

Seq Scan on candidate can (cost=0.00..69,380.59 rows=359,679 width=59) (actual time=0.048..747.315 rows=359,721 loops=1)

  • Filter: (deleted_timestamp IS NULL)
  • Rows Removed by Filter: 3,738
15. 86.291 234.919 ↑ 1.1 336,162 1

Hash (cost=11,046.90..11,046.90 rows=366,790 width=20) (actual time=234.919..234.919 rows=336,162 loops=1)

  • Buckets: 65,536 Batches: 8 Memory Usage: 2,557kB
16. 148.628 148.628 ↑ 1.0 366,790 1

Seq Scan on common_location loc (cost=0.00..11,046.90 rows=366,790 width=20) (actual time=0.045..148.628 rows=366,790 loops=1)

17. 56.030 319.860 ↑ 1.0 82,039 1

Hash (cost=18,465.02..18,465.02 rows=82,223 width=42) (actual time=319.860..319.860 rows=82,039 loops=1)

  • Buckets: 65,536 Batches: 2 Memory Usage: 3,563kB
18. 78.718 263.830 ↑ 1.0 82,039 1

Hash Left Join (cost=10,702.72..18,465.02 rows=82,223 width=42) (actual time=124.208..263.830 rows=82,039 loops=1)

  • Hash Cond: (con.company_id = comp.id)
19. 61.615 61.615 ↑ 1.0 82,039 1

Seq Scan on contact con (cost=0.00..5,351.47 rows=82,223 width=22) (actual time=0.033..61.615 rows=82,039 loops=1)

  • Filter: (deleted_timestamp IS NULL)
  • Rows Removed by Filter: 15,308
20. 53.648 123.497 ↑ 1.0 179,943 1

Hash (cost=7,222.43..7,222.43 rows=179,943 width=28) (actual time=123.497..123.497 rows=179,943 loops=1)

  • Buckets: 65,536 Batches: 4 Memory Usage: 3,238kB
21. 69.849 69.849 ↑ 1.0 179,943 1

Seq Scan on company comp (cost=0.00..7,222.43 rows=179,943 width=28) (actual time=0.062..69.849 rows=179,943 loops=1)

22. 0.045 0.103 ↑ 1.0 253 1

Hash (cost=5.53..5.53 rows=253 width=13) (actual time=0.103..0.103 rows=253 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
23. 0.058 0.058 ↑ 1.0 253 1

Seq Scan on country co (cost=0.00..5.53 rows=253 width=13) (actual time=0.031..0.058 rows=253 loops=1)

24. 28.676 358.640 ↓ 1.8 93,753 1

Unique (cost=41,576.07..42,058.26 rows=52,419 width=53) (actual time=281.631..358.640 rows=93,753 loops=1)

25. 122.960 329.964 ↑ 1.0 95,276 1

Sort (cost=41,576.07..41,817.16 rows=96,438 width=53) (actual time=281.629..329.964 rows=95,276 loops=1)

  • Sort Key: cd.candidate_id, cd.created DESC
  • Sort Method: external merge Disk: 6,168kB
26. 192.065 207.004 ↑ 1.0 95,276 1

Bitmap Heap Scan on candidate_document cd (cost=1,919.82..30,295.29 rows=96,438 width=53) (actual time=17.796..207.004 rows=95,276 loops=1)

  • Recheck Cond: ((document_type)::text = 'candidate_photo'::text)
  • Heap Blocks: exact=20,276
27. 14.939 14.939 ↑ 1.0 95,276 1

Bitmap Index Scan on candidate_document_document_type_idx (cost=0.00..1,895.71 rows=96,438 width=0) (actual time=14.939..14.939 rows=95,276 loops=1)

  • Index Cond: ((document_type)::text = 'candidate_photo'::text)
28. 1,018.728 1,018.728 ↑ 1.0 363,459 1

Index Scan using candidate_extension_candidate_id__pkey on candidate_extension cce (cost=0.42..33,634.23 rows=363,692 width=53) (actual time=0.048..1,018.728 rows=363,459 loops=1)

29. 35,252.658 35,252.658 ↑ 1,000.0 1 359,721

Function Scan on fn_get_candidate_chevron (cost=0.25..10.25 rows=1,000 width=0) (actual time=0.098..0.098 rows=1 loops=359,721)

30. 0.003 0.037 ↓ 0.0 0 1

Sort (cost=8.32..8.33 rows=1 width=12) (actual time=0.037..0.037 rows=0 loops=1)

  • Sort Key: asd.internal_id
  • Sort Method: quicksort Memory: 25kB
31. 0.034 0.034 ↓ 0.0 0 1

Index Scan using app_sync_data_entity_idx on app_sync_data asd (cost=0.29..8.31 rows=1 width=12) (actual time=0.034..0.034 rows=0 loops=1)

  • Index Cond: ((entity_type)::text = 'candidate'::text)
  • Filter: ((app_id)::text = 'TIME_TEMP'::text)
32. 308.983 1,649.508 ↓ 4.9 330,051 1

GroupAggregate (cost=151,545.01..157,787.88 rows=67,307 width=12) (actual time=1,096.751..1,649.508 rows=330,051 loops=1)

  • Group Key: cd_1.candidate_id
33. 474.211 1,340.525 ↑ 1.1 672,581 1

Sort (cost=151,545.01..153,401.61 rows=742,641 width=4) (actual time=1,089.446..1,340.525 rows=672,581 loops=1)

  • Sort Key: cd_1.candidate_id
  • Sort Method: external merge Disk: 9,216kB
34. 184.710 866.314 ↑ 1.1 672,854 1

Hash Right Join (cost=66,046.05..68,975.45 rows=742,641 width=4) (actual time=682.040..866.314 rows=672,854 loops=1)

  • Hash Cond: (dv.doc_id = cd_1.id)
35. 0.014 0.014 ↓ 0.0 0 1

Seq Scan on document_verifier dv (cost=0.00..15.80 rows=580 width=16) (actual time=0.014..0.014 rows=0 loops=1)

36. 138.247 681.590 ↑ 1.1 672,854 1

Hash (cost=53,862.04..53,862.04 rows=742,641 width=8) (actual time=681.590..681.590 rows=672,854 loops=1)

  • Buckets: 131,072 Batches: 16 Memory Usage: 2,668kB
37. 115.867 543.343 ↑ 1.1 672,854 1

Hash Join (cost=1.45..53,862.04 rows=742,641 width=8) (actual time=0.061..543.343 rows=672,854 loops=1)

  • Hash Cond: (cd_1.document_types_id = dt.id)
38. 427.447 427.447 ↓ 1.0 766,881 1

Seq Scan on candidate_document cd_1 (cost=0.00..51,555.82 rows=742,641 width=44) (actual time=0.029..427.447 rows=766,881 loops=1)

  • Filter: ((visible = 1) AND ((contact_id > 0) OR (candidate_id > 0) OR (deal_id > 0) OR (position_description_id > 0) OR (company_id > 0)))
  • Rows Removed by Filter: 208,552
39. 0.006 0.029 ↑ 1.0 20 1

Hash (cost=1.20..1.20 rows=20 width=4) (actual time=0.029..0.029 rows=20 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
40. 0.023 0.023 ↑ 1.0 20 1

Seq Scan on document_types dt (cost=0.00..1.20 rows=20 width=4) (actual time=0.022..0.023 rows=20 loops=1)

Planning time : 11.114 ms
Execution time : 43,502.670 ms