explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CkXK

Settings
# exclusive inclusive rows x rows loops node
1. 0.036 6,012.352 ↑ 2.6 12 1

Subquery Scan on t1 (cost=1,232,451.60..1,232,453.39 rows=31 width=144) (actual time=6,012.318..6,012.352 rows=12 loops=1)

2. 212.359 6,012.316 ↑ 2.6 12 1

HashAggregate (cost=1,232,451.60..1,232,452.53 rows=31 width=20) (actual time=6,012.304..6,012.316 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. 223.004 5,799.957 ↓ 80.0 115,725 1

Hash Right Join (cost=1,230,440.59..1,232,408.22 rows=1,446 width=20) (actual time=5,569.513..5,799.957 rows=115,725 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) = 'CC'::bpchar)
  • Rows Removed by Filter: 201,111
4. 64.293 3,392.233 ↓ 6.8 20,225 1

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

  • Group Key: o.order_number, o.branch_code
5. 22.532 3,327.940 ↓ 39.1 115,725 1

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

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

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

  • Hash Cond: (s_1.invoice_no = o.order_number)
7. 108.532 2,112.591 ↓ 3.4 170,519 1

Bitmap Heap Scan on stocksales s_1 (cost=255,697.10..407,392.50 rows=49,962 width=4) (actual time=2,005.857..2,112.591 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=4,490
8. 68.278 2,004.059 ↓ 0.0 0 1

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

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

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

  • Index Cond: ((invoice_date >= '2019-01-01'::date) AND (invoice_date <= '2019-12-31'::date))
11. 978.821 978.821 ↓ 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=978.821..978.821 rows=6,918,380 loops=1)

  • Index Cond: (type_id = ANY ('{18,25}'::integer[]))
12. 73.707 1,106.816 ↓ 1.3 154,469 1

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

  • Buckets: 262,144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 7,932kB
13. 980.260 1,033.109 ↓ 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=96.351..1,033.109 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: 35,388
  • Heap Blocks: exact=113,383
14. 52.849 52.849 ↓ 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=52.849..52.849 rows=189,857 loops=1)

  • Index Cond: (branch_code = 'CC'::bpchar)
15. 120.990 2,184.720 ↓ 1.1 316,836 1

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 21,792kB
16. 1,066.782 2,063.730 ↓ 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=999.535..2,063.730 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=6,883
17. 75.110 996.948 ↓ 0.0 0 1

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

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

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

  • Index Cond: ((invoice_date >= '2019-01-01'::date) AND (invoice_date <= '2019-12-31'::date))
Planning time : 3.828 ms
Execution time : 6,012.661 ms