explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DQJY

Settings
# exclusive inclusive rows x rows loops node
1. 407.326 423,715.456 ↓ 8.5 93,518 1

Sort (cost=1,007,796.19..1,007,823.69 rows=11,000 width=167) (actual time=423,695.236..423,715.456 rows=93,518 loops=1)

  • Sort Key: fs.fit_number
  • Sort Method: quicksort Memory: 27498kB
2. 198.703 423,308.130 ↓ 8.5 93,518 1

Hash Left Join (cost=95,564.89..1,007,057.81 rows=11,000 width=167) (actual time=21,608.693..423,308.130 rows=93,518 loops=1)

  • Hash Cond: ((fs.location_id)::text = (dp.dep_id)::text)
  • Join Filter: ((fs.location_type)::text = 'SITE'::text)
  • Rows Removed by Join Filter: 72
3. 152.525 423,030.003 ↓ 8.5 93,518 1

Hash Left Join (cost=95,344.33..1,006,277.53 rows=11,000 width=167) (actual time=21,529.185..423,030.003 rows=93,518 loops=1)

  • Hash Cond: ((ac.code = cs.acc_code) AND ((fs.location_id)::text = (cs.site_id)::text))
  • Join Filter: ((fs.location_type)::text = ANY ('{CUST,REP}'::text[]))
  • Rows Removed by Join Filter: 10627
4. 131.951 422,621.238 ↓ 8.5 93,518 1

Hash Left Join (cost=94,773.27..1,004,830.90 rows=11,000 width=167) (actual time=21,272.919..422,621.238 rows=93,518 loops=1)

  • Hash Cond: (ex.et_code = et.code)
5. 158.479 422,488.921 ↓ 8.5 93,518 1

Nested Loop Left Join (cost=94,760.52..1,004,666.90 rows=11,000 width=158) (actual time=21,272.528..422,488.921 rows=93,518 loops=1)

6. 549.877 176,014.566 ↓ 546.9 93,514 1

Nested Loop Left Join (cost=94,751.18..1,002,831.74 rows=171 width=154) (actual time=21,192.154..176,014.566 rows=93,514 loops=1)

  • Join Filter: (((fs.location_type)::text = 'ENG'::text) AND (ag.acc_code = ac.code))
  • Rows Removed by Join Filter: 24
7. 2,065.166 168,264.111 ↓ 546.9 93,514 1

