explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hFa6n

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 15,383.027 ↑ 6.2 12 1

Unique (cost=1,117,795.77..1,117,796.52 rows=75 width=53) (actual time=15,383.021..15,383.027 rows=12 loops=1)

2. 0.023 15,383.022 ↑ 6.2 12 1

Sort (cost=1,117,795.77..1,117,795.96 rows=75 width=53) (actual time=15,383.020..15,383.022 rows=12 loops=1)

  • Sort Key: nc_contractor_info.proj_id, nc_contractor_info.contractor_id, nc_contractor_info.main_contractor, nc_contractor_info.warranty_expiry DESC
  • Sort Method: quicksort Memory: 26kB
3. 0.005 15,382.999 ↑ 6.2 12 1

Subquery Scan on nc_contractor_info (cost=1,117,790.25..1,117,793.44 rows=75 width=53) (actual time=15,382.987..15,382.999 rows=12 loops=1)

4. 0.008 15,382.994 ↑ 6.2 12 1

Unique (cost=1,117,790.25..1,117,792.69 rows=75 width=249) (actual time=15,382.985..15,382.994 rows=12 loops=1)

5. 0.030 15,382.986 ↑ 6.2 12 1

Sort (cost=1,117,790.25..1,117,790.44 rows=75 width=249) (actual time=15,382.985..15,382.986 rows=12 loops=1)

  • Sort Key: v.proj_id, v.contractor_id, v.main_contractor, v.po_id, v.po_item_id, v.doc_no, v.objective_id, v.objective_snap, v.budget_sub_category_high_rise_id, v.budget_sub_category_high_rise_name_snap, v.warranty_expiry, v.source
  • Sort Method: quicksort Memory: 26kB
6. 0.004 15,382.956 ↑ 6.2 12 1

Subquery Scan on v (cost=1,117,786.42..1,117,787.92 rows=75 width=249) (actual time=15,382.947..15,382.956 rows=12 loops=1)

7. 0.033 15,382.952 ↑ 6.2 12 1

HashAggregate (cost=1,117,786.42..1,117,787.17 rows=75 width=249) (actual time=15,382.946..15,382.952 rows=12 loops=1)

  • Group Key: nc_contractor_info_excl_migrate.source, nc_contractor_info_excl_migrate.proj_id, nc_contractor_info_excl_migrate.proj_type, nc_contractor_info_excl_migrate.internal_order_id, nc_contractor_info_excl_migrate.contractor_id, nc_contractor_info_excl_migrate.main_contractor, nc_contractor_info_excl_migrate.po_id, nc_contractor_info_excl_migrate.po_item_id, nc_contractor_info_excl_migrate.doc_no, nc_contractor_info_excl_migrate.objective_id, nc_contractor_info_excl_migrate.objective_snap, nc_contractor_info_excl_migrate.budget_sub_category_high_rise_id, nc_contractor_info_excl_migrate.budget_sub_category_high_rise_name_snap, nc_contractor_info_excl_migrate.warranty_expiry
8. 0.002 15,382.919 ↑ 6.2 12 1

Append (cost=2,244.35..1,117,783.79 rows=75 width=249) (actual time=15,382.911..15,382.919 rows=12 loops=1)

9. 0.000 8.397 ↓ 0.0 0 1

Unique (cost=2,244.35..2,246.76 rows=74 width=249) (actual time=8.397..8.397 rows=0 loops=1)

10. 0.015 8.397 ↓ 0.0 0 1

Sort (cost=2,244.35..2,244.54 rows=74 width=249) (actual time=8.397..8.397 rows=0 loops=1)

  • Sort Key: nc_contractor_info_excl_migrate.source, nc_contractor_info_excl_migrate.proj_id, nc_contractor_info_excl_migrate.contractor_id, nc_contractor_info_excl_migrate.main_contractor, nc_contractor_info_excl_migrate.po_id, nc_contractor_info_excl_migrate.po_item_id, nc_contractor_info_excl_migrate.doc_no, nc_contractor_info_excl_migrate.objective_id, nc_contractor_info_excl_migrate.objective_snap, nc_contractor_info_excl_migrate.budget_sub_category_high_rise_id, nc_contractor_info_excl_migrate.budget_sub_category_high_rise_name_snap, nc_contractor_info_excl_migrate.warranty_expiry
  • Sort Method: quicksort Memory: 25kB
11. 0.001 8.382 ↓ 0.0 0 1

Subquery Scan on nc_contractor_info_excl_migrate (cost=2,238.91..2,242.06 rows=74 width=249) (actual time=8.382..8.382 rows=0 loops=1)

12. 0.000 8.381 ↓ 0.0 0 1

Unique (cost=2,238.91..2,241.32 rows=74 width=249) (actual time=8.381..8.381 rows=0 loops=1)

13. 0.019 8.381 ↓ 0.0 0 1

Sort (cost=2,238.91..2,239.10 rows=74 width=249) (actual time=8.381..8.381 rows=0 loops=1)

  • Sort Key: pi.proj_id, c.id, (false), (NULL::uuid), (NULL::uuid), (NULL::character varying), (NULL::uuid), (NULL::character varying), pi.budget_sub_cat_high_rise_id, ((bschr.name)::character varying(500)) COLLATE thai, (NULL::date), ('SUPPLIER'::text)
  • Sort Method: quicksort Memory: 25kB
