explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KwJ4 : Optimization for: plan #75xO

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 26.433 1,585.310 ↑ 4.3 9,916 1

HashAggregate (cost=69,249.28..69,378.45 rows=43,056 width=517) (actual time=1,578.400..1,585.310 rows=9,916 loops=1)

  • Group Key: ""*SELECT* 1"".invoice, (''::text), (''::text), (''::text), ""*SELECT* 1"".qty_ordered, ""*SELECT* 1"".inven, ""*SELECT* 1"".unit_meas, ""*SELECT* 1"".warehouse, ""*SELECT* 1"".descr, ""*SELECT* 1"".ship_date, ""*SELECT* 1"".order_amt, ""*SELECT* 1"".stock, ""*SELECT* 1"".m_in_o, ""*SELECT* 1"".wt_in_o, ""*SELECT* 1"".mfg, ""*SELECT* 1"".color, ""*SELECT* 1"".gr_vendor, ""*SELECT* 1"".total_out
2. 6.771 1,558.877 ↑ 4.3 9,916 1

Append (cost=16,700.59..68,861.78 rows=43,056 width=517) (actual time=529.695..1,558.877 rows=9,916 loops=1)

3. 1.200 535.971 ↓ 2,468.0 2,468 1

Subquery Scan on *SELECT* 1 (cost=16,700.59..16,700.61 rows=1 width=405) (actual time=529.694..535.971 rows=2,468 loops=1)

4. 4.510 534.771 ↓ 2,468.0 2,468 1

GroupAggregate (cost=16,700.59..16,700.61 rows=1 width=444) (actual time=529.693..534.771 rows=2,468 loops=1)

  • Group Key: arinv.invoice, arinv.progress, arinvdet.inven, (CASE WHEN ((arinvdet.unit_meas)::text ~~ '%+%'::text) THEN inventry.each_unit WHEN (arinvdet.unit_meas IS NULL) THEN 'Each'::character varying ELSE arinvdet.unit_meas END), arinvdet.warehouse, arinvdet.descr, arinv.ship_date, invenwh.count, invenwh.order_amt, (((arinvdet.mto IS FALSE) AND (arinvdet.c_type = '2'::numeric))), invenwh.m_in_o, invenwh.wt_in_o, inventry.mfg, inventry.color, inventry.gr_vendor, inventry.sales_o, inventry.m_out_o
5. 14.683 530.261 ↓ 2,477.0 2,477 1

Sort (cost=16,700.59..16,700.59 rows=1 width=188) (actual time=529.680..530.261 rows=2,477 loops=1)

  • Sort Key: arinv.invoice, arinv.progress, arinvdet.inven, (CASE WHEN ((arinvdet.unit_meas)::text ~~ '%+%'::text) THEN inventry.each_unit WHEN (arinvdet.unit_meas IS NULL) THEN 'Each'::character varying ELSE arinvdet.unit_meas END), arinvdet.warehouse, arinvdet.descr, arinv.ship_date, invenwh.count, invenwh.order_amt, (((arinvdet.mto IS FALSE) AND (arinvdet.c_type = '2'::numeric))), invenwh.m_in_o, invenwh.wt_in_o, inventry.mfg, inventry.color, inventry.gr_vendor, inventry.sales_o, inventry.m_out_o
  • Sort Method: quicksort Memory: 742kB
6. 6.279 515.578 ↓ 2,477.0 2,477 1

Nested Loop Left Join (cost=9,480.27..16,700.59 rows=1 width=188) (actual time=366.593..515.578 rows=2,477 loops=1)

7. 5.504 452.328 ↓ 2,477.0 2,477 1

Nested Loop (cost=9,480.10..16,696.96 rows=1 width=153) (actual time=366.558..452.328 rows=2,477 loops=1)

8. 14.176 424.328 ↓ 2,812.0 2,812 1

