explain.depesz.com

PostgreSQL's explain analyze made readable

Result: y8xv

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 39.260 191,178.665 ↑ 4.4 9,711 1

HashAggregate (cost=72,872.38..73,001.54 rows=43,055 width=517) (actual time=191,167.919..191,178.665 rows=9,711 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. 21.990 191,139.405 ↑ 4.4 9,711 1

Append (cost=18,942.85..72,484.88 rows=43,055 width=517) (actual time=189,931.442..191,139.405 rows=9,711 loops=1)

3. 1.142 189,937.640 ↓ 2,451.0 2,451 1

Subquery Scan on *SELECT* 1 (cost=18,942.85..18,942.87 rows=1 width=405) (actual time=189,931.440..189,937.640 rows=2,451 loops=1)

4. 4.488 189,936.498 ↓ 2,451.0 2,451 1

GroupAggregate (cost=18,942.85..18,942.87 rows=1 width=444) (actual time=189,931.438..189,936.498 rows=2,451 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. 18.627 189,932.010 ↓ 2,460.0 2,460 1

Sort (cost=18,942.85..18,942.85 rows=1 width=188) (actual time=189,931.422..189,932.010 rows=2,460 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: 737kB
6. 19.495 189,913.383 ↓ 2,460.0 2,460 1

Nested Loop Left Join (cost=9,483.29..18,942.85 rows=1 width=188) (actual time=691.149..189,913.383 rows=2,460 loops=1)

7. 11.637 31,907.768 ↓ 2,460.0 2,460 1

Nested Loop (cost=9,479.76..17,898.96 rows=1 width=153) (actual time=679.958..31,907.768 rows=2,460 loops=1)

8. 0.000 31,840.231 ↓ 2,795.0 2,795 1

Gather (cost=9,479.68..17,897.06 rows=1 width=146) (actual time=679.906..31,840.231 rows=2,795 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 64,066.969 64,899.128 ↓ 932.0 932 3

Nested Loop Left Join (cost=8,479.68..16,896.96 rows=1 width=146) (actual time=711.231..64,899.128 rows=932 loops=3)

  • Join Filter: (((invenunt.id)::text = (arinvdet.inven)::text) AND ((invenunt.unit)::text = (arinvdet.unit_meas)::text))
  • Rows Removed by Join Filter: 60523862
10. 26.431 798.790 ↓ 932.0 932 3

Nested Loop (cost=8,479.68..15,692.53 rows=1 width=146) (actual time=683.835..798.790 rows=932 loops=3)

  • Join Filter: ((arinvdet.inven)::text = (inventry.id)::text)
11. 3.946 772.341 ↓ 957.0 957 3

Nested Loop Left Join (cost=8,479.62..15,692.41 rows=1 width=128) (actual time=683.755..772.341 rows=957 loops=3)

  • Filter: (apinvdet.autoid IS NULL)
  • Rows Removed by Filter: 5
12. 35.413 768.394 ↑ 2.2 962 3

Nested Loop Left Join (cost=8,479.54..15,463.71 rows=2,152 width=138) (actual time=683.744..768.394 rows=962 loops=3)

13. 31.324 732.957 ↑ 2.2 962 3

Merge Join (cost=8,479.45..8,556.67 rows=2,152 width=138) (actual time=683.716..732.957 rows=962 loops=3)

  • Merge Cond: (((invenwh.id)::text = (arinvdet.inven)::text) AND ((invenwh.warehouse)::text = (arinvdet.warehouse)::text))
14. 558.508 599.544 ↑ 1.3 33,354 3

Sort (cost=4,522.16..4,543.16 rows=42,006 width=32) (actual time=582.779..599.544 rows=33,354 loops=3)

  • Sort Key: invenwh.id, invenwh.warehouse
  • Sort Method: quicksort Memory: 3168kB
15. 41.036 41.036 ↑ 1.2 33,606 3

Parallel Seq Scan on invenwh (cost=0.00..3,877.02 rows=42,006 width=32) (actual time=0.008..41.036 rows=33,606 loops=3)

16. 42.786 102.089 ↑ 1.8 2,887 3

Sort (cost=3,957.27..3,959.87 rows=5,189 width=114) (actual time=99.521..102.089 rows=2,887 loops=3)

  • Sort Key: arinvdet.inven, arinvdet.warehouse
  • Sort Method: quicksort Memory: 755kB
17. 59.303 59.303 ↑ 1.8 2,887 3

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

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

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

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

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

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

Index Scan using idx_btree_inventry_id on inventry (cost=0.06..0.12 rows=1 width=42) (actual time=0.017..0.018 rows=1 loops=2,872)

  • Index Cond: ((id)::text = (invenwh.id)::text)
  • Filter: (c_type = 2.0)
  • Rows Removed by Filter: 0
21. 33.369 33.369 ↑ 1.0 64,964 2,795

Seq Scan on invenunt (cost=0.00..941.90 rows=65,633 width=18) (actual time=0.009..33.369 rows=64,964 loops=2,795)

22. 55.900 55.900 ↑ 1.0 1 2,795

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

  • Index Cond: ((autoid)::text = (arinvdet.doc_aid)::text)
  • Filter: (is_rma IS NOT TRUE)
  • Rows Removed by Filter: 0
23. 89,140.560 157,986.120 ↑ 1.0 1 2,460

Hash Right Join (cost=3.53..1,043.88 rows=1 width=49) (actual time=16.020..64.222 rows=1 loops=2,460)

  • Hash Cond: (((invenunt_1.id)::text = (arinvdet2.inven)::text) AND ((invenunt_1.unit)::text = (arinvdet2.unit_meas)::text))
24. 68,769.300 68,769.300 ↑ 1.0 64,964 2,460

Seq Scan on invenunt invenunt_1 (cost=0.00..941.90 rows=65,633 width=27) (actual time=0.003..27.955 rows=64,964 loops=2,460)

25. 19.680 76.260 ↑ 1.0 1 2,460

Hash (cost=3.53..3.53 rows=1 width=35) (actual time=0.031..0.031 rows=1 loops=2,460)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 56.580 56.580 ↑ 1.0 1 2,460

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

  • Index Cond: ((autoid)::text = (arinvdet.autoid)::text)
27. 14.294 1,179.775 ↑ 5.9 7,260 1

Subquery Scan on *SELECT* 2 (cost=48,704.40..53,542.01 rows=43,054 width=405) (actual time=1,010.618..1,179.775 rows=7,260 loops=1)

28. 67.247 1,165.481 ↑ 5.9 7,260 1

Finalize GroupAggregate (cost=48,704.40..53,412.85 rows=43,054 width=409) (actual time=1,010.616..1,165.481 rows=7,260 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
29. 0.000 1,098.234 ↑ 4.5 8,055 1

Gather Merge (cost=48,704.40..52,788.57 rows=35,878 width=184) (actual time=1,010.594..1,098.234 rows=8,055 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
30. 126.465 3,008.970 ↑ 6.7 2,685 3

Partial GroupAggregate (cost=47,704.39..47,946.57 rows=17,939 width=184) (actual time=953.182..1,002.990 rows=2,685 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
31. 481.050 2,882.505 ↑ 1.6 11,507 3

Sort (cost=47,704.39..47,713.36 rows=17,939 width=157) (actual time=953.166..960.835 rows=11,507 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: 2718kB
32. 122.872 2,401.455 ↑ 1.6 11,507 3

Nested Loop (cost=2,409.26..47,450.90 rows=17,939 width=157) (actual time=184.020..800.485 rows=11,507 loops=3)

  • Join Filter: ((inventry_1.id)::text = (invenwh_1.id)::text)
33. 50.232 1,588.143 ↑ 1.6 11,507 3

Hash Left Join (cost=2,409.17..45,400.19 rows=17,911 width=136) (actual time=183.971..529.381 rows=11,507 loops=3)

  • Hash Cond: (((arinvdet_1.inven)::text = (invenunt_2.id)::text) AND ((arinvdet_1.unit_meas)::text = (invenunt_2.unit)::text))
34. 143.001 1,298.028 ↑ 1.6 11,507 3

Nested Loop (cost=1,204.74..44,028.47 rows=17,911 width=136) (actual time=103.493..432.676 rows=11,507 loops=3)

35. 66.345 570.387 ↑ 1.2 16,240 3

Nested Loop (cost=1,204.68..42,693.34 rows=19,164 width=94) (actual time=103.462..190.129 rows=16,240 loops=3)

36. 5.812 424.272 ↑ 1.1 886 3

Nested Loop Left Join (cost=1,204.60..14,210.96 rows=1,013 width=42) (actual time=102.860..141.424 rows=886 loops=3)

37. 10.410 389.211 ↑ 1.1 886 3

Hash Left Join (cost=1,204.51..12,260.82 rows=1,013 width=38) (actual time=102.827..129.737 rows=886 loops=3)

  • Hash Cond: (((arinvdet2_1.inven)::text = (invenunt_1_1.id)::text) AND ((arinvdet2_1.unit_meas)::text = (invenunt_1_1.unit)::text))
38. 10.117 159.207 ↑ 1.1 886 3

Nested Loop Left Join (cost=0.08..11,046.83 rows=1,013 width=51) (actual time=29.085..53.069 rows=886 loops=3)

39. 119.841 119.841 ↑ 1.1 886 3

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

  • Filter: ((quan <> ship) AND (status = 0.0))
  • Rows Removed by Filter: 70288
40. 29.249 29.249 ↑ 1.0 1 2,659

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

  • Index Cond: ((autoid)::text = (apinvdet_1.autoid)::text)
41. 126.300 219.594 ↑ 1.0 64,877 3

Hash (cost=941.90..941.90 rows=65,633 width=18) (actual time=73.198..73.198 rows=64,877 loops=3)

  • Buckets: 131072 Batches: 1 Memory Usage: 4265kB
42. 93.294 93.294 ↑ 1.0 64,964 3

Seq Scan on invenunt invenunt_1_1 (cost=0.00..941.90 rows=65,633 width=18) (actual time=0.014..31.098 rows=64,964 loops=3)

43. 29.249 29.249 ↑ 1.0 1 2,659

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

  • Index Cond: ((autoid)::text = (apinvdet_1.doc_aid)::text)
44. 79.770 79.770 ↓ 1.1 18 2,659

Index Scan using arinvdet_docaid_idx on arinvdet arinvdet_1 (cost=0.09..28.07 rows=17 width=86) (actual time=0.015..0.030 rows=18 loops=2,659)

  • Index Cond: ((doc_aid)::text = (apinvdet_1.doc_aid)::text)
45. 584.640 584.640 ↑ 1.0 1 48,720

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=48,720)

  • Index Cond: ((id)::text = (arinvdet_1.inven)::text)
  • Filter: (c_type = 2.0)
  • Rows Removed by Filter: 0
46. 135.552 239.883 ↑ 1.0 64,877 3

Hash (cost=941.90..941.90 rows=65,633 width=18) (actual time=79.961..79.961 rows=64,877 loops=3)

  • Buckets: 131072 Batches: 1 Memory Usage: 4265kB
47. 104.331 104.331 ↑ 1.0 64,964 3

Seq Scan on invenunt invenunt_2 (cost=0.00..941.90 rows=65,633 width=18) (actual time=0.007..34.777 rows=64,964 loops=3)

48. 690.440 690.440 ↑ 1.0 1 34,522

Index Scan using idx_invenwh_id on invenwh invenwh_1 (cost=0.08..0.11 rows=1 width=32) (actual time=0.017..0.020 rows=1 loops=34,522)

  • Index Cond: ((id)::text = (arinvdet_1.inven)::text)
  • Filter: ((arinvdet_1.warehouse)::text = (warehouse)::text)
  • Rows Removed by Filter: 3