explain.depesz.com

PostgreSQL's explain analyze made readable

Result: huFo

Settings
# exclusive inclusive rows x rows loops node
1. 60.669 7,776.190 ↓ 6.8 20,225 1

HashAggregate (cost=596,384.04..596,413.62 rows=2,958 width=7) (actual time=7,770.352..7,776.190 rows=20,225 loops=1)

  • Group Key: o.order_number, o.branch_code
2. 22.502 7,715.521 ↓ 39.1 115,725 1

Result (cost=444,456.92..596,369.25 rows=2,958 width=7) (actual time=7,556.216..7,715.521 rows=115,725 loops=1)

  • One-Time Filter: ('CC'::text = ANY ('{CC,CP}'::text[]))
3. 85.093 7,693.019 ↓ 39.1 115,725 1

Hash Join (cost=444,456.92..596,369.25 rows=2,958 width=7) (actual time=7,556.212..7,693.019 rows=115,725 loops=1)

  • Hash Cond: (s.invoice_no = o.order_number)
4. 53.382 2,077.615 ↓ 3.4 170,519 1

Bitmap Heap Scan on stocksales s (cost=255,697.10..407,392.50 rows=49,962 width=4) (actual time=2,025.519..2,077.615 rows=170,519 loops=1)

  • Recheck Cond: ((branch_code = 'CW'::bpchar) AND (invoice_date >= '2019-01-01'::date) AND (invoice_date <= '2019-12-31'::date) AND (type_id = ANY ('{18,25}'::integer[])))
  • Heap Blocks: exact=4490
5. 44.880 2,024.233 ↓ 0.0 0 1

BitmapAnd (cost=255,697.10..255,697.10 rows=49,962 width=0) (actual time=2,024.233..2,024.233 rows=0 loops=1)

6. 498.706 498.706 ↑ 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=498.706..498.706 rows=3,565,975 loops=1)

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

  • Index Cond: ((invoice_date >= '2019-01-01'::date) AND (invoice_date <= '2019-12-31'::date))
8. 1,095.220 1,095.220 ↓ 1.0 6,918,380 1

Bitmap Index Scan on stocksales_type_id_idx (cost=0.00..121,735.92 rows=6,918,237 width=0) (actual time=1,095.220..1,095.220 rows=6,918,380 loops=1)

  • Index Cond: (type_id = ANY ('{18,25}'::integer[]))
9. 78.658 5,530.311 ↓ 1.3 154,469 1

Hash (cost=187,306.79..187,306.79 rows=116,242 width=7) (actual time=5,530.311..5,530.311 rows=154,469 loops=1)

  • Buckets: 262144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 7932kB
10. 5,379.800 5,451.653 ↓ 1.3 154,469 1

Bitmap Heap Scan on orders o (cost=3,487.56..187,306.79 rows=116,242 width=7) (actual time=118.522..5,451.653 rows=154,469 loops=1)

  • Recheck Cond: (branch_code = 'CC'::bpchar)
  • Filter: (((order_state)::text = ANY ('{INVOICED,REFUNDED}'::text[])) AND ((order_type)::text = ANY ('{order,backorder,quote,proforma}'::text[])))
  • Rows Removed by Filter: 35388
  • Heap Blocks: exact=113383
11. 71.853 71.853 ↓ 1.0 189,857 1

Bitmap Index Scan on orders_branch_code (cost=0.00..3,458.50 rows=186,943 width=0) (actual time=71.853..71.853 rows=189,857 loops=1)

  • Index Cond: (branch_code = 'CC'::bpchar)
Planning time : 2.344 ms
Execution time : 7,777.867 ms