explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8kGZ

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 84.713 7,170.498 ↓ 6.8 1,353 1

GroupAggregate (cost=378,770.36..404,079.89 rows=200 width=44) (actual time=6,556.081..7,170.498 rows=1,353 loops=1)

  • Group Key: (date_trunc('HOUR'::text, o.creation_date))
  • Buffers: shared hit=1472809 read=114569 dirtied=22728, temp read=1006 written=1006
2. 490.298 7,085.785 ↓ 1.1 159,630 1

GroupAggregate (cost=378,770.36..399,610.62 rows=148,859 width=36) (actual time=6,555.938..7,085.785 rows=159,630 loops=1)

  • Group Key: (date_trunc('HOUR'::text, o.creation_date)), o.number, evtpyxis.order_id
  • Buffers: shared hit=1472809 read=114569 dirtied=22728, temp read=1006 written=1006
3. 410.003 6,595.487 ↓ 1.1 164,832 1

Sort (cost=378,770.36..379,142.51 rows=148,859 width=36) (actual time=6,555.857..6,595.487 rows=164,832 loops=1)

  • Sort Key: (date_trunc('HOUR'::text, o.creation_date)), o.number, evtpyxis.order_id
  • Sort Method: external merge Disk: 8040kB
  • Buffers: shared hit=1472809 read=114569 dirtied=22728, temp read=1006 written=1006
4. 61.143 6,185.484 ↓ 1.1 164,832 1

Nested Loop (cost=33,997.39..365,980.70 rows=148,859 width=36) (actual time=2,108.997..6,185.484 rows=164,832 loops=1)

  • Buffers: shared hit=1472798 read=114569 dirtied=22728
5. 72.686 5,645.313 ↓ 1.1 159,676 1

Nested Loop (cost=33,996.96..295,313.10 rows=145,929 width=36) (actual time=2,108.394..5,645.313 rows=159,676 loops=1)

  • Buffers: shared hit=831923 read=113748 dirtied=22722
6. 708.545 4,774.247 ↓ 1.1 159,676 1

Hash Right Join (cost=33,996.53..178,813.76 rows=145,929 width=32) (actual time=2,107.169..4,774.247 rows=159,676 loops=1)

  • Hash Cond: (evtpyxis.order_id = o.id)
  • Buffers: shared hit=247791 read=95705 dirtied=21839
7. 1,958.649 1,958.649 ↓ 1.0 523,009 1

Seq Scan on order_status_event evtpyxis (cost=0.00..137,514.59 rows=518,566 width=12) (actual time=0.020..1,958.649 rows=523,009 loops=1)

  • Filter: ((final_status)::text = 'RELEASED'::text)
  • Rows Removed by Filter: 4881867
  • Buffers: shared hit=10554 read=58488 dirtied=20088
8. 2,107.053 2,107.053 ↓ 1.1 159,676 1

Hash (cost=32,172.41..32,172.41 rows=145,929 width=24) (actual time=2,107.053..2,107.053 rows=159,676 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 8733kB
  • Buffers: shared hit=237237 read=37217 dirtied=1751
  • -> Index Scan using order_creation_date_index on order_ o (cost=0.43..32172.41 rows=145929 width=24) (actual time=0.709..2048.868 rows=
  • Index Cond: ((creation_date >= '2018-09-29 00:00:00'::timestamp without time zone) AND (creation_date <= '2018-11-30 00:00:00'::tim
  • Filter: (((type)::text = 'RE'::text) AND (date_part('hour'::text, creation_date) <> ALL ('{22,23}'::double precision[])))
  • Rows Removed by Filter: 138000
  • Buffers: shared hit=237237 read=37217 dirtied=1751
9. 798.380 798.380 ↑ 1.0 1 159,676

Index Only Scan using project_pkey on project p (cost=0.43..0.79 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=159,676)

  • Index Cond: (id = o.project_id)
  • Heap Fetches: 121669
  • Buffers: shared hit=584132 read=18043 dirtied=883
10. 479.028 479.028 ↑ 1.0 1 159,676

Index Scan using index_payment_project_id on payment pay (cost=0.43..0.47 rows=1 width=12) (actual time=0.002..0.003 rows=1 loops=159,676)

  • Index Cond: (project_id = p.id)
  • Filter: initial
  • Buffers: shared hit=640875 read=821 dirtied=6