explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BNbc : Legacy mode() query

Settings
# exclusive inclusive rows x rows loops node
1. 194,033.924 593,922.980 ↓ 0.0 0 1

Insert on gi_perf_entity t (cost=15,023,079.54..15,209,435.92 rows=2,662,234 width=3,828) (actual time=593,922.980..593,922.980 rows=0 loops=1)

  • Conflict Resolution: UPDATE
  • Conflict Arbiter Indexes: pkgi_perf_entity
  • Conflict Filter: (t.b_batchid <= '1'::numeric)
  • Tuples Inserted: 0
  • Conflicting Tuples: 4,500,000
2.          

CTE s

3. 1,378.461 389,302.335 ↓ 1.7 4,500,000 1

Subquery Scan on c2 (cost=14,929,901.35..15,023,079.54 rows=2,662,234 width=583) (actual time=358,424.210..389,302.335 rows=4,500,000 loops=1)

4. 387,923.874 387,923.874 ↓ 1.7 4,500,000 1

CTE Scan on consolidated c (cost=14,929,901.35..14,983,146.03 rows=2,662,234 width=567) (actual time=358,424.204..387,923.874 rows=4,500,000 loops=1)

5.          

CTE most_freq_vals

6. 17,864.436 40,983.426 ↓ 3.4 5,500,002 1

GroupAggregate (cost=2,032,831.71..2,160,738.13 rows=1,604,151 width=102) (actual time=17,891.796..40,983.426 rows=5,500,002 loops=1)

  • Group Key: s_1.perf_entity_id
7. 14,826.685 23,118.990 ↓ 1.3 10,000,002 1

Sort (cost=2,032,831.71..2,052,798.46 rows=7,986,702 width=39) (actual time=17,891.749..23,118.990 rows=10,000,002 loops=1)

  • Sort Key: s_1.perf_entity_id
  • Sort Method: external merge Disk: 515,328kB
8. 8,292.305 8,292.305 ↓ 1.3 10,000,002 1

Seq Scan on mi_perf_entity s_1 (cost=0.00..680,410.53 rows=7,986,702 width=39) (actual time=2.938..8,292.305 rows=10,000,002 loops=1)

  • Filter: ((b_batchid = '1'::numeric) AND ((b_classname)::text = 'PerfEntity'::text))
9.          

CTE consolidated

10. 8,618.327 383,278.253 ↓ 1.7 4,500,000 1

Unique (cost=12,622,740.35..12,769,163.22 rows=2,662,234 width=559) (actual time=358,424.196..383,278.253 rows=4,500,000 loops=1)

11. 89,023.556 374,659.926 ↓ 3.4 9,000,000 1

Sort (cost=12,622,740.35..12,629,395.94 rows=2,662,234 width=559) (actual time=358,424.194..374,659.926 rows=9,000,000 loops=1)

  • Sort Key: s_2.perf_entity_id, (first_value(s_2.b_pubid) OVER (?)), (first_value(s_2.b_sourceid) OVER (?)), (first_value(s_2.match_key) OVER (?)), (first_value(s_2.suggestion_key) OVER (?)), freq.attr_most_freq1, freq.attr_most_freq2, freq.attr_most_freq3, (first_value(s_2.attr_ranking1) OVER (?)), (first_value(s_2.attr_ranking2) OVER (?)), (first_value(s_2.attr_ranking3) OVER (?)), (count(1) OVER (?)), (sum(CASE s_2.b_confirmationstatus WHEN 'CONFIRMED'::text THEN 1 ELSE 0 END) OVER (?)), (sum(CASE s_2.b_confirmationstatus WHEN 'NOT_CONFIRMED'::text THEN 1 ELSE 0 END) OVER (?)), (first_value(s_2.b_confscore) OVER (?)), (first_value(s_2.b_confscoretype) OVER (?)), (first_value(s_2.b_hassuggmerge) OVER (?)), (first_value(s_2.b_suggmergeid) OVER (?)), (first_value(s_2.b_suggmergemasterscount) OVER (?)), (first_value(s_2.b_suggmergeconfscore) OVER (?)), (first_value(s_2.b_suggmergeconfscoretype) OVER (?))
  • Sort Method: external merge Disk: 1,708,520kB
12. 21,739.651 285,636.370 ↓ 3.4 9,000,000 1

WindowAgg (cost=10,219,383.38..10,345,839.50 rows=2,662,234 width=559) (actual time=254,562.609..285,636.370 rows=9,000,000 loops=1)

13. 82,147.359 263,896.719 ↓ 3.4 9,000,000 1

