explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ee6C

Settings
# exclusive inclusive rows x rows loops node
1. 330.848 36,068.321 ↓ 2,861.8 137,366 1

Sort (cost=90,708,031.88..90,708,032.00 rows=48 width=121) (actual time=35,947.387..36,068.321 rows=137,366 loops=1)

  • Output: r.uid, r.transaction_id, r.output_index, r.uidrelationship, r.abisender, r.datevalue, r.dateaccounting, r.value
  • Sort Key: r.dateaccounting
  • Sort Method: external merge Disk: 18320kB
  • Buffers: shared hit=5907105 read=187484, temp read=59832 written=59832
2. 294.119 35,737.473 ↓ 2,861.8 137,366 1

Hash Join (cost=932,783.75..90,708,030.54 rows=48 width=121) (actual time=7,692.271..35,737.473 rows=137,366 loops=1)

  • Output: r.uid, r.transaction_id, r.output_index, r.uidrelationship, r.abisender, r.datevalue, r.dateaccounting, r.value
  • Hash Cond: (r.uidreportupload = rep.uid)
  • Buffers: shared hit=5907105 read=187484, temp read=57541 written=57541
3. 4,503.582 35,424.032 ↓ 21.8 203,367 1

Subquery Scan on r (cost=932,452.49..90,707,663.87 rows=9,316 width=137) (actual time=7,672.941..35,424.032 rows=203,367 loops=1)

  • Output: r.uidreportupload, r.uid, r.transaction_id, r.output_index, r.uidrelationship, r.abisender, r.datevalue, r.dateaccounting, r.value, r.uidmatch, r.matchtype, r.rn, r_1."timestamp
  • Filter: ((r.rn = 1) AND ((r.uidmatch IS NULL) OR ((r.uidmatch IS NOT NULL) AND ((SubPlan 1) > '2018-10-31'::date))))
  • Rows Removed by Filter: 2535761
  • Buffers: shared hit=5907045 read=187484, temp read=57541 written=57541
4. 5,403.312 16,971.075 ↑ 1.1 2,739,128 1

WindowAgg (cost=932,452.49..995,238.55 rows=3,139,303 width=314) (actual time=7,672.777..16,971.075 rows=2,739,128 loops=1)

  • Output: r_1.uidreportupload, r_1.uid, r_1.transaction_id, r_1.output_index, r_1.uidrelationship, r_1.abisender, r_1.datevalue, r_1.dateaccounting, r_1.value, r_1.uidmatch, NULL::character varying(255), row_number() OVER (?), r_1."timestamp
  • Buffers: shared hit=75016 read=187484, temp read=57541 written=57541
5. 7,886.951 11,567.763 ↑ 1.1 2,739,128 1

Sort (cost=932,452.49..940,300.75 rows=3,139,303 width=161) (actual time=7,672.768..11,567.763 rows=2,739,128 loops=1)

  • Output: r_1.uid, r_1."timestamp", r_1.uidreportupload, r_1.transaction_id, r_1.output_index, r_1.uidrelationship, r_1.abisender, r_1.datevalue, r_1.dateaccounting, r_1.value, r_1.uidmatch
  • Sort Key: r_1.uid, r_1."timestamp" DESC
  • Sort Method: external merge Disk: 459928kB
  • Buffers: shared hit=75016 read=187484, temp read=57541 written=57541
6. 3,680.812 3,680.812 ↑ 1.1 2,739,128 1

Seq Scan on corda.spunta_record r_1 (cost=0.00..336,167.10 rows=3,139,303 width=161) (actual time=23.967..3,680.812 rows=2,739,128 loops=1)

  • Output: r_1.uid, r_1."timestamp", r_1.uidreportupload, r_1.transaction_id, r_1.output_index, r_1.uidrelationship, r_1.abisender, r_1.datevalue, r_1.dateaccounting, r_1.value, r_1.uidmatch
  • Filter: ((r_1."timestamp" < '2020-02-07 19:08:43.074'::timestamp without time zone) AND (r_1.dateaccounting <= '2018-10-31'::date))
  • Rows Removed by Filter: 2172012
  • Buffers: shared hit=75016 read=187484
7.          

SubPlan (for Subquery Scan)

8. 5,072.500 13,949.375 ↑ 1.0 1 1,268,125

Aggregate (cost=28.55..28.56 rows=1 width=4) (actual time=0.010..0.011 rows=1 loops=1,268,125)

  • Output: max(m.dateaccounting)
  • Buffers: shared hit=5832029
9. 8,876.875 8,876.875 ↑ 3.0 2 1,268,125

Index Scan using idx_spunta_record_uidmatch on corda.spunta_record m (cost=0.43..28.54 rows=6 width=4) (actual time=0.004..0.007 rows=2 loops=1,268,125)

  • Output: m.output_index, m.transaction_id, m.abisender, m.cause, m.cro, m.crointermediary, m.dateaccounting, m.datevalue, m.humanreadableid, m.isblacklisted, m.isin, m.matchtype, m.uid, m.uidmatch, m.uidrelationship, m.uidreportupload, m.uiduploadnote, m.value, m."timestamp
  • Index Cond: (m.uidmatch = r.uidmatch)
  • Buffers: shared hit=5832029
10. 2.158 19.322 ↓ 195.2 2,538 1

Hash (cost=331.10..331.10 rows=13 width=16) (actual time=19.321..19.322 rows=2,538 loops=1)

  • Output: rep.uid
  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 151kB
  • Buffers: shared hit=60
11. 3.963 17.164 ↓ 195.2 2,538 1

Subquery Scan on rep (cost=235.70..331.10 rows=13 width=16) (actual time=6.209..17.164 rows=2,538 loops=1)

  • Output: rep.uid
  • Filter: (rep.rn = 1)
  • Buffers: shared hit=60
12. 5.016 13.201 ↑ 1.0 2,538 1

WindowAgg (cost=235.70..299.30 rows=2,544 width=58) (actual time=6.205..13.201 rows=2,538 loops=1)

  • Output: rep_1.uid, rep_1.uidrelationship, rep_1.abisender, rep_1.datereference, row_number() OVER (?), rep_1."timestamp
  • Buffers: shared hit=60
13. 5.610 8.185 ↑ 1.0 2,538 1

Sort (cost=235.70..242.06 rows=2,544 width=50) (actual time=6.194..8.185 rows=2,538 loops=1)

  • Output: rep_1.uidrelationship, rep_1.abisender, rep_1.datereference, rep_1."timestamp", rep_1.uid
  • Sort Key: rep_1.uidrelationship, rep_1.datereference, rep_1.abisender, rep_1."timestamp" DESC
  • Sort Method: quicksort Memory: 453kB
  • Buffers: shared hit=60
14. 2.575 2.575 ↑ 1.0 2,538 1

Seq Scan on corda.spunta_report_upload rep_1 (cost=0.00..91.80 rows=2,544 width=50) (actual time=0.009..2.575 rows=2,538 loops=1)

  • Output: rep_1.uidrelationship, rep_1.abisender, rep_1.datereference, rep_1."timestamp", rep_1.uid
  • Filter: (rep_1."timestamp" < '2020-02-07 19:08:43.074'::timestamp without time zone)
  • Rows Removed by Filter: 6
  • Buffers: shared hit=60
Planning time : 6.251 ms
Execution time : 36,263.767 ms