explain.depesz.com

PostgreSQL's explain analyze made readable

Result: T63g

Settings
# exclusive inclusive rows x rows loops node
1. 0.934 27,229.064 ↓ 1.3 9 1

GroupAggregate (cost=51,260,916.17..51,260,916.47 rows=7 width=95) (actual time=27,227.609..27,229.064 rows=9 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=4660248 read=306163, temp read=23107 written=23107
2. 1.988 27,228.130 ↓ 102.7 719 1

Sort (cost=51,260,916.17..51,260,916.19 rows=7 width=49) (actual time=27,227.590..27,228.130 rows=719 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: 92kB
  • Buffers: shared hit=4660248 read=306163, temp read=23107 written=23107
3. 2.895 27,226.142 ↓ 102.7 719 1

Hash Join (cost=605,096.54..51,260,916.07 rows=7 width=49) (actual time=5,966.018..27,226.142 rows=719 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=4660248 read=306163, temp read=23107 written=23107
4. 3,418.094 27,203.876 ↑ 2.0 719 1

Subquery Scan on r (cost=604,765.28..51,260,579.36 rows=1,431 width=64) (actual time=5,946.625..27,203.876 rows=719 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.uidmatch IS NOT NULL) AND (r.rn = 1) AND ((SubPlan 1) <= '2018-10-31'::date))
  • Rows Removed by Filter: 2051603
  • Buffers: shared hit=4660188 read=306163, temp read=23107 written=23107
5. 4,021.496 12,518.911 ↓ 1.2 2,052,322 1

WindowAgg (cost=604,765.28..640,192.42 rows=1,771,357 width=299) (actual time=5,858.311..12,518.911 rows=2,052,322 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=344 read=262156, temp read=23107 written=23107
6. 5,412.616 8,497.415 ↓ 1.2 2,052,322 1

Sort (cost=604,765.28..609,193.67 rows=1,771,357 width=88) (actual time=5,858.300..8,497.415 rows=2,052,322 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: 184688kB
  • Buffers: shared hit=344 read=262156, temp read=23107 written=23107
7. 3,084.799 3,084.799 ↓ 1.2 2,052,322 1

Seq Scan on corda.spunta_record r_1 (cost=0.00..336,167.10 rows=1,771,357 width=88) (actual time=0.012..3,084.799 rows=2,052,322 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: 2858818
  • Buffers: shared hit=344 read=262156
8.          

SubPlan (for Subquery Scan)

9. 4,097.044 11,266.871 ↑ 1.0 1 1,024,261

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

  • Output: min(m.dateaccounting)
  • Buffers: shared hit=4659844 read=44007
10. 7,169.827 7,169.827 ↑ 3.0 2 1,024,261

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,024,261)

  • 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=4659844 read=44007
11. 2.215 19.371 ↓ 195.2 2,538 1

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

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

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

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

WindowAgg (cost=235.70..299.30 rows=2,544 width=58) (actual time=6.152..13.130 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.805 8.164 ↑ 1.0 2,538 1

Sort (cost=235.70..242.06 rows=2,544 width=50) (actual time=6.146..8.164 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.359 2.359 ↑ 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.011..2.359 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.429 ms
Execution time : 27,265.291 ms