explain.depesz.com

PostgreSQL's explain analyze made readable

Result: F6Sx : Optimization for: plan #8kGZ

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 83.565 3,610.787 ↓ 6.8 1,353 1

GroupAggregate (cost=378,762.98..404,071.66 rows=200 width=44) (actual time=3,018.317..3,610.787 rows=1,353 loops=1)

  • Group Key: (date_trunc('HOUR'::text, o.creation_date))
  • Buffers: shared hit=1587604 dirtied=77
2. 489.289 3,527.222 ↓ 1.1 159,630 1

GroupAggregate (cost=378,762.98..399,602.54 rows=148,854 width=36) (actual time=3,018.195..3,527.222 rows=159,630 loops=1)

  • Group Key: (date_trunc('HOUR'::text, o.creation_date)), o.number, evtpyxis.order_id
  • Buffers: shared hit=1587604 dirtied=77
3. 349.523 3,037.933 ↓ 1.1 164,832 1

Sort (cost=378,762.98..379,135.12 rows=148,854 width=36) (actual time=3,018.136..3,037.933 rows=164,832 loops=1)

  • Sort Key: (date_trunc('HOUR'::text, o.creation_date)), o.number, evtpyxis.order_id
  • Sort Method: quicksort Memory: 19022kB
  • Buffers: shared hit=1587604 dirtied=77
4. 145.891 2,688.410 ↓ 1.1 164,832 1

Nested Loop (cost=33,996.20..365,973.79 rows=148,854 width=36) (actual time=372.303..2,688.410 rows=164,832 loops=1)

  • Buffers: shared hit=1587604 dirtied=77
5. 145.263 2,223.167 ↓ 1.1 159,676 1

Nested Loop (cost=33,995.76..295,308.60 rows=145,924 width=36) (actual time=372.287..2,223.167 rows=159,676 loops=1)

  • Buffers: shared hit=945908 dirtied=77
6. 631.769 1,758.552 ↓ 1.1 159,676 1

Hash Right Join (cost=33,995.33..178,812.56 rows=145,924 width=32) (actual time=372.261..1,758.552 rows=159,676 loops=1)

  • Hash Cond: (evtpyxis.order_id = o.id)
  • Buffers: shared hit=343285 dirtied=76
7. 754.567 754.567 ↓ 1.0 523,027 1

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

  • Filter: ((final_status)::text = 'RELEASED'::text)
  • Rows Removed by Filter: 4882575
  • Buffers: shared hit=69042 dirtied=19
8. 372.216 372.216 ↓ 1.1 159,676 1

Hash (cost=32,171.28..32,171.28 rows=145,924 width=24) (actual time=372.216..372.216 rows=159,676 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 8733kB
  • Buffers: shared hit=274243 dirtied=57
  • -> Index Scan using order_creation_date_index on order_ o (cost=0.43..32171.28 rows=145924 width=24) (actual time=0.029..326.813 rows=1
  • 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=274243 dirtied=57
9. 319.352 319.352 ↑ 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.002..0.002 rows=1 loops=159,676)

  • Index Cond: (id = o.project_id)
  • Heap Fetches: 122117
  • Buffers: shared hit=602623 dirtied=1
10. 319.352 319.352 ↑ 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.002 rows=1 loops=159,676)

  • Index Cond: (project_id = p.id)
  • Filter: initial
  • Buffers: shared hit=641696