explain.depesz.com

PostgreSQL's explain analyze made readable

Result: h85I

Settings
# exclusive inclusive rows x rows loops node
1. 215.324 11,944.560 ↑ 1.4 40,922 1

GroupAggregate (cost=2,053,454.07..2,061,023.39 rows=57,127 width=129) (actual time=11,651.848..11,944.560 rows=40,922 loops=1)

  • Group Key: e.bid, e.description, (to_char(e.transaction_datetime_local, 'YYYY-MM-DD'::text)), e.payout_date, p.erp_reference
  • Buffers: shared hit=1746229 read=94642 dirtied=130
2. 1,601.414 11,729.236 ↓ 9.6 550,641 1

Sort (cost=2,053,454.07..2,053,596.88 rows=57,127 width=77) (actual time=11,651.809..11,729.236 rows=550,641 loops=1)

  • Sort Key: e.bid, e.description, (to_char(e.transaction_datetime_local, 'YYYY-MM-DD'::text)), e.payout_date, p.erp_reference
  • Sort Method: quicksort Memory: 101969kB
  • Buffers: shared hit=1746229 read=94642 dirtied=130
3. 9,502.992 10,127.822 ↓ 9.6 550,641 1

Hash Join (cost=125,377.65..2,048,940.50 rows=57,127 width=77) (actual time=626.671..10,127.822 rows=550,641 loops=1)

  • Hash Cond: ((e.bid)::text = (t.object_id)::text)
  • Buffers: shared hit=1746226 read=94642 dirtied=130
  • -> Index Scan using account_entry_transaction_datetime_local_description_idx on account_entry e (cost=0.70..1916137.53 rows=418800 width=45) (actual time=1.637..8659.119 rows=2974715 loop
  • Index Cond: ((transaction_datetime_local >= '2019-11-02 00:00:00'::timestamp without time zone) AND (transaction_datetime_local < '2019-11-03 00:00:00'::timestamp without time zone) A
  • Filter: ((transaction_datetime_local < payout_date) AND ((status)::text = 'OPEN'::text))
  • Rows Removed by Filter: 24230
  • Buffers: shared hit=1745955 read=37797 dirtied=130
4. 5.709 624.830 ↓ 1.1 31,829 1

Hash (cost=122,538.01..122,538.01 rows=27,697 width=22) (actual time=624.830..624.830 rows=31,829 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1913kB
  • Buffers: shared hit=271 read=56845
5. 36.342 619.121 ↓ 1.1 31,829 1

Hash Join (cost=98,409.19..122,538.01 rows=27,697 width=22) (actual time=542.895..619.121 rows=31,829 loops=1)

  • Hash Cond: ((t.object_id)::text = (p.bid)::text)
  • Buffers: shared hit=271 read=56845
6. 40.149 40.149 ↓ 1.0 202,846 1

Seq Scan on toggle t (cost=0.00..23,596.45 rows=202,809 width=7) (actual time=0.032..40.149 rows=202,846 loops=1)

  • Filter: (((object_type)::text = 'PERSON'::text) AND ((name)::text = 'AUTO_PAYOUT'::text) AND ((val)::text = 'S'::text))
  • Rows Removed by Filter: 200
  • Buffers: shared hit=2 read=1767
7. 23.674 542.630 ↑ 1.0 88,808 1

Hash (cost=89,168.81..89,168.81 rows=90,150 width=15) (actual time=542.630..542.630 rows=88,808 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 5141kB
  • Buffers: shared hit=269 read=55078
8. 429.549 518.956 ↑ 1.0 88,808 1

Bitmap Heap Scan on person p (cost=2,633.09..89,168.81 rows=90,150 width=15) (actual time=98.010..518.956 rows=88,808 loops=1)

  • Recheck Cond: (erp_reference IS NOT NULL)
  • Heap Blocks: exact=54616
  • Buffers: shared hit=269 read=55078
9. 89.407 89.407 ↓ 1.0 90,928 1

Bitmap Index Scan on person_erp_reference_idx (cost=0.00..2,610.55 rows=90,150 width=0) (actual time=89.407..89.407 rows=90,928 loops=1)

  • Index Cond: (erp_reference IS NOT NULL)
  • Buffers: shared hit=2 read=729
Planning time : 1.529 ms
Execution time : 11,955.723 ms