Sort (cost=10,219,383.38..10,226,038.97 rows=2,662,234 width=863) (actual time=254,559.873..263,896.719 rows=9,000,000 loops=1)

  • Sort Key: s_2.perf_entity_id, (CASE WHEN (s_2.perf_entity_id IS NOT NULL) THEN 0 ELSE 1 END)
  • Sort Method: external merge Disk: 1,928,648kB
14. 18,963.915 181,749.360 ↓ 3.4 9,000,000 1

WindowAgg (cost=6,805,311.75..6,905,145.53 rows=2,662,234 width=863) (actual time=156,171.121..181,749.360 rows=9,000,000 loops=1)

15. 53,604.467 162,785.445 ↓ 3.4 9,000,000 1

Sort (cost=6,805,311.75..6,811,967.34 rows=2,662,234 width=639) (actual time=156,171.084..162,785.445 rows=9,000,000 loops=1)

  • Sort Key: s_2.perf_entity_id, s_2.b_pubid, s_2.b_sourceid
  • Sort Method: external merge Disk: 1,417,832kB
16. 17,190.367 109,180.978 ↓ 3.4 9,000,000 1

Hash Join (cost=3,939,981.60..4,255,431.90 rows=2,662,234 width=639) (actual time=66,079.081..109,180.978 rows=9,000,000 loops=1)

  • Hash Cond: (freq.perf_entity_id = s_2.perf_entity_id)
17. 43,809.881 43,809.881 ↓ 3.4 5,500,002 1

CTE Scan on most_freq_vals freq (cost=0.00..32,083.02 rows=1,604,151 width=126) (actual time=17,891.799..43,809.881 rows=5,500,002 loops=1)

18. 4,498.546 48,180.730 ↓ 3.4 9,000,000 1

Hash (cost=3,719,514.67..3,719,514.67 rows=2,662,234 width=545) (actual time=48,180.730..48,180.730 rows=9,000,000 loops=1)

  • Buckets: 8,192 Batches: 512 Memory Usage: 2,647kB
19. 17,822.529 43,682.184 ↓ 3.4 9,000,000 1

Hash Join (cost=1,908,517.99..3,719,514.67 rows=2,662,234 width=545) (actual time=17,886.847..43,682.184 rows=9,000,000 loops=1)

  • Hash Cond: (s_2.perf_entity_id = s_3.perf_entity_id)
20. 7,976.985 7,976.985 ↓ 1.3 10,000,002 1

Seq Scan on mi_perf_entity s_2 (cost=0.00..680,410.53 rows=7,986,702 width=539) (actual time=3.562..7,976.985 rows=10,000,002 loops=1)

  • Filter: ((b_batchid = '1'::numeric) AND ((b_classname)::text = 'PerfEntity'::text))
21. 936.763 17,882.670 ↓ 8.4 4,500,000 1

Hash (cost=1,899,745.03..1,899,745.03 rows=534,717 width=6) (actual time=17,882.663..17,882.670 rows=4,500,000 loops=1)

  • Buckets: 131,072 (originally 131072) Batches: 64 (originally 8) Memory Usage: 3,705kB
22. 2,409.733 16,945.907 ↓ 8.4 4,500,000 1

GroupAggregate (cost=1,814,445.71..1,894,397.86 rows=534,717 width=6) (actual time=12,379.216..16,945.907 rows=4,500,000 loops=1)

  • Group Key: s_3.perf_entity_id
  • Filter: (count(*) > 1)
  • Rows Removed by Filter: 1,000,002
23. 7,479.032 14,536.174 ↓ 1.3 10,000,002 1

Sort (cost=1,814,445.71..1,834,412.46 rows=7,986,702 width=6) (actual time=12,379.199..14,536.174 rows=10,000,002 loops=1)

  • Sort Key: s_3.perf_entity_id
  • Sort Method: external merge Disk: 166,344kB
24. 7,057.142 7,057.142 ↓ 1.3 10,000,002 1

Seq Scan on mi_perf_entity s_3 (cost=0.00..680,410.53 rows=7,986,702 width=6) (actual time=0.026..7,057.142 rows=10,000,002 loops=1)

  • Filter: ((b_batchid = '1'::numeric) AND ((b_classname)::text = 'PerfEntity'::text))
25. 399,889.056 399,889.056 ↓ 1.7 4,500,000 1

CTE Scan on s (cost=0.00..186,356.38 rows=2,662,234 width=3,828) (actual time=358,424.223..399,889.056 rows=4,500,000 loops=1)

Planning time : 0.927 ms
Execution time : 594,268.507 ms