14. 0.012 8.362 ↓ 0.0 0 1

Hash Join (cost=2,231.17..2,236.61 rows=74 width=249) (actual time=8.362..8.362 rows=0 loops=1)

  • Hash Cond: (pi.proj_id = proj.id)
15. 0.003 8.213 ↓ 0.0 0 1

HashAggregate (cost=2,217.17..2,219.57 rows=240 width=245) (actual time=8.213..8.213 rows=0 loops=1)

  • Group Key: ('SUPPLIER'::text), pi.proj_id, pi.internal_order_id, c.id, (false), (NULL::uuid), (NULL::uuid), (NULL::character varying), (NULL::uuid), (NULL::character varying), pi.budget_sub_cat_high_rise_id, ((bschr.name)::character varying(500)), (NULL::date)
16. 0.002 8.210 ↓ 0.0 0 1

Append (cost=1,767.70..2,209.37 rows=240 width=245) (actual time=8.210..8.210 rows=0 loops=1)

17. 0.000 5.888 ↓ 0.0 0 1

Unique (cost=1,767.70..1,770.65 rows=236 width=729) (actual time=5.888..5.888 rows=0 loops=1)

18. 0.008 5.888 ↓ 0.0 0 1

Sort (cost=1,767.70..1,768.29 rows=236 width=729) (actual time=5.888..5.888 rows=0 loops=1)

  • Sort Key: pi.proj_id, c.id, pi.budget_sub_cat_high_rise_id, ((bschr.name)::character varying(500)) COLLATE thai
  • Sort Method: quicksort Memory: 25kB
19. 0.000 5.880 ↓ 0.0 0 1

Nested Loop (cost=1.13..1,758.40 rows=236 width=729) (actual time=5.880..5.880 rows=0 loops=1)

20. 0.001 5.880 ↓ 0.0 0 1

Nested Loop (cost=0.84..1,589.42 rows=236 width=572) (actual time=5.880..5.880 rows=0 loops=1)

21. 0.000 5.879 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.56..1,532.85 rows=1 width=568) (actual time=5.879..5.879 rows=0 loops=1)

  • Join Filter: (pi.budget_sub_cat_high_rise_id = bschr.id)
22. 0.001 5.879 ↓ 0.0 0 1

Nested Loop (cost=0.56..1,530.98 rows=1 width=52) (actual time=5.879..5.879 rows=0 loops=1)

  • Join Filter: (pi.ord = sppm.pr_item)
23. 0.000 5.878 ↓ 0.0 0 1

Nested Loop (cost=0.28..1,525.24 rows=6 width=63) (actual time=5.878..5.878 rows=0 loops=1)

24. 5.878 5.878 ↓ 0.0 0 1

Seq Scan on pr_item pi (cost=0.00..1,479.46 rows=6 width=68) (actual time=5.877..5.878 rows=0 loops=1)

  • Filter: (internal_order_id = '00811f57-3855-46cd-a5cd-23d21550f2b0'::uuid)
  • Rows Removed by Filter: 15157
25. 0.000 0.000 ↓ 0.0 0

Index Scan using pr_pkey on pr (cost=0.28..7.63 rows=1 width=27) (never executed)

  • Index Cond: (id = pi.pr_id)
26. 0.000 0.000 ↓ 0.0 0

Index Only Scan using sap_pr_po_map_uindex on sap_pr_po_map sppm (cost=0.29..0.82 rows=11 width=19) (never executed)

  • Index Cond: (pr_doc_no = (pr.pr_no)::text)
  • Heap Fetches: 0
27. 0.000 0.000 ↓ 0.0 0

Seq Scan on budget_sub_category_high_rise bschr (cost=0.00..1.39 rows=39 width=532) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Index Scan using sap_po_doc_no_po_item_uindex on sap_po po (cost=0.28..56.23 rows=33 width=12) (never executed)

  • Index Cond: (po_item = sppm.po_item)
29. 0.000 0.000 ↓ 0.0 0

Index Scan using contractor_code_uindex on contractor c (cost=0.29..0.72 rows=1 width=25) (never executed)

  • Index Cond: ((code)::text = (po.vendor_code)::text)
30. 0.000 0.002 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.00..0.02 rows=1 width=1,697) (actual time=0.002..0.002 rows=0 loops=1)

31. 0.002 0.002 ↓ 0.0 0 1

HashAggregate (cost=0.00..0.01 rows=1 width=1,697) (actual time=0.001..0.002 rows=0 loops=1)

  • Group Key: 'PROJ_CONTRACTOR'::text, id, NULL::uuid, id, false, NULL::uuid, NULL::uuid, NULL::character varying(255), NULL::uuid, NULL::character varying(500), NULL::uuid, NULL::character varying(501), NULL::date
32. 0.000 0.000 ↓ 0.0 0 1

Result (cost=0.00..0.00 rows=0 width=1,697) (actual time=0.000..0.000 rows=0 loops=1)

  • One-Time Filter: false
33. 0.001 2.273 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=436.26..436.30 rows=1 width=784) (actual time=2.273..2.273 rows=0 loops=1)

