explain.depesz.com

PostgreSQL's explain analyze made readable

Result: E3IJ : with sorted mi

Settings
# exclusive inclusive rows x rows loops node
1. 9,556.856 404,221.824 ↓ 4.5 4,500,000 1

Hash Join (cost=15,859,226.67..16,006,570.33 rows=1,006,324 width=597) (actual time=379,990.302..404,221.824 rows=4,500,000 loops=1)

  • Hash Cond: (s2.perf_entity_id = m_1.perf_entity_id)
2.          

CTE sorted_mi

3. 120,146.564 120,146.564 ↓ 1.3 10,000,002 1

Index Scan using i1mi_perf_entity on mi_perf_entity m_2 (cost=0.56..4,260,234.53 rows=7,986,702 width=705) (actual time=7.001..120,146.564 rows=10,000,002 loops=1)

  • Filter: ((b_batchid = '1'::numeric) AND ((b_classname)::text = 'PerfEntity'::text))
4. 5,014.890 233,085.805 ↓ 2.8 4,500,000 1

Unique (cost=11,399,309.41..11,526,397.80 rows=1,597,340 width=823) (actual time=218,397.848..233,085.805 rows=4,500,000 loops=1)

5. 71,721.735 228,070.915 ↓ 3.4 9,000,000 1

Sort (cost=11,399,309.41..11,405,998.27 rows=2,675,545 width=823) (actual time=218,397.846..228,070.915 rows=9,000,000 loops=1)

  • Sort Key: s2.perf_entity_id, (first_value(s2.b_pubid) OVER (?)), (first_value(s2.b_sourceid) OVER (?)), (first_value(s2.match_key) OVER (?)), (first_value(s2.suggestion_key) OVER (?)), (first_value(s2.attr_ranking1) OVER (?)), (first_value(s2.attr_ranking2) OVER (?)), (first_value(s2.attr_ranking3) OVER (?)), (count(1) OVER (?)), (sum(CASE s2.b_confirmationstatus WHEN 'CONFIRMED'::text THEN 1 ELSE 0 END) OVER (?)), (sum(CASE s2.b_confirmationstatus WHEN 'NOT_CONFIRMED'::text THEN 1 ELSE 0 END) OVER (?)), (first_value(s2.b_confscore) OVER (?)), (first_value(s2.b_confscoretype) OVER (?)), (first_value(s2.b_hassuggmerge) OVER (?)), (first_value(s2.b_suggmergeid) OVER (?)), (first_value(s2.b_suggmergemasterscount) OVER (?)), (first_value(s2.b_suggmergeconfscore) OVER (?)), (first_value(s2.b_suggmergeconfscoretype) OVER (?))
  • Sort Method: external merge Disk: 1,364,856kB
6. 22,632.286 156,349.180 ↓ 3.4 9,000,000 1

WindowAgg (cost=7,991,542.13..8,205,585.73 rows=2,675,545 width=823) (actual time=107,461.973..156,349.180 rows=9,000,000 loops=1)

7. 17,942.671 133,716.894 ↓ 3.4 9,000,000 1

WindowAgg (cost=7,991,542.13..8,091,875.07 rows=2,675,545 width=2,331) (actual time=107,461.913..133,716.894 rows=9,000,000 loops=1)

8. 42,462.090 115,774.223 ↓ 3.4 9,000,000 1

Sort (cost=7,991,542.13..7,998,230.99 rows=2,675,545 width=2,107) (actual time=107,460.691..115,774.223 rows=9,000,000 loops=1)

  • Sort Key: s2.perf_entity_id, s2.b_pubid, s2.b_sourceid
  • Sort Method: external merge Disk: 1,065,600kB
9. 24,578.256 73,312.133 ↓ 3.4 9,000,000 1

Hash Join (cost=199,671.56..380,814.96 rows=2,675,545 width=2,107) (actual time=22,766.101..73,312.133 rows=9,000,000 loops=1)

  • Hash Cond: (s2.perf_entity_id = m.perf_entity_id)
10. 25,969.856 25,969.856 ↓ 1.3 10,000,002 1

CTE Scan on sorted_mi s2 (cost=0.00..159,734.04 rows=7,986,702 width=2,107) (actual time=0.802..25,969.856 rows=10,000,002 loops=1)

11. 874.631 22,764.021 ↓ 67,164.2 4,500,000 1

Hash (cost=199,670.72..199,670.72 rows=67 width=30) (actual time=22,764.020..22,764.021 rows=4,500,000 loops=1)

  • Buckets: 131,072 (originally 1024) Batches: 64 (originally 1) Memory Usage: 3,705kB
12. 7,368.232 21,889.390 ↓ 67,164.2 4,500,000 1

HashAggregate (cost=199,667.55..199,670.05 rows=67 width=30) (actual time=19,994.238..21,889.390 rows=4,500,000 loops=1)

  • Group Key: m.perf_entity_id
  • Filter: (count(*) > 1)
  • Rows Removed by Filter: 1,000,002
13. 14,521.158 14,521.158 ↓ 1.3 10,000,002 1

CTE Scan on sorted_mi m (cost=0.00..159,734.04 rows=7,986,702 width=30) (actual time=0.001..14,521.158 rows=10,000,002 loops=1)

14. 1,804.312 161,579.163 ↓ 43,650.8 5,500,002 1

Hash (cost=199,681.16..199,681.16 rows=126 width=126) (actual time=161,579.163..161,579.163 rows=5,500,002 loops=1)

  • Buckets: 65,536 (originally 1024) Batches: 128 (originally 1) Memory Usage: 3,630kB
15. 17,752.373 159,774.851 ↓ 43,650.8 5,500,002 1

GroupAggregate (cost=199,675.19..199,679.90 rows=126 width=126) (actual time=139,820.169..159,774.851 rows=5,500,002 loops=1)

  • Group Key: m_1.perf_entity_id
16. 13,882.486 142,022.478 ↓ 50,000.0 10,000,002 1

Sort (cost=199,675.19..199,675.69 rows=200 width=852) (actual time=139,820.107..142,022.478 rows=10,000,002 loops=1)

  • Sort Key: m_1.perf_entity_id
  • Sort Method: external merge Disk: 515,328kB
17. 128,139.992 128,139.992 ↓ 50,000.0 10,000,002 1

CTE Scan on sorted_mi m_1 (cost=0.00..199,667.55 rows=200 width=852) (actual time=7.007..128,139.992 rows=10,000,002 loops=1)

  • Filter: ((b_batchid = '1'::numeric) AND ((b_classname)::text = 'PerfEntity'::text))
Planning time : 3.332 ms
Execution time : 404,655.305 ms