explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fQwW

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 3.502 627,674.770 ↓ 7.4 16,446 1

Unique (cost=55,602,752.87..55,602,786.34 rows=2,231 width=52) (actual time=627,670.441..627,674.770 rows=16,446 loops=1)

2. 88.661 627,671.268 ↓ 7.4 16,446 1

Sort (cost=55,602,752.87..55,602,758.45 rows=2,231 width=52) (actual time=627,670.440..627,671.268 rows=16,446 loops=1)

  • Sort Key: ordmst.odrnum, ordlin.txnidr, ordlin.odrtyp, ordmst.prgcod, (CASE WHEN (lnedet.valdoctyp IS NOT NULL) THEN (SubPlan 1) ELSE NULL::character varying END)
  • Sort Method: quicksort Memory: 2053kB
3. 110.698 627,582.607 ↓ 7.4 16,446 1

Nested Loop (cost=1,005.41..55,602,628.79 rows=2,231 width=52) (actual time=257.827..627,582.607 rows=16,446 loops=1)

  • Join Filter: ((ordmst.prgcod)::text = (usrprg.prgcod)::text)
  • Rows Removed by Join Filter: 968183
4. 0.000 335.841 ↓ 42.1 16,446 1

Gather (cost=1,005.13..552,718.64 rows=391 width=36) (actual time=257.793..335.841 rows=16,446 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 10.149 835.471 ↓ 33.6 5,482 3 / 3

Nested Loop Left Join (cost=5.13..551,679.54 rows=163 width=36) (actual time=254.331..835.471 rows=5,482 loops=3)

6. 12.633 754.056 ↓ 60.2 5,482 3 / 3

Nested Loop (cost=4.57..551,199.53 rows=91 width=34) (actual time=254.305..754.056 rows=5,482 loops=3)

7. 606.998 606.998 ↓ 38.1 8,962 3 / 3

Parallel Seq Scan on pltodrmst ordmst (cost=0.00..549,178.76 rows=235 width=14) (actual time=254.073..606.998 rows=8,962 loops=3)

  • Filter: ((odrdat >= '2020-03-10 00:00:00'::timestamp without time zone) AND ((odrsta)::text = 'F'::text))
  • Rows Removed by Filter: 1995417
8. 26.885 134.425 ↑ 1.0 1 26,885 / 3

Bitmap Heap Scan on pltodrlne ordlin (cost=4.57..8.59 rows=1 width=20) (actual time=0.014..0.015 rows=1 loops=26,885)

  • Recheck Cond: (((odrnum)::text = (ordmst.odrnum)::text) AND ((prgcod)::text = (ordmst.prgcod)::text))
  • Filter: (((dlvsta)::text = 'S'::text) AND ((prgcod)::text = ANY ('{CHREX,ETH,PAMREX}'::text[])))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=6
9. 107.540 107.540 ↑ 1.0 1 26,885 / 3

Bitmap Index Scan on i_odrlne_1 (cost=0.00..4.57 rows=1 width=0) (actual time=0.012..0.012 rows=1 loops=26,885)

  • Index Cond: (((odrnum)::text = (ordmst.odrnum)::text) AND ((prgcod)::text = (ordmst.prgcod)::text))
10. 71.266 71.266 ↑ 1.0 1 16,446 / 3

Index Scan using pltodrlnedet_pkey on pltodrlnedet lnedet (cost=0.56..5.27 rows=1 width=20) (actual time=0.013..0.013 rows=1 loops=16,446)

  • Index Cond: (((ordlin.prgcod)::text = (prgcod)::text) AND ((ordlin.odrnum)::text = (odrnum)::text) AND (ordlin.txnidr = txnidr) AND ((backpost)::text = 'N'::text))
11. 49.307 49.338 ↑ 1.4 60 16,446

Materialize (cost=0.29..10.24 rows=87 width=6) (actual time=0.000..0.003 rows=60 loops=16,446)

12. 0.031 0.031 ↑ 1.3 65 1

Index Only Scan using pltusracsprg_pkey on pltusracsprg usrprg (cost=0.29..9.81 rows=87 width=6) (actual time=0.018..0.031 rows=65 loops=1)

  • Index Cond: (usrnam = 'Vajiya'::text)
  • Heap Fetches: 31
13.          

SubPlan (for Nested Loop)

14. 8.473 627,086.730 ↑ 1.0 1 8,473

Subquery Scan on tempval (cost=0.00..24,674.77 rows=1 width=32) (actual time=72.293..74.010 rows=1 loops=8,473)

  • Filter: (tempval.rnum = 1)
  • Rows Removed by Filter: 0
15. 50.838 627,078.257 ↑ 1.0 1 8,473

WindowAgg (cost=0.00..24,674.76 rows=1 width=40) (actual time=72.292..74.009 rows=1 loops=8,473)

16. 627,027.419 627,027.419 ↑ 1.0 1 8,473

Seq Scan on pltvaldocmst valdoc (cost=0.00..24,674.75 rows=1 width=2) (actual time=72.286..74.003 rows=1 loops=8,473)

  • Filter: (((prgcod)::text = (ordlin.prgcod)::text) AND ((ordnum)::text = (ordlin.odrnum)::text) AND (odrlnenum = ordlin.txnidr) AND ((valdoctyp)::text = (lnedet.valdoctyp)::text) AND ((valdocsts)::text = ANY ('{I,U,E,O}'::text[])))
  • Rows Removed by Filter: 473297
Planning time : 2.044 ms
Execution time : 627,675.555 ms