explain.depesz.com

PostgreSQL's explain analyze made readable

Result: m4Us

Settings
# exclusive inclusive rows x rows loops node
1. 0.037 5,324.222 ↑ 28.4 12 1

Subquery Scan on t1 (cost=1,090,578.62..1,090,598.23 rows=341 width=144) (actual time=5,324.188..5,324.222 rows=12 loops=1)

2. 671.876 5,324.185 ↑ 28.4 12 1

HashAggregate (cost=1,090,578.62..1,090,588.85 rows=341 width=20) (actual time=5,324.174..5,324.185 rows=12 loops=1)

  • Group Key: date_part('year'::text, (s.invoice_date)::timestamp without time zone), date_part('month'::text, (s.invoice_date)::timestamp without time zone)
3. 1,410.876 4,652.309 ↓ 19.5 316,836 1

Hash Right Join (cost=1,089,625.07..1,090,091.87 rows=16,225 width=20) (actual time=3,256.359..4,652.309 rows=316,836 loops=1)

  • Hash Cond: (o.order_number = s.invoice_no)
  • Join Filter: (s.type_id = ANY ('{18,25}'::integer[]))
  • Filter: (COALESCE(o.branch_code, s.branch_code) = 'CW'::bpchar)
  • Rows Removed by Filter: 2,929,435
4. 0.002 0.013 ↓ 0.0 0 1

HashAggregate (cost=342,615.36..342,619.70 rows=434 width=7) (actual time=0.013..0.013 rows=0 loops=1)

  • Group Key: o.order_number, o.branch_code
5. 0.011 0.011 ↓ 0.0 0 1

Result (cost=514.95..342,613.19 rows=434 width=7) (actual time=0.011..0.011 rows=0 loops=1)

  • One-Time Filter: ('CW'::text = ANY ('{CC,CP}'::text[]))
6. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=514.95..342,613.19 rows=434 width=7) (never executed)

7. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on orders o (cost=514.39..73,442.98 rows=17,054 width=7) (never executed)

  • Recheck Cond: (branch_code = 'CW'::bpchar)
  • Filter: (((order_state)::text = ANY ('{INVOICED,REFUNDED}'::text[])) AND ((order_type)::text = ANY ('{order,backorder,quote,proforma}'::text[])))
8. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on orders_branch_code (cost=0.00..510.12 rows=27,426 width=0) (never executed)

  • Index Cond: (branch_code = 'CW'::bpchar)
9. 0.000 0.000 ↓ 0.0 0

Index Scan using stocksales_invoice_no_invoice_date_idx on stocksales s_1 (cost=0.56..15.77 rows=1 width=4) (never executed)

  • Index Cond: ((invoice_no = o.order_number) AND (invoice_date >= '2019-01-01'::date) AND (invoice_date <= '2019-12-31'::date))
  • Filter: ((type_id = ANY ('{18,25}'::integer[])) AND (branch_code = 'CW'::bpchar))
10. 1,311.098 3,241.420 ↓ 1.0 3,246,271 1

Hash (cost=706,446.09..706,446.09 rows=3,245,090 width=24) (actual time=3,241.420..3,241.420 rows=3,246,271 loops=1)

  • Buckets: 4,194,304 Batches: 1 Memory Usage: 218,220kB
11. 1,559.109 1,930.322 ↓ 1.0 3,246,271 1

Bitmap Heap Scan on stocksales s (cost=68,858.74..706,446.09 rows=3,245,090 width=24) (actual time=385.833..1,930.322 rows=3,246,271 loops=1)

  • Recheck Cond: ((invoice_date >= '2019-01-01'::date) AND (invoice_date <= '2019-12-31'::date))
  • Heap Blocks: exact=49,340
12. 371.213 371.213 ↓ 1.0 3,246,271 1

Bitmap Index Scan on ssales_invoice_date_idx (cost=0.00..68,047.46 rows=3,245,090 width=0) (actual time=371.213..371.213 rows=3,246,271 loops=1)

  • Index Cond: ((invoice_date >= '2019-01-01'::date) AND (invoice_date <= '2019-12-31'::date))
Planning time : 26.320 ms
Execution time : 5,329.157 ms