explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LzFo

Settings
# exclusive inclusive rows x rows loops node
1. 179.269 35,760.639 ↑ 2.0 12 1

GroupAggregate (cost=90,600,732.50..90,600,734.12 rows=24 width=95) (actual time=35,472.888..35,760.639 rows=12 loops=1)

  • Output: r.uidrelationship, r.abisender, (('0'::numeric > r.value)), count(r.uid), sum(r.value), sum(((GREATEST(('2018-10-31'::date - r.datevalue), 0))::numeric * r.value))
  • Group Key: r.uidrelationship, r.abisender, (('0'::numeric > r.value))
  • Buffers: shared hit=5827785 read=266804, temp read=30643 written=30643
2. 269.979 35,581.370 ↓ 2,861.8 137,366 1

Sort (cost=90,600,732.50..90,600,732.62 rows=48 width=49) (actual time=35,472.797..35,581.370 rows=137,366 loops=1)

  • Output: r.uidrelationship, r.abisender, (('0'::numeric > r.value)), r.uid, r.value, r.datevalue
  • Sort Key: r.uidrelationship, r.abisender, (('0'::numeric > r.value))
  • Sort Method: quicksort Memory: 21684kB
  • Buffers: shared hit=5827785 read=266804, temp read=30643 written=30643
3. 313.116 35,311.391 ↓ 2,861.8 137,366 1

Hash Join (cost=825,484.25..90,600,731.16 rows=48 width=49) (actual time=7,288.320..35,311.391 rows=137,366 loops=1)

  • Output: r.uidrelationship, r.abisender, ('0'::numeric > r.value), r.uid, r.value, r.datevalue
  • Hash Cond: (r.uidreportupload = rep.uid)
  • Buffers: shared hit=5827785 read=266804, temp read=30643 written=30643
4. 4,644.233 34,978.929 ↓ 21.8 203,367 1

Subquery Scan on r (cost=825,152.99..90,600,364.37 rows=9,316 width=64) (actual time=7,268.961..34,978.929 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=5827725 read=266804, temp read=30643 written=30643
5. 5,465.101 16,385.321 ↑ 1.1 2,739,128 1

WindowAgg (cost=825,152.99..887,939.05 rows=3,139,303 width=299) (actual time=7,268.716..16,385.321 rows=2,739,128 loops=1)

  • Output: r_1.uidreportupload, r_1.uid, NULL::character varying(64), NULL::integer, r_1.uidrelationship, r_1.abisender, r_1.datevalue, NULL::date, r_1.value, r_1.uidmatch, NULL::character varying(255), row_number() OVER (?), r_1."timestamp
  • Buffers: shared hit=35324 read=227176, temp read=30643 written=30643
6. 7,314.263 10,920.220 ↑ 1.1 2,739,128 1

Sort (cost=825,152.99..833,001.25 rows=3,139,303 width=88) (actual time=7,268.707..10,920.220 rows=2,739,128 loops=1)

  • Output: r_1.uid, r_1."timestamp", r_1.uidreportupload, r_1.uidrelationship, r_1.abisender, r_1.datevalue, r_1.value, r_1.uidmatch
  • Sort Key: r_1.uid, r_1."timestamp" DESC
  • Sort Method: external merge Disk: 244912kB
  • Buffers: shared hit=35324 read=227176, temp read=30643 written=30643
7. 3,605.957 3,605.957 ↑ 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=88) (actual time=22.280..3,605.957 rows=2,739,128 loops=1)

  • Output: r_1.uid, r_1."timestamp", r_1.uidreportupload, r_1.uidrelationship, r_1.abisender, r_1.datevalue, 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=35324 read=227176
8.          

SubPlan (for Subquery Scan)

9. 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=5792401 read=39628
10. 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=5792401 read=39628
11. 2.144 19.346 ↓ 195.2 2,538 1

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

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

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

  • Output: rep.uid
  • Filter: (rep.rn = 1)
  • Buffers: shared hit=60
13. 5.075 13.218 ↑ 1.0 2,538 1

WindowAgg (cost=235.70..299.30 rows=2,544 width=58) (actual time=6.165..13.218 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
14. 5.586 8.143 ↑ 1.0 2,538 1

Sort (cost=235.70..242.06 rows=2,544 width=50) (actual time=6.158..8.143 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
15. 2.557 2.557 ↑ 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.014..2.557 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 : 0.459 ms
Execution time : 35,805.302 ms