explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Z93E : With Added index

Settings
# exclusive inclusive rows x rows loops node
1. 259,063.214 490,485.640 ↓ 0.0 0 1

Insert on gi_perf_entity t (cost=20,028,024.61..6,047,398,764.08 rows=100,426,420,085 width=3,804) (actual time=490,485.640..490,485.640 rows=0 loops=1)

  • Conflict Resolution: UPDATE
  • Conflict Arbiter Indexes: pkgi_perf_entity
  • Conflict Filter: (t.b_batchid <= '1'::numeric)
  • Tuples Inserted: 1,000,002
  • Conflicting Tuples: 4,500,000
2. 16,745.463 231,422.426 ↑ 18,259.3 5,500,002 1

Hash Join (cost=20,028,024.61..6,047,398,764.08 rows=100,426,420,085 width=3,804) (actual time=204,170.831..231,422.426 rows=5,500,002 loops=1)

  • Hash Cond: (cons.perf_entity_id = freq.perf_entity_id)
3. 1,696.577 168,276.479 ↑ 1.8 5,500,002 1

Subquery Scan on cons (cost=17,371,132.35..17,946,132.47 rows=10,000,002 width=447) (actual time=157,765.855..168,276.479 rows=5,500,002 loops=1)

4. 4,943.732 166,579.902 ↑ 1.8 5,500,002 1

Unique (cost=17,371,132.35..17,846,132.45 rows=10,000,002 width=459) (actual time=157,765.848..166,579.902 rows=5,500,002 loops=1)

5. 61,019.069 161,636.170 ↑ 1.0 10,000,002 1

Sort (cost=17,371,132.35..17,396,132.36 rows=10,000,002 width=459) (actual time=157,765.845..161,636.170 rows=10,000,002 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,479,432kB
6. 24,925.201 100,617.101 ↑ 1.0 10,000,002 1

WindowAgg (cost=9,153,565.11..9,953,565.27 rows=10,000,002 width=459) (actual time=48,235.456..100,617.101 rows=10,000,002 loops=1)

7. 19,714.276 75,691.900 ↑ 1.0 10,000,002 1

WindowAgg (cost=9,153,565.11..9,528,565.19 rows=10,000,002 width=763) (actual time=48,235.424..75,691.900 rows=10,000,002 loops=1)

8. 44,616.493 55,977.624 ↑ 1.0 10,000,002 1

Sort (cost=9,153,565.11..9,178,565.12 rows=10,000,002 width=539) (actual time=48,235.385..55,977.624 rows=10,000,002 loops=1)

  • Sort Key: s2.perf_entity_id, s2.b_pubid, s2.b_sourceid
  • Sort Method: external merge Disk: 1,155,064kB
9. 11,361.131 11,361.131 ↑ 1.0 10,000,002 1

Seq Scan on mi_perf_entity s2 (cost=0.00..710,610.03 rows=10,000,002 width=539) (actual time=2.258..11,361.131 rows=10,000,002 loops=1)

  • Filter: ((b_batchid = '1'::numeric) AND ((b_classname)::text = 'PerfEntity'::text))
10. 1,983.570 46,400.484 ↓ 2.7 5,500,002 1

Hash (cost=2,600,401.66..2,600,401.66 rows=2,008,528 width=102) (actual time=46,400.484..46,400.484 rows=5,500,002 loops=1)

  • Buckets: 32,768 Batches: 128 Memory Usage: 3,362kB
11. 857.474 44,416.914 ↓ 2.7 5,500,002 1

Subquery Scan on freq (cost=2,420,167.11..2,600,401.66 rows=2,008,528 width=102) (actual time=23,475.703..44,416.914 rows=5,500,002 loops=1)

12. 17,528.002 43,559.440 ↓ 2.7 5,500,002 1

GroupAggregate (cost=2,420,167.11..2,580,316.38 rows=2,008,528 width=102) (actual time=23,475.701..43,559.440 rows=5,500,002 loops=1)

  • Group Key: s.perf_entity_id
13. 12,493.845 26,031.438 ↑ 1.0 10,000,002 1

Sort (cost=2,420,167.11..2,445,167.12 rows=10,000,002 width=39) (actual time=23,475.648..26,031.438 rows=10,000,002 loops=1)

  • Sort Key: s.perf_entity_id
  • Sort Method: external merge Disk: 515,336kB
14. 13,537.593 13,537.593 ↑ 1.0 10,000,002 1

Seq Scan on mi_perf_entity s (cost=0.00..710,610.03 rows=10,000,002 width=39) (actual time=1.406..13,537.593 rows=10,000,002 loops=1)

  • Filter: ((b_batchid = '1'::numeric) AND ((b_classname)::text = 'PerfEntity'::text))
Planning time : 0.886 ms
Execution time : 490,692.979 ms