Hash Join (cost=94,750.76..1,001,826.27 rows=171 width=154) (actual time=21,074.672..168,264.111 rows=93,514 loops=1)

  • Hash Cond: (CASE WHEN (((fs.location_type)::text = 'SITE'::text) AND ((fs.is_dc)::text = '1'::text) AND ((fs.location_status)::text = 'ACT'::text) AND ((fs.stock_status)::text = 'STOCK'::text)) THEN 1 WHEN (((fs.location_type)::text = 'SITE'::text) AND ((fs.is_dc)::text = '1'::text) AND ((fs.location_status)::text = ANY ('{ACT,EXP}'::text[])) AND ((fs.servicename)::text <> 'REPLENReturnEx'::text) AND ((fs.stock_status)::text = 'LOST'::text)) THEN 1 WHEN (((fs.location_type)::text = 'SITE'::text) AND ((fs.is_dc)::text = '1'::text) AND ((fs.location_status)::text = 'ACT'::text) AND ((fs.stock_status)::text = 'HOLD'::text)) THEN 2 WHEN (((fs.location_type)::text = 'SITE'::text) AND ((fs.is_dc)::text = '1'::text) AND ((fs.location_status)::text = ANY ('{ACT,EXP}'::text[])) AND ((fs.servicename)::text = 'REPLENReturnEx'::text) AND ((fs.stock_status)::text = 'LOST'::text)) THEN 2 WHEN (((fs.location_type)::text = 'SITE'::text) AND ((fs.is_fsl)::text = '1'::text) AND ((fs.is_dc)::text = '1'::text) AND ((fs.location_status)::text = ANY ('{ACT,EXP}'::text[])) AND ((fs.stock_status)::text = 'INBOUND'::text)) THEN 3 WHEN (((fs.location_type)::text = 'SITE'::text) AND ((fs.is_fsl)::text = '1'::text) AND ((fs.location_status)::text = 'ACT'::text) AND ((fs.stock_status)::text = 'STOCK'::text)) THEN 4 WHEN (((fs.location_type)::text = 'SITE'::text) AND ((fs.is_fsl)::text = '1'::text) AND ((fs.location_status)::text = 'ACT'::text) AND ((fs.stock_status)::text = 'HOLD'::text)) THEN 4 WHEN (((fs.location_type)::text = 'SITE'::text) AND ((fs.is_fsl)::text = '1'::text) AND ((fs.location_status)::text = ANY ('{ACT,EXP}'::text[])) AND ((fs.stock_status)::text = 'LOST'::text)) THEN 4 WHEN (((fs.location_type)::text = 'SITE'::text) AND ((fs.is_fsl)::text = '1'::text) AND ((fs.is_dc)::text = '0'::text) AND ((fs.location_status)::text = ANY ('{ACT,EXP}'::text[])) AND ((fs.stock_status)::text = 'INBOUND'::text)) THEN 5 WHEN (((fs.location_type)::text = 'REP'::text) AND ((fs.location_status)::text = 'EXP'::text)) THEN 6 WHEN (((fs.location_type)::text = 'REP'::text) AND ((fs.location_status)::text = 'ACT'::text)) THEN 7 WHEN (((fs.location_type)::text = 'REP'::text) AND ((fs.location_status)::text = 'USED'::text)) THEN 8 WHEN (((fs.location_type)::text = 'SITE'::text) AND ((fs.location_status)::text = 'EXP'::text) AND ((fs.req_type)::text = 'PUDO'::text)) THEN 9 WHEN (((fs.location_type)::text = 'SITE'::text) AND ((fs.location_status)::text = 'ACT'::text) AND ((fs.req_type)::text = 'PUDO'::text)) THEN 10 WHEN (((fs.location_type)::text = 'ENG'::text) AND ((fs.location_status)::text = 'EXP'::text) AND ((fs.req_type)::text = 'PICK'::text)) THEN 10 WHEN (((fs.location_type)::text = 'ENG'::text) AND ((fs.location_status)::text = 'ACT'::text)) THEN 11 WHEN (((fs.location_type)::text = 'CUST'::text) AND ((fs.location_status)::text = 'ACT'::text)) THEN 12 WHEN (((fs.location_type)::text = 'CUST'::text) AND ((fs.location_status)::text = 'LOAN'::text)) THEN 13 WHEN ((((fs.req_type)::text = 'RTRN'::text) OR ((fs.stock_status)::text = 'RETURNED'::text)) AND ((fs.location_status)::text <> ALL ('{CAN,USED}'::text[]))) THEN 14 WHEN (((fs.location_type)::text = 'CUST'::text) AND ((fs.location_status)::text = 'EXP'::text)) THEN 15 WHEN (((fs.location_status)::text <> ALL ('{CAN,USED}'::text[])) AND ((fs.stock_status)::text <> 'CANCELLED'::text)) THEN 16 ELSE 0 END = fsc.id)
8. 1,785.224 166,198.919 ↓ 149.7 320,290 1

Hash Join (cost=94,749.40..1,001,633.36 rows=2,139 width=143) (actual time=21,074.552..166,198.919 rows=320,290 loops=1)

  • Hash Cond: ((fs.acc_nr)::text = (ac.acc_nr)::text)
9. 143,425.282 164,413.674 ↓ 1.0 4,099,935 1

Bitmap Heap Scan on fit_snapshots fs (cost=94,741.09..986,268.45 rows=4,089,390 width=139) (actual time=21,074.478..164,413.674 rows=4,099,935 loops=1)

  • Recheck Cond: (day = '2018-07-18'::date)
  • Filter: ((po_code IS NULL) OR ((stock_status)::text <> 'INBOUND'::text))
  • Rows Removed by Filter: 1223
  • Heap Blocks: exact=123254
10. 20,988.392 20,988.392 ↓ 1.0 4,101,158 1

Bitmap Index Scan on fit_snapshots_pkey (cost=0.00..93,718.74 rows=4,092,024 width=0) (actual time=20,988.392..20,988.392 rows=4,101,158 loops=1)

  • Index Cond: (day = '2018-07-18'::date)
11. 0.003 0.021 ↑ 1.0 1 1

