explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xsX9

Settings
# exclusive inclusive rows x rows loops node
1. 180.191 18,954.450 ↑ 2.0 12 1

GroupAggregate (cost=881,783.50..881,784.87 rows=24 width=95) (actual time=18,665.416..18,954.450 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=212 read=262,348, temp read=30,644 written=30,644
2. 254.744 18,774.259 ↓ 3,596.0 136,647 1

Sort (cost=881,783.50..881,783.60 rows=38 width=49) (actual time=18,665.308..18,774.259 rows=136,647 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: 21,611kB
  • Buffers: shared hit=212 read=262,348, temp read=30,644 written=30,644
3. 293.458 18,519.515 ↓ 3,596.0 136,647 1

Hash Join (cost=786,526.42..881,782.51 rows=38 width=49) (actual time=7,356.772..18,519.515 rows=136,647 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=212 read=262,348, temp read=30,644 written=30,644
4. 2,309.339 18,207.047 ↓ 26.9 202,648 1

Subquery Scan on r (cost=786,193.57..881,420.88 rows=7,547 width=64) (actual time=7,337.750..18,207.047 rows=202,648 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 NULL) AND (r.rn = 1))
  • Rows Removed by Filter: 2,536,480
  • Buffers: shared hit=152 read=262,348, temp read=30,644 written=30,644
5. 5,291.711 15,897.708 ↑ 1.1 2,739,128 1

WindowAgg (cost=786,193.57..844,794.99 rows=2,930,071 width=299) (actual time=7,337.688..15,897.708 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=152 read=262,348, temp read=30,644 written=30,644
6. 6,930.590 10,605.997 ↑ 1.1 2,739,128 1

Sort (cost=786,193.57..793,518.75 rows=2,930,071 width=88) (actual time=7,337.678..10,605.997 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: 244,920kB
  • Buffers: shared hit=152 read=262,348, temp read=30,644 written=30,644
7. 3,675.407 3,675.407 ↑ 1.1 2,739,128 1

Seq Scan on corda.spunta_record r_1 (cost=0.00..331,257.26 rows=2,930,071 width=88) (actual time=3.031..3,675.407 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: 2,172,012
  • Buffers: shared hit=152 read=262,348
8. 2.168 19.010 ↓ 195.2 2,538 1

Hash (cost=332.69..332.69 rows=13 width=16) (actual time=19.009..19.010 rows=2,538 loops=1)

  • Output: rep.uid
  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 151kB
  • Buffers: shared hit=60
9. 3.920 16.842 ↓ 195.2 2,538 1

Subquery Scan on rep (cost=236.77..332.69 rows=13 width=16) (actual time=5.943..16.842 rows=2,538 loops=1)

  • Output: rep.uid
  • Filter: (rep.rn = 1)
  • Buffers: shared hit=60
10. 5.009 12.922 ↑ 1.0 2,538 1

WindowAgg (cost=236.77..300.72 rows=2,558 width=58) (actual time=5.939..12.922 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
11. 5.531 7.913 ↑ 1.0 2,538 1

Sort (cost=236.77..243.16 rows=2,558 width=50) (actual time=5.934..7.913 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
12. 2.382 2.382 ↑ 1.0 2,538 1

Seq Scan on corda.spunta_report_upload rep_1 (cost=0.00..91.97 rows=2,558 width=50) (actual time=0.012..2.382 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.294 ms
Execution time : 19,000.403 ms