34. 0.000 2.272 ↓ 0.0 0 1

Unique (cost=436.26..436.29 rows=1 width=1,268) (actual time=2.272..2.272 rows=0 loops=1)

35. 0.017 2.272 ↓ 0.0 0 1

Sort (cost=436.26..436.26 rows=1 width=1,268) (actual time=2.272..2.272 rows=0 loops=1)

  • Sort Key: ((po_1.po_type)::character varying(255)), po_1.proj_id, po_1.contractor_id, (CASE WHEN ((po_1.po_type = 'LOW_CONTRACT'::po_type) AND (po_1.contractor_id = io.main_contractor_id)) THEN true ELSE false END), po_1.id, (CASE WHEN (po_1.po_type = 'LOW_CONTRACT'::po_type) THEN pi_1.id ELSE NULL::uuid END), (COALESCE(po_1.contract_doc_no, po_1.po_no)), po_1.objective_id, po_1.objective_snap, (COALESCE((pi_1.allow_rebate_retention_bond_start_date - 1), pi_1.retention_end_date))
  • Sort Method: quicksort Memory: 25kB
36. 0.001 2.255 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.56..436.25 rows=1 width=1,268) (actual time=2.255..2.255 rows=0 loops=1)

  • Join Filter: (io.id = pi_1.internal_order_id)
37. 0.001 2.254 ↓ 0.0 0 1

Nested Loop (cost=0.28..427.91 rows=1 width=228) (actual time=2.254..2.254 rows=0 loops=1)

38. 2.253 2.253 ↓ 0.0 0 1

Seq Scan on po_item pi_1 (cost=0.00..419.55 rows=1 width=56) (actual time=2.253..2.253 rows=0 loops=1)

  • Filter: (internal_order_id = '00811f57-3855-46cd-a5cd-23d21550f2b0'::uuid)
  • Rows Removed by Filter: 4044
39. 0.000 0.000 ↓ 0.0 0

Index Scan using po_pkey on po po_1 (cost=0.28..8.31 rows=1 width=188) (never executed)

  • Index Cond: (id = pi_1.po_id)
  • Filter: ((po_type = ANY ('{LOW_CONTRACT,LOW_PO_HOUSE}'::po_type[])) AND (po_status <> ALL ('{DRAFT,DRAFT_SEND_BACK,WAITING_FOR_AUTHORITY_APPROVAL,WAITING_FOR_APPROVAL,CANCELED,REJECTED,APPROVED,PRINTED,WAITING_FOR_RELEASE}'::po_status[])))
40. 0.000 0.000 ↓ 0.0 0

Index Scan using internal_order_pkey on internal_order io (cost=0.29..8.30 rows=1 width=32) (never executed)

  • Index Cond: (id = '00811f57-3855-46cd-a5cd-23d21550f2b0'::uuid)
41. 0.001 0.029 ↓ 0.0 0 1

Subquery Scan on *SELECT* 4 (cost=0.01..0.03 rows=1 width=784) (actual time=0.029..0.029 rows=0 loops=1)

42. 0.000 0.028 ↓ 0.0 0 1

Unique (cost=0.01..0.02 rows=1 width=1,268) (actual time=0.028..0.028 rows=0 loops=1)

43. 0.027 0.028 ↓ 0.0 0 1

Sort (cost=0.01..0.02 rows=0 width=1,268) (actual time=0.028..0.028 rows=0 loops=1)

  • Sort Key: ((po_type)::character varying(255)), proj_id, contractor_id, id, (COALESCE(contract_doc_no, po_no)), objective_id, objective_snap, budget_sub_cat_id, ((name)::character varying(500)) COLLATE thai, (COALESCE((allow_rebate_retention_bond_start_date - 1), retention_end_date))
  • Sort Method: quicksort Memory: 25kB
44. 0.001 0.001 ↓ 0.0 0 1

Result (cost=0.00..0.00 rows=0 width=1,268) (actual time=0.000..0.001 rows=0 loops=1)

  • One-Time Filter: false
45. 0.000 0.016 ↓ 0.0 0 1

Subquery Scan on *SELECT* 5 (cost=0.01..0.03 rows=1 width=784) (actual time=0.016..0.016 rows=0 loops=1)

46. 0.001 0.016 ↓ 0.0 0 1

Unique (cost=0.01..0.02 rows=1 width=1,268) (actual time=0.016..0.016 rows=0 loops=1)

47. 0.015 0.015 ↓ 0.0 0 1

Sort (cost=0.01..0.02 rows=0 width=1,268) (actual time=0.015..0.015 rows=0 loops=1)

  • Sort Key: ((po_type)::character varying(255)), proj_id, contractor_id, (CASE WHEN ((code)::text = '02'::text) THEN true ELSE false END), id, id, (COALESCE(contract_doc_no, po_no)), objective_id, objective_snap, budget_sub_cat_id, ((name)::character varying(500)) COLLATE thai, (COALESCE((allow_rebate_retention_bond_start_date - 1), retention_end_date))
  • Sort Method: quicksort Memory: 25kB
48. 0.000 0.000 ↓ 0.0 0 1

Result (cost=0.00..0.00 rows=0 width=1,268) (actual time=0.000..0.000 rows=0 loops=1)

  • One-Time Filter: false