Hash (cost=8.29..8.29 rows=1 width=11) (actual time=0.021..0.021 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.018 0.018 ↑ 1.0 1 1

Index Scan using accounts_pk on accounts ac (cost=0.28..8.29 rows=1 width=11) (actual time=0.017..0.018 rows=1 loops=1)

  • Index Cond: (code = 1122)
13. 0.012 0.026 ↑ 1.0 16 1

Hash (cost=1.16..1.16 rows=16 width=19) (actual time=0.026..0.026 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.014 0.014 ↑ 1.0 16 1

Seq Scan on fit_snapshot_categories fsc (cost=0.00..1.16 rows=16 width=19) (actual time=0.009..0.014 rows=16 loops=1)

  • Filter: (description IS NOT NULL)
15. 7,200.578 7,200.578 ↓ 0.0 0 93,514

Index Scan using agents_agent_id_in on agents ag (cost=0.42..5.86 rows=1 width=10) (actual time=0.075..0.077 rows=0 loops=93,514)

  • Index Cond: (lower((agent_id)::text) = lower((fs.location_id)::text))
  • Filter: (acc_code = 1122)
  • Rows Removed by Filter: 3
16. 329.453 246,315.876 ↓ 0.0 0 93,514

Hash Semi Join (cost=9.34..19.38 rows=64 width=12) (actual time=2.628..2.634 rows=0 loops=93,514)

  • Hash Cond: (ex.et_code = except_types.code)
17. 245,941.820 245,941.820 ↑ 164.0 1 93,514

Index Only Scan using exceptions_i4 on exceptions ex (cost=0.70..9.59 rows=164 width=12) (actual time=2.610..2.630 rows=1 loops=93,514)

  • Index Cond: (cons_code = (fs.request_no)::bigint)
  • Heap Fetches: 0
18. 0.038 44.603 ↑ 1.0 51 1

Hash (cost=8.01..8.01 rows=51 width=4) (actual time=44.603..44.603 rows=51 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
19. 44.565 44.565 ↑ 1.0 51 1

Seq Scan on except_types (cost=0.00..8.01 rows=51 width=4) (actual time=19.461..44.565 rows=51 loops=1)

  • Filter: ((id)::text ~~ 'R%'::text)
  • Rows Removed by Filter: 190
20. 0.136 0.366 ↑ 1.0 241 1

Hash (cost=9.73..9.73 rows=241 width=17) (actual time=0.366..0.366 rows=241 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
21. 0.147 0.230 ↑ 1.0 241 1

Hash Left Join (cost=1.18..9.73 rows=241 width=17) (actual time=0.050..0.230 rows=241 loops=1)

  • Hash Cond: (et.eg_code = eg.code)
22. 0.053 0.053 ↑ 1.0 241 1

Seq Scan on except_types et (cost=0.00..7.41 rows=241 width=12) (actual time=0.005..0.053 rows=241 loops=1)

23. 0.020 0.030 ↑ 1.0 8 1

Hash (cost=1.08..1.08 rows=8 width=13) (actual time=0.030..0.030 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 0.010 0.010 ↑ 1.0 8 1

Seq Scan on exception_groups eg (cost=0.00..1.08 rows=8 width=13) (actual time=0.005..0.010 rows=8 loops=1)

25. 1.055 256.240 ↑ 1.0 1,497 1

Hash (cost=548.60..548.60 rows=1,497 width=12) (actual time=256.240..256.240 rows=1,497 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 81kB
26. 176.595 255.185 ↑ 1.0 1,497 1

Bitmap Heap Scan on customer_sites cs (cost=31.89..548.60 rows=1,497 width=12) (actual time=93.381..255.185 rows=1,497 loops=1)

  • Recheck Cond: (acc_code = 1122)
  • Heap Blocks: exact=142
27. 78.590 78.590 ↑ 1.0 1,497 1

Bitmap Index Scan on customer_sites_acc_code_in (cost=0.00..31.52 rows=1,497 width=0) (actual time=78.590..78.590 rows=1,497 loops=1)

  • Index Cond: (acc_code = 1122)
28. 2.208 79.424 ↑ 1.0 5,392 1

Hash (cost=153.16..153.16 rows=5,392 width=7) (actual time=79.424..79.424 rows=5,392 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 270kB
29. 77.216 77.216 ↑ 1.0 5,392 1

Index Only Scan using depots_i on depots dp (cost=0.28..153.16 rows=5,392 width=7) (actual time=0.108..77.216 rows=5,392 loops=1)

  • Heap Fetches: 0
Planning time : 20.020 ms
Execution time : 423,727.316 ms