explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rVoC

Settings
# exclusive inclusive rows x rows loops node
1. 0.037 2,516.952 ↓ 200.0 200 1

Limit (cost=96,020.88..96,020.92 rows=1 width=376) (actual time=2,516.511..2,516.952 rows=200 loops=1)

  • Buffers: shared hit=475794
2.          

CTE rs

3. 12.354 1,123.625 ↓ 278.1 37,550 1

Unique (cost=94,905.46..94,907.15 rows=135 width=23) (actual time=1,106.885..1,123.625 rows=37,550 loops=1)

  • Buffers: shared hit=105617
4. 106.034 1,111.271 ↓ 278.1 37,550 1

Sort (cost=94,905.46..94,905.80 rows=135 width=23) (actual time=1,106.883..1,111.271 rows=37,550 loops=1)

  • Sort Key: usvfa6pf_1.a6opco, usvfa6pf_1.a6vnbr, usvfa6pf_1.a6adcd, immatwtf_rs.twbygp
  • Sort Method: quicksort Memory: 4470kB
  • Buffers: shared hit=105617
5. 6.450 1,005.237 ↓ 278.1 37,550 1

Gather (cost=69,182.57..94,900.69 rows=135 width=23) (actual time=874.299..1,005.237 rows=37,550 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=105617
6. 49.306 998.787 ↓ 223.5 12,517 3 / 3

Merge Join (cost=68,182.57..93,887.19 rows=56 width=23) (actual time=904.181..998.787 rows=12,517 loops=3)

  • Merge Cond: (((usvfa6pf_1.a6opco)::text = (immatwtf_rs.twopco)::text) AND ((usvfa6pf_1.a6vnbr)::text = (immatwtf_rs.vendor)::text) AND (usvfa6pf_1.a6adcd = immatwtf_rs.shippoint))
  • Buffers: shared hit=105617
7. 40.098 40.098 ↑ 1.2 206,165 3 / 3

Parallel Index Only Scan using ix_usvfa6pf_ix_usvfa6pf on usvfa6pf usvfa6pf_1 (cost=0.42..22,641.18 rows=256,398 width=20) (actual time=0.327..40.098 rows=206,165 loops=3)

  • Heap Fetches: 9022
  • Buffers: shared hit=5048
8. 157.197 909.383 ↑ 3.0 37,665 3 / 3

Sort (cost=68,182.14..68,467.22 rows=114,032 width=21) (actual time=903.830..909.383 rows=37,665 loops=3)

  • Sort Key: immatwtf_rs.twopco, immatwtf_rs.vendor, immatwtf_rs.shippoint
  • Sort Method: quicksort Memory: 4479kB
  • Buffers: shared hit=100569
9. 3.533 752.186 ↑ 3.0 37,678 3 / 3

Subquery Scan on immatwtf_rs (cost=56,323.34..58,603.98 rows=114,032 width=21) (actual time=740.034..752.186 rows=37,678 loops=3)

  • Buffers: shared hit=100551
10. 539.055 748.653 ↑ 3.0 37,678 3 / 3

HashAggregate (cost=56,323.34..57,463.66 rows=114,032 width=21) (actual time=740.033..748.653 rows=37,678 loops=3)

  • Group Key: immatwtf.twopco, immatwtf.twvnbr, immatwtf.twadcd, immatwtf.twbygp
  • Buffers: shared hit=100551
11. 209.598 209.598 ↓ 1.0 1,146,365 3 / 3

Seq Scan on immatwtf (cost=0.00..44,920.17 rows=1,140,317 width=21) (actual time=0.005..209.598 rows=1,146,365 loops=3)

  • Buffers: shared hit=100551
12. 0.384 2,516.915 ↓ 200.0 200 1

Unique (cost=1,113.73..1,113.77 rows=1 width=376) (actual time=2,516.493..2,516.915 rows=200 loops=1)

  • Buffers: shared hit=475794
13. 112.937 2,516.531 ↓ 737.0 737 1

Sort (cost=1,113.73..1,113.73 rows=1 width=376) (actual time=2,516.491..2,516.531 rows=737 loops=1)

  • Sort Key: (btrim((rs.vendor)::text)), rs.shippoint, rs.clone, (btrim((usvfa6pf.a6shnm)::text)), (btrim((usvba2pf.a2tyem)::text)), (btrim((usvfa6pf.a6adr1)::text)), (btrim((usvfa6pf.a6adr2)::text)), (btrim((usvfa6pf.a6adr3)::text)), (btrim((usvfa6pf.a6adr4)::text)), (btrim((usvfa6pf.a6cit)::text)), (btrim((usvfa6pf.a6stae)::text)), (btrim((usvfa6pf.a6zip)::text)), (btrim((usvfa6pf.a6cy20)::text))
  • Sort Method: quicksort Memory: 6839kB
  • Buffers: shared hit=475794
14. 337.968 2,403.594 ↓ 37,549.0 37,549 1

Nested Loop (cost=0.84..1,113.72 rows=1 width=376) (actual time=1,106.967..2,403.594 rows=37,549 loops=1)

  • Join Filter: (((rs.opco)::text = (usvfa6pf.a6opco)::text) AND ((rs.vendor)::text = (usvfa6pf.a6vnbr)::text) AND (rs.shippoint = usvfa6pf.a6adcd))
  • Rows Removed by Join Filter: 1016552
  • Buffers: shared hit=475794
15. 32.446 1,389.744 ↓ 18,774.5 37,549 1

Nested Loop (cost=0.42..1,111.45 rows=2 width=113) (actual time=1,106.924..1,389.744 rows=37,549 loops=1)

  • Buffers: shared hit=256758
16. 1,131.998 1,131.998 ↓ 278.1 37,550 1

CTE Scan on rs (cost=0.00..2.70 rows=135 width=96) (actual time=1,106.886..1,131.998 rows=37,550 loops=1)

  • Buffers: shared hit=105617
17. 225.300 225.300 ↑ 1.0 1 37,550

Index Scan using ix_usvba2pf_ix_usvba2pf on usvba2pf (cost=0.42..8.20 rows=1 width=17) (actual time=0.006..0.006 rows=1 loops=37,550)

  • Index Cond: (((a2opco)::text = (rs.opco)::text) AND ((a2vnbr)::text = (rs.vendor)::text))
  • Buffers: shared hit=151141
18. 675.882 675.882 ↓ 9.3 28 37,549

Index Scan using ix_usvfa6pf_ix_usvfa6pf on usvfa6pf (cost=0.42..1.07 rows=3 width=82) (actual time=0.008..0.018 rows=28 loops=37,549)

  • Index Cond: (((a6opco)::text = (usvba2pf.a2opco)::text) AND ((a6vnbr)::text = (usvba2pf.a2vnbr)::text))
  • Buffers: shared hit=219036
Planning time : 1.007 ms
Execution time : 2,520.257 ms