explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Yy2 : Optimization for: plan #y8xv

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 23.178 1,655.661 ↑ 4.4 9,771 1

HashAggregate (cost=69,513.42..69,642.59 rows=43,055 width=517) (actual time=1,649.601..1,655.661 rows=9,771 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. 4.957 1,632.483 ↑ 4.4 9,771 1

Append (cost=16,698.65..69,125.93 rows=43,055 width=517) (actual time=648.896..1,632.483 rows=9,771 loops=1)

3. 1.200 655.107 ↓ 2,457.0 2,457 1

Subquery Scan on *SELECT* 1 (cost=16,698.65..16,698.68 rows=1 width=405) (actual time=648.895..655.107 rows=2,457 loops=1)

4. 4.445 653.907 ↓ 2,457.0 2,457 1

GroupAggregate (cost=16,698.65..16,698.67 rows=1 width=444) (actual time=648.892..653.907 rows=2,457 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.551 649.462 ↓ 2,466.0 2,466 1

Sort (cost=16,698.65..16,698.66 rows=1 width=188) (actual time=648.879..649.462 rows=2,466 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: 739kB
6. 12.847 634.911 ↓ 2,466.0 2,466 1

Nested Loop Left Join (cost=9,480.01..16,698.65 rows=1 width=188) (actual time=368.219..634.911 rows=2,466 loops=1)

7. 5.608 543.152 ↓ 2,466.0 2,466 1

Nested Loop (cost=9,479.84..16,695.03 rows=1 width=153) (actual time=368.170..543.152 rows=2,466 loops=1)

8. 0.000 498.330 ↓ 2,801.0 2,801 1

Gather (cost=9,479.76..16,693.13 rows=1 width=146) (actual time=368.148..498.330 rows=2,801 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 15.515 551.904 ↓ 934.0 934 3

Nested Loop Left Join (cost=8,479.76..15,693.03 rows=1 width=146) (actual time=452.545..551.904 rows=934 loops=3)

10. 17.508 536.375 ↓ 934.0 934 3

Nested Loop (cost=8,479.68..15,692.53 rows=1 width=146) (actual time=452.505..536.375 rows=934 loops=3)

  • Join Filter: ((arinvdet.inven)::text = (inventry.id)::text)
11. 2.054 518.854 ↓ 959.0 959 3

Nested Loop Left Join (cost=8,479.62..15,692.41 rows=1 width=128) (actual time=452.420..518.854 rows=959 loops=3)

  • Filter: (apinvdet.autoid IS NULL)
  • Rows Removed by Filter: 5
12. 15.403 516.799 ↑ 2.2 964 3

Nested Loop Left Join (cost=8,479.54..15,463.71 rows=2,152 width=138) (actual time=452.411..516.799 rows=964 loops=3)

13. 34.231 501.383 ↑ 2.2 964 3

Merge Join (cost=8,479.45..8,556.67 rows=2,152 width=138) (actual time=452.383..501.383 rows=964 loops=3)

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

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

  • Sort Key: invenwh.id, invenwh.warehouse
  • Sort Method: quicksort Memory: 4246kB
15. 32.408 32.408 ↑ 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..32.408 rows=33,606 loops=3)

16. 17.844 48.235 ↑ 1.8 2,893 3

Sort (cost=3,957.27..3,959.87 rows=5,189 width=114) (actual time=46.952..48.235 rows=2,893 loops=3)

  • Sort Key: arinvdet.inven, arinvdet.warehouse
  • Sort Method: quicksort Memory: 757kB
17. 30.391 30.391 ↑ 1.8 2,893 3

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

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

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

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

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,892)

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

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

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

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

  • Index Cond: ((id = (arinvdet.inven)::text) AND (unit = (arinvdet.unit_meas)::text))
  • Heap Fetches: 1161
22. 39.214 39.214 ↑ 1.0 1 2,801

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

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

Nested Loop Left Join (cost=0.17..3.62 rows=1 width=49) (actual time=0.029..0.032 rows=1 loops=2,466)

24. 39.456 39.456 ↑ 1.0 1 2,466

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

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

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

  • Index Cond: (((id)::text = (arinvdet2.inven)::text) AND ((unit)::text = (arinvdet2.unit_meas)::text))
26. 4.970 972.419 ↑ 5.9 7,314 1

Subquery Scan on *SELECT* 2 (cost=47,589.64..52,427.25 rows=43,054 width=405) (actual time=904.366..972.419 rows=7,314 loops=1)

27. 24.075 967.449 ↑ 5.9 7,314 1

Finalize GroupAggregate (cost=47,589.64..52,298.09 rows=43,054 width=409) (actual time=904.364..967.449 rows=7,314 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 943.374 ↑ 4.4 8,156 1

Gather Merge (cost=47,589.64..51,673.81 rows=35,878 width=184) (actual time=904.340..943.374 rows=8,156 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
29. 69.525 2,746.227 ↑ 6.6 2,719 3

Partial GroupAggregate (cost=46,589.63..46,831.81 rows=17,939 width=184) (actual time=887.923..915.409 rows=2,719 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. 298.230 2,676.702 ↑ 1.6 11,532 3

Sort (cost=46,589.63..46,598.60 rows=17,939 width=157) (actual time=887.908..892.234 rows=11,532 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: 3780kB
31. 126.685 2,378.472 ↑ 1.6 11,532 3

Nested Loop (cost=1,200.22..46,336.14 rows=17,939 width=157) (actual time=201.867..792.824 rows=11,532 loops=3)

  • Join Filter: ((inventry_1.id)::text = (invenwh_1.id)::text)
32. 51.882 1,663.638 ↑ 1.6 11,532 3

Hash Left Join (cost=1,200.13..44,285.43 rows=17,911 width=136) (actual time=201.816..554.546 rows=11,532 loops=3)

  • Hash Cond: (((arinvdet_1.inven)::text = (invenunt_2.id)::text) AND ((arinvdet_1.unit_meas)::text = (invenunt_2.unit)::text))
33. 186.404 1,165.491 ↑ 1.6 11,532 3

Nested Loop (cost=0.39..42,918.41 rows=17,911 width=136) (actual time=52.732..388.497 rows=11,532 loops=3)

34. 61.263 441.825 ↑ 1.2 16,281 3

Nested Loop (cost=0.34..41,583.27 rows=19,164 width=94) (actual time=52.695..147.275 rows=16,281 loops=3)

35. 5.010 300.102 ↑ 1.1 894 3

Nested Loop Left Join (cost=0.25..13,100.89 rows=1,013 width=42) (actual time=51.823..100.034 rows=894 loops=3)

36. 5.838 268.272 ↑ 1.1 894 3

Nested Loop Left Join (cost=0.17..11,150.76 rows=1,013 width=38) (actual time=51.795..89.424 rows=894 loops=3)

37. 7.839 224.886 ↑ 1.1 894 3

Nested Loop Left Join (cost=0.08..11,048.50 rows=1,013 width=51) (actual time=51.756..74.962 rows=894 loops=3)

38. 184.863 184.863 ↑ 1.1 894 3

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

  • Filter: ((quan <> ship) AND (status = 0.0))
  • Rows Removed by Filter: 70294
39. 32.184 32.184 ↑ 1.0 1 2,682

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

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

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.013..0.014 rows=1 loops=2,682)

  • Index Cond: ((id = (arinvdet2_1.inven)::text) AND (unit = (arinvdet2_1.unit_meas)::text))
  • Heap Fetches: 1134
41. 26.820 26.820 ↑ 1.0 1 2,682

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,682)

  • Index Cond: ((autoid)::text = (apinvdet_1.doc_aid)::text)
42. 80.460 80.460 ↓ 1.1 18 2,682

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

  • Index Cond: ((doc_aid)::text = (apinvdet_1.doc_aid)::text)
43. 537.262 537.262 ↑ 1.0 1 48,842

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.011 rows=1 loops=48,842)

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

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3753kB
45. 182.628 182.628 ↓ 1.0 64,964 3

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

46. 588.149 588.149 ↑ 1.0 1 34,597

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

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