49. 0.019 0.137 ↑ 1.0 62 1

Hash (cost=13.22..13.22 rows=62 width=20) (actual time=0.137..0.137 rows=62 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
50. 0.118 0.118 ↑ 1.0 62 1

Seq Scan on proj (cost=0.00..13.22 rows=62 width=20) (actual time=0.016..0.118 rows=62 loops=1)

  • Filter: (proj_type = 'LOW_RISE'::proj_type)
  • Rows Removed by Filter: 36
51. 0.007 15,374.520 ↓ 12.0 12 1

Unique (cost=1,115,536.26..1,115,536.28 rows=1 width=1,701) (actual time=15,374.513..15,374.520 rows=12 loops=1)

52. 0.046 15,374.513 ↓ 12.0 12 1

Sort (cost=1,115,536.26..1,115,536.27 rows=1 width=1,701) (actual time=15,374.512..15,374.513 rows=12 loops=1)

  • Sort Key: ('MIGRATE_LIVE1_CONTRACTOR'::text), proj_1.id, c_1.id, (COALESCE(migrate_live1_contractor.is_main, false)), migrate_live1_contractor.warranty_expiry
  • Sort Method: quicksort Memory: 26kB
53. 0.016 15,374.467 ↓ 12.0 12 1

Merge Anti Join (cost=1,094,539.89..1,115,536.25 rows=1 width=1,701) (actual time=15,374.130..15,374.467 rows=12 loops=1)

  • Merge Cond: (proj_1.id = pi_2.proj_id)
  • Join Filter: ((NOT (pi_2.internal_order_id IS DISTINCT FROM io_1.id)) AND (c_2.id = c_1.id) AND ((('MIGRATE_LIVE1_CONTRACTOR'::text) = 'MIGRATE_LIVE1_CONTRACTOR'::text) OR ((('MIGRATE_LIVE1_CONTRACTOR'::text) = 'MIGRATE_LIVE1_SUPPLIER'::text) AND (NOT ((NULL::date) IS DISTINCT FROM migrate_live1_contractor.warranty_expiry)))))
  • Rows Removed by Join Filter: 12
54. 0.176 1,542.603 ↓ 12.0 12 1

Nested Loop (cost=294,802.46..301,206.47 rows=1 width=89) (actual time=1,542.274..1,542.603 rows=12 loops=1)

  • Join Filter: (((((migrate_live1_contractor.proj_code)::character varying(255)))::text = (proj_1.code)::text) AND (io_1.proj_id = proj_1.id))
  • Rows Removed by Join Filter: 732
55. 0.115 0.115 ↑ 1.0 62 1

Index Scan using proj_pkey on proj proj_1 (cost=0.14..57.53 rows=62 width=25) (actual time=0.010..0.115 rows=62 loops=1)

  • Filter: (proj_type = 'LOW_RISE'::proj_type)
  • Rows Removed by Filter: 36
56. 0.125 1,542.312 ↓ 1.7 12 62

Materialize (cost=294,802.32..301,141.36 rows=7 width=601) (actual time=20.159..24.876 rows=12 loops=62)

57. 35.680 1,542.187 ↓ 1.7 12 1

Hash Join (cost=294,802.32..301,141.33 rows=7 width=601) (actual time=1,249.865..1,542.187 rows=12 loops=1)

  • Hash Cond: ((((migrate_live1_contractor.io_code)::character varying(255)))::text = (io_1.io_code)::text)
58. 102.894 1,506.495 ↑ 1.0 187,734 1

Hash Join (cost=294,794.00..300,624.86 rows=193,551 width=1,085) (actual time=1,246.910..1,506.495 rows=187,734 loops=1)

  • Hash Cond: ((((migrate_live1_contractor.code)::character varying(255)))::text = (c_1.code)::text)
59. 99.109 1,392.704 ↑ 1.0 193,540 1

Unique (cost=293,715.08..297,102.22 rows=193,551 width=1,585) (actual time=1,235.939..1,392.704 rows=193,540 loops=1)

60. 1,122.536 1,293.595 ↑ 1.0 193,551 1

Sort (cost=293,715.08..294,198.95 rows=193,551 width=1,585) (actual time=1,235.937..1,293.595 rows=193,551 loops=1)

  • Sort Key: ((migrate_live1_contractor.code)::character varying(255)) COLLATE thai, ((migrate_live1_contractor.proj_code)::character varying(255)) COLLATE thai, ((migrate_live1_contractor.io_code)::character varying(255)) COLLATE thai, migrate_live1_contractor.is_main, migrate_live1_contractor.warranty_expiry, ('MIGRATE_LIVE1_CONTRACTOR'::text)
  • Sort Method: external merge Disk: 10864kB
61. 26.004 171.059 ↑ 1.0 193,551 1

Append (cost=0.00..9,452.02 rows=193,551 width=1,585) (actual time=0.014..171.059 rows=193,551 loops=1)

62. 13.045 13.045 ↑ 1.0 22,687 1

Seq Scan on migrate_live1_contractor (cost=0.00..724.87 rows=22,687 width=1,585) (actual time=0.014..13.045 rows=22,687 loops=1)

63. 46.452 132.010 ↑ 1.0 170,864 1

Subquery Scan on *SELECT* 2_1 (cost=0.00..8,500.28 rows=170,864 width=1,585) (actual time=0.066..132.010 rows=170,864 loops=1)

64. 85.558 85.558 ↑ 1.0 170,864 1

Seq Scan on migrate_live1_supplier (cost=0.00..6,791.64 rows=170,864 width=59) (actual time=0.065..85.558 rows=170,864 loops=1)

65. 2.749 10.897 ↑ 4.0 4,009 1

Hash (cost=876.19..876.19 rows=16,219 width=25) (actual time=10.897..10.897 rows=4,009 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 352kB
66. 8.148 8.148 ↑ 1.0 16,219 1

Seq Scan on contractor c_1 (cost=0.00..876.19 rows=16,219 width=25) (actual time=0.008..8.148 rows=16,219 loops=1)

67. 0.003 0.012 ↑ 1.0 1 1

Hash (cost=8.30..8.30 rows=1 width=43) (actual time=0.012..0.012 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
68. 0.009 0.009 ↑ 1.0 1 1

Index Scan using internal_order_pkey on internal_order io_1 (cost=0.29..8.30 rows=1 width=43) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: (id = '00811f57-3855-46cd-a5cd-23d21550f2b0'::uuid)
69. 0.006 13,831.848 ↑ 14,939.0 16 1

Materialize (cost=799,737.43..813,705.34 rows=239,024 width=52) (actual time=13,831.839..13,831.848 rows=16 loops=1)

70. 0.007 13,831.842 ↑ 47,804.8 5 1

Unique (cost=799,737.43..810,717.54 rows=239,024 width=249) (actual time=13,831.835..13,831.842 rows=5 loops=1)

71. 775.381 13,831.835 ↑ 58,560.6 5 1

Sort (cost=799,737.43..800,469.43 rows=292,803 width=249) (actual time=13,831.834..13,831.835 rows=5 loops=1)

  • Sort Key: pi_2.proj_id, proj_2.proj_type, pi_2.internal_order_id, c_2.id, (false), (NULL::uuid), (NULL::uuid), (NULL::character varying), (NULL::uuid), (NULL::character varying), pi_2.budget_sub_cat_high_rise_id, ((bschr_1.name)::character varying(500)) COLLATE thai, (NULL::date), ('SUPPLIER'::text)
  • Sort Method: external sort Disk: 18544kB
72. 122.477 13,056.454 ↑ 1.4 209,352 1

Hash Join (cost=674,603.65..703,095.54 rows=292,803 width=249) (actual time=12,774.349..13,056.454 rows=209,352 loops=1)

  • Hash Cond: (pi_2.proj_id = proj_2.id)
73. 98.498 12,933.887 ↑ 2.8 209,854 1

Unique (cost=674,589.44..695,503.94 rows=597,557 width=245) (actual time=12,774.232..12,933.887 rows=209,854 loops=1)

74. 1,249.604 12,835.389 ↑ 2.8 209,854 1

Sort (cost=674,589.44..676,083.33 rows=597,557 width=245) (actual time=12,774.230..12,835.389 rows=209,854 loops=1)

  • Sort Key: ('SUPPLIER'::text), pi_2.proj_id, pi_2.internal_order_id, c_2.id, (false), (NULL::uuid), (NULL::uuid), (NULL::character varying), (NULL::uuid), (NULL::character varying), pi_2.budget_sub_cat_high_rise_id, ((bschr_1.name)::character varying(500)) COLLATE thai, (NULL::date)
  • Sort Method: external merge Disk: 17752kB
75. 34.012 11,585.785 ↑ 2.8 209,854 1

Append (cost=456,835.35..478,370.68 rows=597,557 width=245) (actual time=9,027.011..11,585.785 rows=209,854 loops=1)

76. 868.634 11,499.745 ↑ 2.9 207,382 1

Unique (cost=456,835.35..465,761.59 rows=595,083 width=729) (actual time=9,027.010..11,499.745 rows=207,382 loops=1)

77. 9,686.842 10,631.111 ↓ 5.2 3,081,840 1

Sort (cost=456,835.35..458,323.05 rows=595,083 width=729) (actual time=9,027.009..10,631.111 rows=3,081,840 loops=1)

  • Sort Key: pi_2.proj_id, pi_2.internal_order_id, c_2.id, pi_2.budget_sub_cat_high_rise_id, ((bschr_1.name)::character varying(500)) COLLATE thai
  • Sort Method: external merge Disk: 263608kB
78. 873.685 944.269 ↓ 5.2 3,081,840 1

Hash Join (cost=3,399.77..13,302.76 rows=595,083 width=729) (actual time=37.813..944.269 rows=3,081,840 loops=1)

  • Hash Cond: (sppm_1.po_item = po_2.po_item)
79. 5.439 54.952 ↓ 5.3 11,551 1

Hash Left Join (cost=1,816.92..2,243.12 rows=2,184 width=568) (actual time=22.170..54.952 rows=11,551 loops=1)

  • Hash Cond: (pi_2.budget_sub_cat_high_rise_id = bschr_1.id)
80. 11.592 49.470 ↓ 5.3 11,551 1

Hash Join (cost=1,815.04..2,235.43 rows=2,184 width=52) (actual time=22.112..49.470 rows=11,551 loops=1)

  • Hash Cond: ((pr_1.id = pi_2.pr_id) AND (sppm_1.pr_item = pi_2.ord))
81. 11.439 16.954 ↑ 1.0 11,551 1

Hash Join (cost=146.12..505.43 rows=11,634 width=24) (actual time=1.159..16.954 rows=11,551 loops=1)

  • Hash Cond: ((sppm_1.pr_doc_no)::text = (pr_1.pr_no)::text)
82. 4.386 4.386 ↑ 1.0 11,634 1

Seq Scan on sap_pr_po_map sppm_1 (cost=0.00..199.34 rows=11,634 width=19) (actual time=0.013..4.386 rows=11,634 loops=1)

83. 0.441 1.129 ↑ 1.0 1,783 1

Hash (cost=123.83..123.83 rows=1,783 width=27) (actual time=1.129..1.129 rows=1,783 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 119kB
84. 0.688 0.688 ↑ 1.0 1,783 1

Seq Scan on pr pr_1 (cost=0.00..123.83 rows=1,783 width=27) (actual time=0.005..0.688 rows=1,783 loops=1)

85. 6.585 20.924 ↑ 1.0 15,157 1

Hash (cost=1,441.57..1,441.57 rows=15,157 width=68) (actual time=20.924..20.924 rows=15,157 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1354kB
86. 14.339 14.339 ↑ 1.0 15,157 1

Seq Scan on pr_item pi_2 (cost=0.00..1,441.57 rows=15,157 width=68) (actual time=0.013..14.339 rows=15,157 loops=1)

87. 0.020 0.043 ↑ 1.0 39 1

Hash (cost=1.39..1.39 rows=39 width=532) (actual time=0.043..0.043 rows=39 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
88. 0.023 0.023 ↑ 1.0 39 1

Seq Scan on budget_sub_category_high_rise bschr_1 (cost=0.00..1.39 rows=39 width=532) (actual time=0.015..0.023 rows=39 loops=1)

89. 2.230 15.632 ↑ 1.0 7,332 1

Hash (cost=1,490.35..1,490.35 rows=7,400 width=20) (actual time=15.632..15.632 rows=7,332 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 437kB
90. 4.282 13.402 ↑ 1.0 7,332 1

Hash Join (cost=1,078.93..1,490.35 rows=7,400 width=20) (actual time=7.148..13.402 rows=7,332 loops=1)

  • Hash Cond: ((po_2.vendor_code)::text = (c_2.code)::text)
91. 2.005 2.005 ↑ 1.0 7,400 1

Seq Scan on sap_po po_2 (cost=0.00..392.00 rows=7,400 width=12) (actual time=0.009..2.005 rows=7,400 loops=1)

92. 2.439 7.115 ↑ 4.0 4,009 1

Hash (cost=876.19..876.19 rows=16,219 width=25) (actual time=7.115..7.115 rows=4,009 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 352kB
93. 4.676 4.676 ↑ 1.0 16,219 1

Seq Scan on contractor c_2 (cost=0.00..876.19 rows=16,219 width=25) (actual time=0.008..4.676 rows=16,219 loops=1)

94. 0.135 8.726 ↑ 1.0 548 1

Subquery Scan on *SELECT* 2_2 (cost=1,136.48..1,146.07 rows=548 width=1,697) (actual time=8.356..8.726 rows=548 loops=1)

95. 0.170 8.591 ↑ 1.0 548 1

Unique (cost=1,136.48..1,140.59 rows=548 width=1,697) (actual time=8.354..8.591 rows=548 loops=1)

96. 0.438 8.421 ↑ 1.0 548 1

Sort (cost=1,136.48..1,137.85 rows=548 width=1,697) (actual time=8.353..8.421 rows=548 loops=1)

  • Sort Key: proj_1_1.id, c_3.id
  • Sort Method: quicksort Memory: 102kB
97. 0.176 7.983 ↑ 1.0 548 1

Hash Join (cost=1,093.13..1,111.56 rows=548 width=1,697) (actual time=7.602..7.983 rows=548 loops=1)

  • Hash Cond: (pc.proj_id = proj_1_1.id)
98. 0.186 7.697 ↑ 1.0 548 1

Hash Join (cost=1,078.93..1,095.85 rows=548 width=32) (actual time=7.466..7.697 rows=548 loops=1)

  • Hash Cond: (pc.contractor_id = c_3.id)
99. 0.082 0.082 ↑ 1.0 548 1

Seq Scan on proj_contractor pc (cost=0.00..15.48 rows=548 width=32) (actual time=0.015..0.082 rows=548 loops=1)

100. 3.730 7.429 ↑ 1.0 16,219 1

Hash (cost=876.19..876.19 rows=16,219 width=16) (actual time=7.429..7.429 rows=16,219 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 889kB
101. 3.699 3.699 ↑ 1.0 16,219 1

Seq Scan on contractor c_3 (cost=0.00..876.19 rows=16,219 width=16) (actual time=0.012..3.699 rows=16,219 loops=1)

102. 0.045 0.110 ↑ 1.0 98 1

Hash (cost=12.98..12.98 rows=98 width=16) (actual time=0.110..0.110 rows=98 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
103. 0.065 0.065 ↑ 1.0 98 1

Seq Scan on proj proj_1_1 (cost=0.00..12.98 rows=98 width=16) (actual time=0.023..0.065 rows=98 loops=1)

104. 0.476 15.571 ↓ 1.2 1,416 1

Subquery Scan on *SELECT* 3_1 (cost=4,031.62..4,077.26 rows=1,141 width=784) (actual time=14.000..15.571 rows=1,416 loops=1)

105. 0.847 15.095 ↓ 1.2 1,416 1

Unique (cost=4,031.62..4,065.85 rows=1,141 width=1,268) (actual time=13.998..15.095 rows=1,416 loops=1)

106. 3.351 14.248 ↓ 1.5 1,670 1

Sort (cost=4,031.62..4,034.47 rows=1,141 width=1,268) (actual time=13.996..14.248 rows=1,670 loops=1)

  • Sort Key: ((po_3.po_type)::character varying(255)), po_3.proj_id, pi_3.internal_order_id, po_3.contractor_id, (CASE WHEN ((po_3.po_type = 'LOW_CONTRACT'::po_type) AND (po_3.contractor_id = io_2.main_contractor_id)) THEN true ELSE false END), po_3.id, (CASE WHEN (po_3.po_type = 'LOW_CONTRACT'::po_type) THEN pi_3.id ELSE NULL::uuid END), (COALESCE(po_3.contract_doc_no, po_3.po_no)), po_3.objective_id, po_3.objective_snap, (COALESCE((pi_3.allow_rebate_retention_bond_start_date - 1), pi_3.retention_end_date))
  • Sort Method: quicksort Memory: 545kB
107. 2.964 10.897 ↓ 1.5 1,670 1

Nested Loop Left Join (cost=239.16..3,973.68 rows=1,141 width=1,268) (actual time=1.028..10.897 rows=1,670 loops=1)

108. 2.650 4.593 ↓ 1.5 1,670 1

Hash Join (cost=238.87..658.97 rows=1,141 width=228) (actual time=0.996..4.593 rows=1,670 loops=1)

  • Hash Cond: (pi_3.po_id = po_3.id)
109. 0.979 0.979 ↑ 1.0 4,044 1

Seq Scan on po_item pi_3 (cost=0.00..409.44 rows=4,044 width=56) (actual time=0.012..0.979 rows=4,044 loops=1)

110. 0.207 0.964 ↓ 1.1 332 1

Hash (cost=235.01..235.01 rows=309 width=188) (actual time=0.964..0.964 rows=332 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 73kB
111. 0.757 0.757 ↓ 1.1 332 1

Seq Scan on po po_3 (cost=0.00..235.01 rows=309 width=188) (actual time=0.016..0.757 rows=332 loops=1)

  • Filter: ((po_type = ANY ('{LOW_CONTRACT,LOW_PO_HOUSE}'::po_type[])) AND (po_status <> ALL ('{DRAFT,DRAFT_SEND_BACK,WAITING_FOR_AUTHORITY_APPROVAL,WAITING_FOR_APPROVAL,CANCELED,REJECTED,APPROVED,PRINTED,WAITING_FOR_RELEASE}'::po_status[])))
  • Rows Removed by Filter: 763
112. 3.340 3.340 ↑ 1.0 1 1,670

Index Scan using internal_order_pkey on internal_order io_2 (cost=0.29..2.89 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1,670)

  • Index Cond: (id = pi_3.internal_order_id)
113. 0.143 24.341 ↑ 1.1 354 1

Subquery Scan on *SELECT* 4_1 (cost=729.63..743.69 rows=375 width=784) (actual time=23.870..24.341 rows=354 loops=1)

114. 0.273 24.198 ↑ 1.1 354 1

Unique (cost=729.63..739.94 rows=375 width=1,268) (actual time=23.868..24.198 rows=354 loops=1)

115. 1.170 23.925 ↑ 1.1 354 1

Sort (cost=729.63..730.57 rows=375 width=1,268) (actual time=23.866..23.925 rows=354 loops=1)

  • Sort Key: ((po_4.po_type)::character varying(255)), po_4.proj_id, po_4.contractor_id, po_4.id, (COALESCE(po_4.contract_doc_no, po_4.po_no)), po_4.objective_id, po_4.objective_snap, po_4.budget_sub_cat_id, ((bschr_2.name)::character varying(500)) COLLATE thai, (COALESCE(((max(po_item.allow_rebate_retention_bond_start_date)) - 1), (max(po_item.retention_end_date))))
  • Sort Method: quicksort Memory: 133kB
116. 0.228 22.755 ↑ 1.1 354 1

Hash Left Join (cost=684.08..713.60 rows=375 width=1,268) (actual time=21.864..22.755 rows=354 loops=1)

  • Hash Cond: (po_4.budget_sub_cat_id = bschr_2.id)
117. 0.368 22.473 ↑ 1.1 354 1

Hash Right Join (cost=682.20..706.99 rows=375 width=212) (actual time=21.795..22.473 rows=354 loops=1)

  • Hash Cond: (po_item.po_id = po_4.id)
118. 7.237 10.533 ↑ 1.0 1,095 1

HashAggregate (cost=439.77..450.72 rows=1,095 width=24) (actual time=10.210..10.533 rows=1,095 loops=1)

  • Group Key: po_item.po_id
119. 3.296 3.296 ↑ 1.0 4,044 1

Seq Scan on po_item (cost=0.00..409.44 rows=4,044 width=24) (actual time=0.011..3.296 rows=4,044 loops=1)

120. 0.169 11.572 ↑ 1.1 354 1

Hash (cost=237.74..237.74 rows=375 width=204) (actual time=11.572..11.572 rows=354 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 83kB
121. 11.403 11.403 ↑ 1.1 354 1

Seq Scan on po po_4 (cost=0.00..237.74 rows=375 width=204) (actual time=0.016..11.403 rows=354 loops=1)

  • Filter: ((po_type = ANY ('{LOW_PO_UTILITY,LOW_CONTRACT_UTILITY,HIGH_PO_BUDGET_CTRL,HIGH_PO_OTHER}'::po_type[])) AND (po_status <> ALL ('{DRAFT,DRAFT_SEND_BACK,WAITING_FOR_AUTHORITY_APPROVAL,WAITING_FOR_APPROVAL,CANCELED,REJECTED,APPROVED,PRINTED,WAITING_FOR_RELEASE}'::po_status[])))
  • Rows Removed by Filter: 741
122. 0.031 0.054 ↑ 1.0 39 1

Hash (cost=1.39..1.39 rows=39 width=532) (actual time=0.054..0.054 rows=39 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
123. 0.023 0.023 ↑ 1.0 39 1

Seq Scan on budget_sub_category_high_rise bschr_2 (cost=0.00..1.39 rows=39 width=532) (actual time=0.014..0.023 rows=39 loops=1)

124. 0.041 3.390 ↑ 2.7 154 1

Subquery Scan on *SELECT* 5_1 (cost=677.91..691.23 rows=410 width=784) (actual time=3.254..3.390 rows=154 loops=1)

125. 0.353 3.349 ↑ 2.7 154 1

HashAggregate (cost=677.91..687.13 rows=410 width=1,268) (actual time=3.252..3.349 rows=154 loops=1)

  • Group Key: (po_5.po_type)::character varying(255), pi_4.proj_id, NULL::uuid, po_5.contractor_id, CASE WHEN ((bschr_3.code)::text = '02'::text) THEN true ELSE false END, po_5.id, pi_4.id, COALESCE(po_5.contract_doc_no, po_5.po_no), po_5.objective_id, po_5.objective_snap, pi_4.budget_sub_cat_id, (bschr_3.name)::character varying(500), COALESCE((pi_4.allow_rebate_retention_bond_start_date - 1), pi_4.retention_end_date)
126. 0.182 2.996 ↑ 2.7 154 1

Hash Join (cost=238.27..664.58 rows=410 width=1,268) (actual time=0.723..2.996 rows=154 loops=1)

  • Hash Cond: (pi_4.budget_sub_cat_id = bschr_3.id)
127. 1.053 2.760 ↑ 2.7 154 1

Hash Join (cost=236.39..656.49 rows=410 width=228) (actual time=0.635..2.760 rows=154 loops=1)

  • Hash Cond: (pi_4.po_id = po_5.id)
128. 1.089 1.089 ↑ 1.0 4,044 1

Seq Scan on po_item pi_4 (cost=0.00..409.44 rows=4,044 width=72) (actual time=0.006..1.089 rows=4,044 loops=1)

129. 0.072 0.618 ↑ 1.0 110 1

Hash (cost=235.01..235.01 rows=111 width=172) (actual time=0.618..0.618 rows=110 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 37kB
130. 0.546 0.546 ↑ 1.0 110 1

Seq Scan on po po_5 (cost=0.00..235.01 rows=111 width=172) (actual time=0.010..0.546 rows=110 loops=1)

  • Filter: ((po_type = 'HIGH_CONTRACT'::po_type) AND (po_status <> ALL ('{DRAFT,DRAFT_SEND_BACK,WAITING_FOR_AUTHORITY_APPROVAL,WAITING_FOR_APPROVAL,CANCELED,REJECTED,APPROVED,PRINTED,WAITING_FOR_RELEASE}'::po_status[])))
  • Rows Removed by Filter: 985
131. 0.031 0.054 ↑ 1.0 39 1

Hash (cost=1.39..1.39 rows=39 width=1,048) (actual time=0.054..0.054 rows=39 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
132. 0.023 0.023 ↑ 1.0 39 1

Seq Scan on budget_sub_category_high_rise bschr_3 (cost=0.00..1.39 rows=39 width=1,048) (actual time=0.014..0.023 rows=39 loops=1)

133. 0.027 0.090 ↑ 1.0 98 1

Hash (cost=12.98..12.98 rows=98 width=20) (actual time=0.090..0.090 rows=98 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
134. 0.063 0.063 ↑ 1.0 98 1

Seq Scan on proj proj_2 (cost=0.00..12.98 rows=98 width=20) (actual time=0.015..0.063 rows=98 loops=1)

Planning time : 69.698 ms
Execution time : 15,512.139 ms