explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uHAW

Settings
# exclusive inclusive rows x rows loops node
1. 2.460 236,712.123 ↑ 2.3 2,565 1

Sort (cost=5,464,198.54..5,464,213.59 rows=6,022 width=59) (actual time=236,712.047..236,712.123 rows=2,565 loops=1)

  • Sort Key: (sum(CASE WHEN ((m.transaction_type)::text = ANY ('{POSSALE,POSCN,POSREF}'::text[])) THEN l.quantity ELSE NULL::numeric END))
  • Sort Method: quicksort Memory: 450kB
2. 925.409 236,709.663 ↑ 2.3 2,565 1

HashAggregate (cost=5,463,669.93..5,463,820.48 rows=6,022 width=59) (actual time=236,707.166..236,709.663 rows=2,565 loops=1)

  • Group Key: btrim(upper((l.master_code)::text), ' '::text)
3. 206,997.964 235,784.254 ↓ 3.5 326,489 1

Hash Join (cost=94,591.37..5,459,902.65 rows=94,182 width=59) (actual time=44,692.651..235,784.254 rows=326,489 loops=1)

  • Hash Cond: ((l.link_guid)::text = (m.guid)::text)
4. 28,276.858 28,276.858 ↓ 1.0 98,144,427 1

Seq Scan on transaction_line_items l (cost=0.00..3,899,718.82 rows=97,611,982 width=89) (actual time=0.039..28,276.858 rows=98,144,427 loops=1)

5. 96.877 509.432 ↓ 5.9 230,004 1

Hash (cost=94,102.15..94,102.15 rows=39,137 width=44) (actual time=509.432..509.432 rows=230,004 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 17288kB
6. 412.555 412.555 ↓ 5.9 230,004 1

Index Scan using tm_datebranchtype on transaction_master m (cost=0.56..94,102.15 rows=39,137 width=44) (actual time=0.081..412.555 rows=230,004 loops=1)

  • Index Cond: ((sale_date >= '2019-09-26'::date) AND (sale_date <= '2019-10-02'::date))
  • Filter: (((transaction_type)::text = 'POSSALE'::text) OR ((transaction_type)::text = 'POSCN'::text) OR ((transaction_type)::text = 'POSREF'::text))
  • Rows Removed by Filter: 71698
Planning time : 5.072 ms
Execution time : 236,717.027 ms