explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eFHgW

Settings
# exclusive inclusive rows x rows loops node
1. 0.038 2,386.043 ↑ 2.6 12 1

Subquery Scan on t1 (cost=977,016.31..977,018.09 rows=31 width=144) (actual time=2,386.010..2,386.043 rows=12 loops=1)

2. 567.064 2,386.005 ↑ 2.6 12 1

HashAggregate (cost=977,016.31..977,017.24 rows=31 width=20) (actual time=2,385.996..2,386.005 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. 378.040 1,818.941 ↓ 219.1 316,836 1

Hash Right Join (cost=976,671.90..976,972.93 rows=1,446 width=20) (actual time=1,442.774..1,818.941 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)
4. 0.003 0.012 ↓ 0.0 0 1

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

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

Result (cost=514.95..342,613.19 rows=434 width=7) (actual time=0.009..0.009 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. 113.650 1,440.889 ↓ 1.1 316,836 1

Hash (cost=630,442.56..630,442.56 rows=289,119 width=24) (actual time=1,440.889..1,440.889 rows=316,836 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 21792kB
11. 142.659 1,327.239 ↓ 1.1 316,836 1

Bitmap Heap Scan on stocksales s (cost=134,068.02..630,442.56 rows=289,119 width=24) (actual time=1,186.565..1,327.239 rows=316,836 loops=1)

  • Recheck Cond: ((branch_code = 'CW'::bpchar) AND (invoice_date >= '2019-01-01'::date) AND (invoice_date <= '2019-12-31'::date))
  • Heap Blocks: exact=6883
12. 42.526 1,184.580 ↓ 0.0 0 1

BitmapAnd (cost=134,068.02..134,068.02 rows=289,119 width=0) (actual time=1,184.580..1,184.580 rows=0 loops=1)

13. 783.394 783.394 ↑ 1.0 3,565,975 1

Bitmap Index Scan on ssales_branch_row_idx (cost=0.00..65,875.74 rows=3,566,824 width=0) (actual time=783.394..783.394 rows=3,565,975 loops=1)

  • Index Cond: (branch_code = 'CW'::bpchar)
14. 358.660 358.660 ↓ 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=358.660..358.660 rows=3,246,271 loops=1)

  • Index Cond: ((invoice_date >= '2019-01-01'::date) AND (invoice_date <= '2019-12-31'::date))
Planning time : 3.850 ms
Execution time : 2,386.321 ms