explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZKQ

Settings
# exclusive inclusive rows x rows loops node
1. 3.370 466,579.460 ↑ 2.0 15,327 1

Unique (cost=756,836,176.84..756,836,633.86 rows=30,468 width=52) (actual time=466,575.211..466,579.460 rows=15,327 loops=1)

2. 84.309 466,576.090 ↑ 2.0 15,327 1

Sort (cost=756,836,176.84..756,836,253.01 rows=30,468 width=52) (actual time=466,575.209..466,576.090 rows=15,327 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: 1582kB
3. 36.766 466,491.781 ↑ 2.0 15,327 1

Hash Join (cost=1,015.98..756,833,907.73 rows=30,468 width=52) (actual time=153.356..466,491.781 rows=15,327 loops=1)

  • Hash Cond: ((ordmst.prgcod)::text = (usrprg.prgcod)::text)
4. 0.000 214.183 ↓ 2.8 15,327 1

Gather (cost=1,005.09..586,888.59 rows=5,416 width=36) (actual time=152.749..214.183 rows=15,327 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 13.329 1,451.431 ↓ 2.3 5,109 3 / 3

Nested Loop Left Join (cost=5.09..585,346.99 rows=2,257 width=36) (actual time=149.032..1,451.431 rows=5,109 loops=3)

6. 12.137 738.169 ↓ 3.9 5,109 3 / 3

Nested Loop (cost=4.53..578,514.36 rows=1,295 width=34) (actual time=148.486..738.169 rows=5,109 loops=3)

7. 611.462 611.462 ↓ 1.9 6,365 3 / 3

Parallel Seq Scan on pltodrmst ordmst (cost=0.00..549,531.49 rows=3,386 width=14) (actual time=148.437..611.462 rows=6,365 loops=3)

  • Filter: ((odrdat >= '2020-03-19 00:00:00'::timestamp without time zone) AND ((odrsta)::text = 'F'::text))
  • Rows Removed by Filter: 2002800
8. 25.460 114.570 ↑ 1.0 1 19,095 / 3

Bitmap Heap Scan on pltodrlne ordlin (cost=4.53..8.55 rows=1 width=20) (actual time=0.017..0.018 rows=1 loops=19,095)

  • 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: 0
  • Heap Blocks: exact=4
9. 89.110 89.110 ↑ 1.0 1 19,095 / 3

Bitmap Index Scan on i_odrlne_1 (cost=0.00..4.53 rows=1 width=0) (actual time=0.014..0.014 rows=1 loops=19,095)

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

Index Scan using pltodrlnedet_pkey on pltodrlnedet lnedet (cost=0.56..5.28 rows=1 width=20) (actual time=0.137..0.137 rows=1 loops=15,327)

  • Index Cond: (((ordlin.prgcod)::text = (prgcod)::text) AND ((ordlin.odrnum)::text = (odrnum)::text) AND (ordlin.txnidr = txnidr) AND ((backpost)::text = 'N'::text))
11. 0.026 0.592 ↑ 1.0 86 1

Hash (cost=9.81..9.81 rows=87 width=6) (actual time=0.592..0.592 rows=86 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
12. 0.566 0.566 ↑ 1.0 86 1

Index Only Scan using pltusracsprg_pkey on pltusracsprg usrprg (cost=0.29..9.81 rows=87 width=6) (actual time=0.546..0.566 rows=86 loops=1)

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

SubPlan (for Hash Join)

14. 24.296 466,240.240 ↑ 1.0 1 6,074

Limit (cost=0.00..24,821.03 rows=1 width=32) (actual time=76.759..76.760 rows=1 loops=6,074)

15. 466,215.944 466,215.944 ↑ 1.0 1 6,074

Seq Scan on pltvaldocmst valdoc (cost=0.00..24,821.03 rows=1 width=32) (actual time=76.756..76.756 rows=1 loops=6,074)

  • 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: 472395
Planning time : 3.175 ms
Execution time : 466,580.476 ms