explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vutF : Optimization for: plan #fQwW

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.782 109,841.931 ↓ 1.6 3,492 1

Unique (cost=55,602,697.10..55,602,730.57 rows=2,231 width=52) (actual time=109,840.998..109,841.931 rows=3,492 loops=1)

2. 16.064 109,841.149 ↓ 1.6 3,492 1

Sort (cost=55,602,697.10..55,602,702.68 rows=2,231 width=52) (actual time=109,840.997..109,841.149 rows=3,492 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: 369kB
3. 25.029 109,825.085 ↓ 1.6 3,492 1

Nested Loop (cost=1,005.41..55,602,573.02 rows=2,231 width=52) (actual time=212.012..109,825.085 rows=3,492 loops=1)

  • Join Filter: ((ordmst.prgcod)::text = (usrprg.prgcod)::text)
  • Rows Removed by Join Filter: 222025
4. 0.000 221.948 ↓ 8.9 3,492 1

Gather (cost=1,005.13..552,718.64 rows=391 width=36) (actual time=211.947..221.948 rows=3,492 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 1.856 635.633 ↓ 7.1 1,164 3 / 3

Nested Loop Left Join (cost=5.13..551,679.54 rows=163 width=36) (actual time=208.526..635.633 rows=1,164 loops=3)

6. 2.933 620.973 ↓ 12.8 1,164 3 / 3

Nested Loop (cost=4.57..551,199.53 rows=91 width=34) (actual time=208.497..620.973 rows=1,164 loops=3)

7. 593.956 593.956 ↓ 8.5 2,007 3 / 3

Parallel Seq Scan on pltodrmst ordmst (cost=0.00..549,178.76 rows=235 width=14) (actual time=208.433..593.956 rows=2,007 loops=3)

  • Filter: ((odrdat >= '2020-03-19 00:00:00'::timestamp without time zone) AND ((odrsta)::text = 'F'::text))
  • Rows Removed by Filter: 2002578
8. 4.014 24.084 ↑ 1.0 1 6,021 / 3

Bitmap Heap Scan on pltodrlne ordlin (cost=4.57..8.59 rows=1 width=20) (actual time=0.012..0.012 rows=1 loops=6,021)

  • 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=1
9. 20.070 20.070 ↑ 1.0 1 6,021 / 3

Bitmap Index Scan on i_odrlne_1 (cost=0.00..4.57 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=6,021)

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

Index Scan using pltodrlnedet_pkey on pltodrlnedet lnedet (cost=0.56..5.27 rows=1 width=20) (actual time=0.011..0.011 rows=1 loops=3,492)

  • Index Cond: (((ordlin.prgcod)::text = (prgcod)::text) AND ((ordlin.odrnum)::text = (odrnum)::text) AND (ordlin.txnidr = txnidr) AND ((backpost)::text = 'N'::text))
11. 10.430 10.476 ↑ 1.3 65 3,492

Materialize (cost=0.29..10.24 rows=87 width=6) (actual time=0.000..0.003 rows=65 loops=3,492)

12. 0.046 0.046 ↑ 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.033..0.046 rows=65 loops=1)

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

SubPlan (for Nested Loop)

14. 4.419 109,567.632 ↓ 0.0 0 1,473

Limit (cost=0.00..24,674.75 rows=1 width=32) (actual time=74.384..74.384 rows=0 loops=1,473)

15. 109,563.213 109,563.213 ↓ 0.0 0 1,473

Seq Scan on pltvaldocmst valdoc (cost=0.00..24,674.75 rows=1 width=32) (actual time=74.381..74.381 rows=0 loops=1,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: 467979
Planning time : 3.191 ms
Execution time : 109,842.328 ms