Gather (cost=9,480.02..16,695.06 rows=1 width=146) (actual time=366.543..424.328 rows=2,812 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 11.135 410.152 ↓ 937.0 937 3

Nested Loop Left Join (cost=8,480.02..15,694.96 rows=1 width=146) (actual time=338.799..410.152 rows=937 loops=3)

10. 11.480 399.007 ↓ 937.0 937 3

Nested Loop (cost=8,479.94..15,694.46 rows=1 width=146) (actual time=338.764..399.007 rows=937 loops=3)

  • Join Filter: ((arinvdet.inven)::text = (inventry.id)::text)
11. 1.846 387.518 ↓ 964.0 964 3

Nested Loop Left Join (cost=8,479.88..15,694.34 rows=1 width=128) (actual time=338.699..387.518 rows=964 loops=3)

  • Filter: (apinvdet.autoid IS NULL)
  • Rows Removed by Filter: 6
12. 10.937 385.672 ↑ 2.2 970 3

Nested Loop Left Join (cost=8,479.80..15,465.65 rows=2,152 width=138) (actual time=338.691..385.672 rows=970 loops=3)

13. 24.588 374.725 ↑ 2.2 970 3

Merge Join (cost=8,479.71..8,556.95 rows=2,152 width=138) (actual time=338.668..374.725 rows=970 loops=3)

  • Merge Cond: (((invenwh.id)::text = (arinvdet.inven)::text) AND ((invenwh.warehouse)::text = (arinvdet.warehouse)::text))
14. 288.998 317.553 ↑ 1.3 33,364 3

Sort (cost=4,522.42..4,543.43 rows=42,019 width=32) (actual time=306.079..317.553 rows=33,364 loops=3)

  • Sort Key: invenwh.id, invenwh.warehouse
  • Sort Method: quicksort Memory: 4384kB
15. 28.555 28.555 ↑ 1.3 33,615 3

Parallel Seq Scan on invenwh (cost=0.00..3,877.06 rows=42,019 width=32) (actual time=0.006..28.555 rows=33,615 loops=3)

16. 11.794 32.584 ↑ 1.8 2,910 3

Sort (cost=3,957.27..3,959.87 rows=5,189 width=114) (actual time=31.442..32.584 rows=2,910 loops=3)

  • Sort Key: arinvdet.inven, arinvdet.warehouse
  • Sort Method: quicksort Memory: 761kB
17. 20.790 20.790 ↑ 1.8 2,910 3

Index Scan using arinvdet_status_idx on arinvdet (cost=0.09..3,893.23 rows=5,189 width=114) (actual time=0.094..20.790 rows=2,910 loops=3)

  • Index Cond: (status = 0.0)
  • Filter: ((mto IS NOT TRUE) AND ((id)::text !~~ '($)%'::text) AND (quan <> ship))
  • Rows Removed by Filter: 14159
18. 0.010 0.010 ↓ 0.0 0 2,909

Index Scan using idx_btree_inlink_cdoc on inlink (cost=0.09..3.21 rows=1 width=20) (actual time=0.010..0.010 rows=0 loops=2,909)

  • Index Cond: ((arinvdet."timestamp")::text = (cdoc_stamp)::text)
19. 0.000 0.000 ↓ 0.0 0 2,909

Index Scan using idx_btree_apinvdet_timestamp on apinvdet (cost=0.08..0.10 rows=1 width=27) (actual time=0.000..0.000 rows=0 loops=2,909)

  • Index Cond: ((inlink.vdoc_stamp)::text = ("timestamp")::text)
20. 0.009 0.009 ↑ 1.0 1 2,891

Index Scan using idx_btree_inventry_id on inventry (cost=0.06..0.12 rows=1 width=42) (actual time=0.008..0.009 rows=1 loops=2,891)

  • Index Cond: ((id)::text = (invenwh.id)::text)
  • Filter: (c_type = 2.0)
  • Rows Removed by Filter: 0
21. 0.010 0.010 ↑ 1.0 1 2,812

Index Only Scan using idx_btree_invenunt_id_unit on invenunt (cost=0.08..0.50 rows=1 width=18) (actual time=0.009..0.010 rows=1 loops=2,812)

  • Index Cond: ((id = (arinvdet.inven)::text) AND (unit = (arinvdet.unit_meas)::text))
  • Heap Fetches: 975
22. 22.496 22.496 ↑ 1.0 1 2,812

Index Scan using arinv_pkey on arinv (cost=0.08..1.90 rows=1 width=41) (actual time=0.008..0.008 rows=1 loops=2,812)

  • Index Cond: ((autoid)::text = (arinvdet.doc_aid)::text)
  • Filter: (is_rma IS NOT TRUE)
  • Rows Removed by Filter: 0
23. 9.908 56.971 ↑ 1.0 1 2,477

Nested Loop Left Join (cost=0.17..3.62 rows=1 width=49) (actual time=0.021..0.023 rows=1 loops=2,477)

24. 24.770 24.770 ↑ 1.0 1 2,477

Index Scan using arinvdet_pkey on arinvdet arinvdet2 (cost=0.09..3.53 rows=1 width=35) (actual time=0.010..0.010 rows=1 loops=2,477)

  • Index Cond: ((autoid)::text = (arinvdet.autoid)::text)
25. 22.293 22.293 ↑ 1.0 1 2,477

Index Scan using idx_btree_invenunt_id_unit on invenunt invenunt_1 (cost=0.08..0.09 rows=1 width=27) (actual time=0.009..0.009 rows=1 loops=2,477)

  • Index Cond: (((id)::text = (arinvdet2.inven)::text) AND ((unit)::text = (arinvdet2.unit_meas)::text))
26. 7.173 1,016.135 ↑ 5.8 7,448 1

Subquery Scan on *SELECT* 2 (cost=47,323.30..52,161.17 rows=43,055 width=405) (actual time=926.565..1,016.135 rows=7,448 loops=1)

27. 34.676 1,008.962 ↑ 5.8 7,448 1

Finalize GroupAggregate (cost=47,323.30..52,032.00 rows=43,055 width=409) (actual time=926.563..1,008.962 rows=7,448 loops=1)

  • Group Key: apinvdet_1.invoice, arinvdet_1.inven, (CASE WHEN ((arinvdet_1.unit_meas)::text ~~ '%+%'::text) THEN inventry_1.each_unit WHEN (arinvdet_1.unit_meas IS NULL) THEN 'Each'::character varying ELSE arinvdet_1.unit_meas END), arinvdet_1.warehouse, arinvdet_1.descr, inmfg.start_date, invenwh_1.count, invenwh_1.order_amt, ((apinvdet_1.c_type = '2'::numeric)), invenwh_1.m_in_o, invenwh_1.wt_in_o, inventry_1.mfg, inventry_1.color, inventry_1.gr_vendor, inventry_1.sales_o, inventry_1.m_out_o
28. 0.000 974.286 ↑ 4.3 8,332 1

Gather Merge (cost=47,323.30..51,407.70 rows=35,880 width=184) (actual time=926.538..974.286 rows=8,332 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
29. 95.571 2,789.937 ↑ 6.5 2,777 3

Partial GroupAggregate (cost=46,323.30..46,565.49 rows=17,940 width=184) (actual time=891.733..929.979 rows=2,777 loops=3)

  • Group Key: apinvdet_1.invoice, arinvdet_1.inven, (CASE WHEN ((arinvdet_1.unit_meas)::text ~~ '%+%'::text) THEN inventry_1.each_unit WHEN (arinvdet_1.unit_meas IS NULL) THEN 'Each'::character varying ELSE arinvdet_1.unit_meas END), arinvdet_1.warehouse, arinvdet_1.descr, inmfg.start_date, invenwh_1.count, invenwh_1.order_amt, ((apinvdet_1.c_type = '2'::numeric)), invenwh_1.m_in_o, invenwh_1.wt_in_o, inventry_1.mfg, inventry_1.color, inventry_1.gr_vendor, inventry_1.sales_o, inventry_1.m_out_o
30. 404.649 2,694.366 ↑ 1.4 12,490 3

Sort (cost=46,323.30..46,332.27 rows=17,940 width=157) (actual time=891.716..898.122 rows=12,490 loops=3)

  • Sort Key: apinvdet_1.invoice, arinvdet_1.inven, (CASE WHEN ((arinvdet_1.unit_meas)::text ~~ '%+%'::text) THEN inventry_1.each_unit WHEN (arinvdet_1.unit_meas IS NULL) THEN 'Each'::character varying ELSE arinvdet_1.unit_meas END), arinvdet_1.warehouse, arinvdet_1.descr, inmfg.start_date, invenwh_1.count, invenwh_1.order_amt, ((apinvdet_1.c_type = '2'::numeric)), invenwh_1.m_in_o, invenwh_1.wt_in_o, inventry_1.mfg, inventry_1.color, inventry_1.gr_vendor, inventry_1.sales_o, inventry_1.m_out_o
  • Sort Method: quicksort Memory: 3051kB
31. 127.232 2,289.717 ↑ 1.4 12,490 3

Nested Loop (cost=1,200.22..46,069.79 rows=17,940 width=157) (actual time=123.413..763.239 rows=12,490 loops=3)

  • Join Filter: ((inventry_1.id)::text = (invenwh_1.id)::text)
32. 56.262 1,525.461 ↑ 1.4 12,491 3

Hash Left Join (cost=1,200.13..44,287.93 rows=17,911 width=136) (actual time=123.369..508.487 rows=12,491 loops=3)

  • Hash Cond: (((arinvdet_1.inven)::text = (invenunt_2.id)::text) AND ((arinvdet_1.unit_meas)::text = (invenunt_2.unit)::text))
33. 142.014 1,212.069 ↑ 1.4 12,491 3

Nested Loop (cost=0.39..42,920.91 rows=17,911 width=136) (actual time=37.332..404.023 rows=12,491 loops=3)

34. 68.103 435.471 ↑ 1.1 17,627 3

Nested Loop (cost=0.34..41,585.77 rows=19,164 width=94) (actual time=37.290..145.157 rows=17,627 loops=3)

35. 4.800 282.087 ↑ 1.1 917 3

Nested Loop Left Join (cost=0.25..13,103.39 rows=1,013 width=42) (actual time=36.295..94.029 rows=917 loops=3)

36. 5.847 249.777 ↑ 1.1 917 3

Nested Loop Left Join (cost=0.17..11,152.43 rows=1,013 width=38) (actual time=36.268..83.259 rows=917 loops=3)

37. 6.048 186.159 ↑ 1.1 917 3

Nested Loop Left Join (cost=0.08..11,050.16 rows=1,013 width=51) (actual time=36.230..62.053 rows=917 loops=3)

38. 141.597 141.597 ↑ 1.1 917 3

Parallel Seq Scan on apinvdet apinvdet_1 (cost=0.00..8,031.11 rows=1,013 width=55) (actual time=36.189..47.199 rows=917 loops=3)

  • Filter: ((quan <> ship) AND (status = 0.0))
  • Rows Removed by Filter: 70298
39. 38.514 38.514 ↑ 1.0 1 2,751

Index Scan using apinvdet_pkey on apinvdet arinvdet2_1 (cost=0.08..2.98 rows=1 width=30) (actual time=0.014..0.014 rows=1 loops=2,751)

  • Index Cond: ((autoid)::text = (apinvdet_1.autoid)::text)
40. 57.771 57.771 ↑ 1.0 1 2,751

Index Only Scan using idx_btree_invenunt_id_unit on invenunt invenunt_1_1 (cost=0.08..0.10 rows=1 width=18) (actual time=0.019..0.021 rows=1 loops=2,751)

  • Index Cond: ((id = (arinvdet2_1.inven)::text) AND (unit = (arinvdet2_1.unit_meas)::text))
  • Heap Fetches: 726
41. 27.510 27.510 ↑ 1.0 1 2,751

Index Scan using inmfg_pkey on inmfg (cost=0.08..1.93 rows=1 width=21) (actual time=0.010..0.010 rows=1 loops=2,751)

  • Index Cond: ((autoid)::text = (apinvdet_1.doc_aid)::text)
42. 85.281 85.281 ↓ 1.1 19 2,751

Index Scan using arinvdet_docaid_idx on arinvdet arinvdet_1 (cost=0.09..28.07 rows=17 width=86) (actual time=0.017..0.031 rows=19 loops=2,751)

  • Index Cond: ((doc_aid)::text = (apinvdet_1.doc_aid)::text)
43. 634.584 634.584 ↑ 1.0 1 52,882

Index Scan using idx_btree_inventry_id on inventry inventry_1 (cost=0.06..0.07 rows=1 width=42) (actual time=0.011..0.012 rows=1 loops=52,882)

  • Index Cond: ((id)::text = (arinvdet_1.inven)::text)
  • Filter: (c_type = 2.0)
  • Rows Removed by Filter: 0
44. 143.619 257.130 ↑ 1.0 64,886 3

Hash (cost=939.89..939.89 rows=64,963 width=18) (actual time=85.710..85.710 rows=64,886 loops=3)

  • Buckets: 65536 Batches: 1 Memory Usage: 3754kB
45. 113.511 113.511 ↓ 1.0 64,973 3

Seq Scan on invenunt invenunt_2 (cost=0.00..939.89 rows=64,963 width=18) (actual time=0.013..37.837 rows=64,973 loops=3)

46. 637.024 637.024 ↑ 1.0 1 37,472

Index Scan using idx_btree_invenwh_id_warehouse on invenwh invenwh_1 (cost=0.08..0.09 rows=1 width=32) (actual time=0.016..0.017 rows=1 loops=37,472)

  • Index Cond: (((id)::text = (arinvdet_1.inven)::text) AND ((warehouse)::text = (arinvdet_1.warehouse)::text))