explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hBph : BY IO

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 10,824.749 ↑ 6.2 12 1

Unique (cost=1,120,422.94..1,120,423.69 rows=75 width=53) (actual time=10,824.744..10,824.749 rows=12 loops=1)

2. 0.009 10,824.744 ↑ 6.2 12 1

Sort (cost=1,120,422.94..1,120,423.13 rows=75 width=53) (actual time=10,824.743..10,824.744 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.004 10,824.735 ↑ 6.2 12 1

Subquery Scan on nc_contractor_info (cost=1,120,417.42..1,120,420.61 rows=75 width=53) (actual time=10,824.726..10,824.735 rows=12 loops=1)

4. 0.006 10,824.731 ↑ 6.2 12 1

Unique (cost=1,120,417.42..1,120,419.86 rows=75 width=249) (actual time=10,824.725..10,824.731 rows=12 loops=1)

5. 0.013 10,824.725 ↑ 6.2 12 1

Sort (cost=1,120,417.42..1,120,417.61 rows=75 width=249) (actual time=10,824.724..10,824.725 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.003 10,824.712 ↑ 6.2 12 1

Subquery Scan on v (cost=1,120,413.58..1,120,415.08 rows=75 width=249) (actual time=10,824.706..10,824.712 rows=12 loops=1)

7. 0.019 10,824.709 ↑ 6.2 12 1

HashAggregate (cost=1,120,413.58..1,120,414.33 rows=75 width=249) (actual time=10,824.705..10,824.709 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.003 10,824.690 ↑ 6.2 12 1

Append (cost=2,252.67..1,120,410.96 rows=75 width=249) (actual time=10,824.684..10,824.690 rows=12 loops=1)

9. 0.001 3.979 ↓ 0.0 0 1

Unique (cost=2,252.67..2,255.07 rows=74 width=249) (actual time=3.979..3.979 rows=0 loops=1)

10. 0.004 3.978 ↓ 0.0 0 1

Sort (cost=2,252.67..2,252.85 rows=74 width=249) (actual time=3.978..3.978 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.000 3.974 ↓ 0.0 0 1

Subquery Scan on nc_contractor_info_excl_migrate (cost=2,247.23..2,250.37 rows=74 width=249) (actual time=3.974..3.974 rows=0 loops=1)

12. 0.001 3.974 ↓ 0.0 0 1

Unique (cost=2,247.23..2,249.63 rows=74 width=249) (actual time=3.974..3.974 rows=0 loops=1)

13. 0.004 3.973 ↓ 0.0 0 1

Sort (cost=2,247.23..2,247.41 rows=74 width=249) (actual time=3.973..3.973 rows=0 loops=1)

  • Sort Key: "*SELECT* 1".proj_id, "*SELECT* 1".contractor_id, (false), (NULL::uuid), (NULL::uuid), (NULL::character varying(255)), (NULL::uuid), (NULL::character varying(500)), (NULL::uuid), ((NULL::character varying(501))::character varying(501)) COLLATE thai, (NULL::date), ('PROJ_CONTRACTOR'::text)
  • Sort Method: quicksort Memory: 25kB
14. 0.003 3.969 ↓ 0.0 0 1

Hash Join (cost=2,239.49..2,244.93 rows=74 width=249) (actual time=3.969..3.969 rows=0 loops=1)

  • Hash Cond: ("*SELECT* 1".proj_id = proj.id)
15. 0.003 3.888 ↓ 0.0 0 1

HashAggregate (cost=2,225.49..2,227.89 rows=240 width=245) (actual time=3.888..3.888 rows=0 loops=1)

  • Group Key: ('PROJ_CONTRACTOR'::text), "*SELECT* 1".proj_id, (NULL::uuid), "*SELECT* 1".contractor_id, (false), (NULL::uuid), (NULL::uuid), (NULL::character varying(255)), (NULL::uuid), (NULL::character varying(500)), (NULL::uuid), ((NULL::character varying(501))::character varying(501)), (NULL::date)
16. 0.002 3.885 ↓ 0.0 0 1

Append (cost=0.00..2,217.69 rows=240 width=245) (actual time=3.885..3.885 rows=0 loops=1)

17. 0.000 0.001 ↓ 0.0 0 1

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

18. 0.001 0.001 ↓ 0.0 0 1

HashAggregate (cost=0.00..0.01 rows=1 width=1,697) (actual time=0.001..0.001 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
19. 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
20. 0.000 0.954 ↓ 0.0 0 1

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

21. 0.001 0.954 ↓ 0.0 0 1

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

22. 0.005 0.953 ↓ 0.0 0 1

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

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

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

  • Join Filter: (io.id = pi.internal_order_id)
24. 0.000 0.947 ↓ 0.0 0 1

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

25. 0.947 0.947 ↓ 0.0 0 1

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

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

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

  • Index Cond: (id = pi.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[])))
27. 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)
28. 0.000 0.015 ↓ 0.0 0 1

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

29. 0.001 0.015 ↓ 0.0 0 1

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

30. 0.013 0.014 ↓ 0.0 0 1

Sort (cost=0.01..0.02 rows=0 width=1,268) (actual time=0.014..0.014 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
31. 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
32. 0.001 0.009 ↓ 0.0 0 1

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

33. 0.000 0.008 ↓ 0.0 0 1

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

34. 0.008 0.008 ↓ 0.0 0 1

Sort (cost=0.01..0.02 rows=0 width=1,268) (actual time=0.008..0.008 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
35. 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
36. 0.001 2.904 ↓ 0.0 0 1

Unique (cost=1,776.01..1,778.96 rows=236 width=729) (actual time=2.904..2.904 rows=0 loops=1)

37. 0.004 2.903 ↓ 0.0 0 1

Sort (cost=1,776.01..1,776.60 rows=236 width=729) (actual time=2.903..2.903 rows=0 loops=1)

  • Sort Key: pi_1.proj_id, c.id, pi_1.budget_sub_cat_high_rise_id, ((bschr.name)::character varying(500)) COLLATE thai
  • Sort Method: quicksort Memory: 25kB
38. 0.001 2.899 ↓ 0.0 0 1

Nested Loop (cost=1.42..1,766.71 rows=236 width=729) (actual time=2.898..2.899 rows=0 loops=1)

39. 0.000 2.898 ↓ 0.0 0 1

Nested Loop (cost=1.13..1,597.73 rows=236 width=572) (actual time=2.898..2.898 rows=0 loops=1)

40. 0.001 2.898 ↓ 0.0 0 1

Nested Loop (cost=0.85..1,541.17 rows=1 width=568) (actual time=2.897..2.898 rows=0 loops=1)

41. 0.001 2.897 ↓ 0.0 0 1

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

  • Join Filter: (pi_1.budget_sub_cat_high_rise_id = bschr.id)
42. 0.000 2.896 ↓ 0.0 0 1

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

  • Join Filter: (pi_1.ord = sppm.pr_item)
43. 0.001 2.896 ↓ 0.0 0 1

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

44. 2.895 2.895 ↓ 0.0 0 1

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

  • Filter: (internal_order_id = '00811f57-3855-46cd-a5cd-23d21550f2b0'::uuid)
  • Rows Removed by Filter: 15157
45. 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_1.pr_id)
46. 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
47. 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)

48. 0.000 0.000 ↓ 0.0 0

Index Only Scan using internal_order_pkey on internal_order io_1 (cost=0.29..8.30 rows=1 width=16) (never executed)

  • Index Cond: (id = '00811f57-3855-46cd-a5cd-23d21550f2b0'::uuid)
  • Heap Fetches: 0
49. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (po_item = sppm.po_item)
50. 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_1.vendor_code)::text)
51. 0.022 0.078 ↑ 1.0 62 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
52. 0.056 0.056 ↑ 1.0 62 1

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

  • Filter: (proj_type = 'LOW_RISE'::proj_type)
  • Rows Removed by Filter: 36
53. 0.004 10,820.708 ↓ 12.0 12 1

Unique (cost=1,118,155.12..1,118,155.13 rows=1 width=1,701) (actual time=10,820.704..10,820.708 rows=12 loops=1)

54. 0.041 10,820.704 ↓ 12.0 12 1

Sort (cost=1,118,155.12..1,118,155.12 rows=1 width=1,701) (actual time=10,820.703..10,820.704 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
55. 0.014 10,820.663 ↓ 12.0 12 1

Merge Anti Join (cost=1,097,158.74..1,118,155.11 rows=1 width=1,701) (actual time=10,820.415..10,820.663 rows=12 loops=1)

  • Merge Cond: (proj_1.id = "*SELECT* 1_1".proj_id)
  • Join Filter: ((NOT ((NULL::uuid) IS DISTINCT FROM io_2.id)) AND ("*SELECT* 1_1".contractor_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
56. 0.155 1,047.979 ↓ 12.0 12 1

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

  • Join Filter: (((((migrate_live1_contractor.proj_code)::character varying(255)))::text = (proj_1.code)::text) AND (io_2.proj_id = proj_1.id))
  • Rows Removed by Join Filter: 732
57. 0.086 0.086 ↑ 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.007..0.086 rows=62 loops=1)

  • Filter: (proj_type = 'LOW_RISE'::proj_type)
  • Rows Removed by Filter: 36
58. 0.099 1,047.738 ↓ 1.7 12 62

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

59. 21.616 1,047.639 ↓ 1.7 12 1

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

  • Hash Cond: ((((migrate_live1_contractor.io_code)::character varying(255)))::text = (io_2.io_code)::text)
60. 62.785 1,025.992 ↑ 1.0 187,734 1

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

  • Hash Cond: ((((migrate_live1_contractor.code)::character varying(255)))::text = (c_1.code)::text)
61. 59.696 958.659 ↑ 1.0 193,540 1

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

62. 778.657 898.963 ↑ 1.0 193,551 1

Sort (cost=293,715.08..294,198.95 rows=193,551 width=1,585) (actual time=862.257..898.963 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
63. 22.503 120.306 ↑ 1.0 193,551 1

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

64. 5.295 5.295 ↑ 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.012..5.295 rows=22,687 loops=1)

65. 28.309 92.508 ↑ 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.814..92.508 rows=170,864 loops=1)

66. 64.199 64.199 ↑ 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.813..64.199 rows=170,864 loops=1)

67. 1.795 4.548 ↑ 4.0 4,009 1

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

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

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

69. 0.024 0.031 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
70. 0.007 0.007 ↑ 1.0 1 1

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

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

Materialize (cost=802,356.28..816,324.19 rows=239,024 width=52) (actual time=9,772.663..9,772.670 rows=16 loops=1)

72. 0.006 9,772.664 ↑ 47,804.8 5 1

Unique (cost=802,356.28..813,336.39 rows=239,024 width=249) (actual time=9,772.659..9,772.664 rows=5 loops=1)

73. 523.436 9,772.658 ↑ 58,560.6 5 1

Sort (cost=802,356.28..803,088.29 rows=292,803 width=249) (actual time=9,772.657..9,772.658 rows=5 loops=1)

  • Sort Key: "*SELECT* 1_1".proj_id, proj_2.proj_type, (NULL::uuid), "*SELECT* 1_1".contractor_id, (false), (NULL::uuid), (NULL::uuid), (NULL::character varying(255)), (NULL::uuid), (NULL::character varying(500)), (NULL::uuid), ((NULL::character varying(501))::character varying(501)) COLLATE thai, (NULL::date), ('PROJ_CONTRACTOR'::text)
  • Sort Method: external sort Disk: 18544kB
74. 82.689 9,249.222 ↑ 1.4 209,352 1

Hash Join (cost=677,222.50..705,714.39 rows=292,803 width=249) (actual time=9,050.123..9,249.222 rows=209,352 loops=1)

  • Hash Cond: ("*SELECT* 1_1".proj_id = proj_2.id)
75. 69.259 9,166.469 ↑ 2.8 209,854 1

Unique (cost=677,208.30..698,122.79 rows=597,557 width=245) (actual time=9,050.048..9,166.469 rows=209,854 loops=1)

76. 907.076 9,097.210 ↑ 2.8 209,854 1

Sort (cost=677,208.30..678,702.19 rows=597,557 width=245) (actual time=9,050.047..9,097.210 rows=209,854 loops=1)

  • Sort Key: ('PROJ_CONTRACTOR'::text), "*SELECT* 1_1".proj_id, (NULL::uuid), "*SELECT* 1_1".contractor_id, (false), (NULL::uuid), (NULL::uuid), (NULL::character varying(255)), (NULL::uuid), (NULL::character varying(500)), (NULL::uuid), ((NULL::character varying(501))::character varying(501)) COLLATE thai, (NULL::date)
  • Sort Method: external sort Disk: 17744kB
77. 30.724 8,190.134 ↑ 2.8 209,854 1

Append (cost=1,136.48..480,989.53 rows=597,557 width=245) (actual time=8.624..8,190.134 rows=209,854 loops=1)

78. 0.113 8.901 ↑ 1.0 548 1

Subquery Scan on *SELECT* 1_1 (cost=1,136.48..1,146.07 rows=548 width=1,697) (actual time=8.623..8.901 rows=548 loops=1)

79. 0.117 8.788 ↑ 1.0 548 1

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

80. 0.444 8.671 ↑ 1.0 548 1

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

  • Sort Key: proj_1_1.id, c_2.id
  • Sort Method: quicksort Memory: 102kB
81. 0.176 8.227 ↑ 1.0 548 1

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

  • Hash Cond: (pc.proj_id = proj_1_1.id)
82. 0.286 7.996 ↑ 1.0 548 1

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

  • Hash Cond: (pc.contractor_id = c_2.id)
83. 0.093 0.093 ↑ 1.0 548 1

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

84. 3.883 7.617 ↑ 1.0 16,219 1

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

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

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

86. 0.036 0.055 ↑ 1.0 98 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
87. 0.019 0.019 ↑ 1.0 98 1

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

88. 0.380 14.161 ↓ 1.2 1,416 1

Subquery Scan on *SELECT* 2_2 (cost=4,031.62..4,077.26 rows=1,141 width=784) (actual time=12.887..14.161 rows=1,416 loops=1)

89. 0.666 13.781 ↓ 1.2 1,416 1

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

90. 3.070 13.115 ↓ 1.5 1,670 1

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

  • Sort Key: ((po_2.po_type)::character varying(255)), po_2.proj_id, pi_2.internal_order_id, po_2.contractor_id, (CASE WHEN ((po_2.po_type = 'LOW_CONTRACT'::po_type) AND (po_2.contractor_id = io_3.main_contractor_id)) THEN true ELSE false END), po_2.id, (CASE WHEN (po_2.po_type = 'LOW_CONTRACT'::po_type) THEN pi_2.id ELSE NULL::uuid END), (COALESCE(po_2.contract_doc_no, po_2.po_no)), po_2.objective_id, po_2.objective_snap, (COALESCE((pi_2.allow_rebate_retention_bond_start_date - 1), pi_2.retention_end_date))
  • Sort Method: quicksort Memory: 545kB
91. 2.589 10.045 ↓ 1.5 1,670 1

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

92. 2.556 4.116 ↓ 1.5 1,670 1

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

  • Hash Cond: (pi_2.po_id = po_2.id)
93. 0.907 0.907 ↑ 1.0 4,044 1

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

94. 0.142 0.653 ↓ 1.1 332 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 73kB
95. 0.511 0.511 ↓ 1.1 332 1

Seq Scan on po po_2 (cost=0.00..235.01 rows=309 width=188) (actual time=0.013..0.511 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
96. 3.340 3.340 ↑ 1.0 1 1,670

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

  • Index Cond: (id = pi_2.internal_order_id)
97. 0.091 7.616 ↑ 1.1 354 1

Subquery Scan on *SELECT* 3_1 (cost=729.63..743.69 rows=375 width=784) (actual time=7.335..7.616 rows=354 loops=1)

98. 0.161 7.525 ↑ 1.1 354 1

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

99. 0.722 7.364 ↑ 1.1 354 1

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

  • Sort Key: ((po_3.po_type)::character varying(255)), po_3.proj_id, po_3.contractor_id, po_3.id, (COALESCE(po_3.contract_doc_no, po_3.po_no)), po_3.objective_id, po_3.objective_snap, po_3.budget_sub_cat_id, ((bschr_1.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
100. 0.149 6.642 ↑ 1.1 354 1

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

  • Hash Cond: (po_3.budget_sub_cat_id = bschr_1.id)
101. 0.257 6.449 ↑ 1.1 354 1

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

  • Hash Cond: (po_item.po_id = po_3.id)
102. 4.413 5.333 ↑ 1.0 1,095 1

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

  • Group Key: po_item.po_id
103. 0.920 0.920 ↑ 1.0 4,044 1

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

104. 0.203 0.859 ↑ 1.1 354 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 83kB
105. 0.656 0.656 ↑ 1.1 354 1

Seq Scan on po po_3 (cost=0.00..237.74 rows=375 width=204) (actual time=0.018..0.656 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
106. 0.024 0.044 ↑ 1.0 39 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
107. 0.020 0.020 ↑ 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.013..0.020 rows=39 loops=1)

108. 0.028 2.226 ↑ 2.7 154 1

Subquery Scan on *SELECT* 4_1 (cost=677.91..691.23 rows=410 width=784) (actual time=2.139..2.226 rows=154 loops=1)

109. 0.217 2.198 ↑ 2.7 154 1

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

  • Group Key: (po_4.po_type)::character varying(255), pi_3.proj_id, NULL::uuid, po_4.contractor_id, CASE WHEN ((bschr_2.code)::text = '02'::text) THEN true ELSE false END, po_4.id, pi_3.id, COALESCE(po_4.contract_doc_no, po_4.po_no), po_4.objective_id, po_4.objective_snap, pi_3.budget_sub_cat_id, (bschr_2.name)::character varying(500), COALESCE((pi_3.allow_rebate_retention_bond_start_date - 1), pi_3.retention_end_date)
110. 0.101 1.981 ↑ 2.7 154 1

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

  • Hash Cond: (pi_3.budget_sub_cat_id = bschr_2.id)
111. 0.693 1.859 ↑ 2.7 154 1

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

  • Hash Cond: (pi_3.po_id = po_4.id)
112. 0.774 0.774 ↑ 1.0 4,044 1

Seq Scan on po_item pi_3 (cost=0.00..409.44 rows=4,044 width=72) (actual time=0.004..0.774 rows=4,044 loops=1)

113. 0.054 0.392 ↑ 1.0 110 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 37kB
114. 0.338 0.338 ↑ 1.0 110 1

Seq Scan on po po_4 (cost=0.00..235.01 rows=111 width=172) (actual time=0.008..0.338 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
115. 0.010 0.021 ↑ 1.0 39 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
116. 0.011 0.011 ↑ 1.0 39 1

Seq Scan on budget_sub_category_high_rise bschr_2 (cost=0.00..1.39 rows=39 width=1,048) (actual time=0.005..0.011 rows=39 loops=1)

117. 615.834 8,126.506 ↑ 2.9 207,382 1

Unique (cost=459,454.20..468,380.44 rows=595,083 width=729) (actual time=6,325.479..8,126.506 rows=207,382 loops=1)

118. 6,819.479 7,510.672 ↓ 5.2 3,081,840 1

Sort (cost=459,454.20..460,941.91 rows=595,083 width=729) (actual time=6,325.477..7,510.672 rows=3,081,840 loops=1)

  • Sort Key: pi_4.proj_id, io_4.id, c_3.id, pi_4.budget_sub_cat_high_rise_id, ((bschr_3.name)::character varying(500)) COLLATE thai
  • Sort Method: external merge Disk: 263608kB
119. 610.506 691.193 ↓ 5.2 3,081,840 1

Hash Join (cost=3,400.06..15,921.61 rows=595,083 width=729) (actual time=31.088..691.193 rows=3,081,840 loops=1)

  • Hash Cond: (sppm_1.po_item = po_5.po_item)
120. 3.707 69.636 ↓ 5.3 11,551 1

Nested Loop Left Join (cost=1,817.21..4,861.97 rows=2,184 width=568) (actual time=19.987..69.636 rows=11,551 loops=1)

121. 3.698 42.827 ↓ 5.3 11,551 1

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

  • Hash Cond: (pi_4.budget_sub_cat_high_rise_id = bschr_3.id)
122. 8.785 39.072 ↓ 5.3 11,551 1

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

  • Hash Cond: ((pr_1.id = pi_4.pr_id) AND (sppm_1.pr_item = pi_4.ord))
123. 7.673 11.417 ↑ 1.0 11,551 1

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

  • Hash Cond: ((sppm_1.pr_doc_no)::text = (pr_1.pr_no)::text)
124. 2.832 2.832 ↑ 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.012..2.832 rows=11,634 loops=1)

125. 0.381 0.912 ↑ 1.0 1,783 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 119kB
126. 0.531 0.531 ↑ 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.531 rows=1,783 loops=1)

127. 5.980 18.870 ↑ 1.0 15,157 1

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

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

Seq Scan on pr_item pi_4 (cost=0.00..1,441.57 rows=15,157 width=68) (actual time=0.006..12.890 rows=15,157 loops=1)

129. 0.037 0.057 ↑ 1.0 39 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
130. 0.020 0.020 ↑ 1.0 39 1

Seq Scan on budget_sub_category_high_rise bschr_3 (cost=0.00..1.39 rows=39 width=532) (actual time=0.013..0.020 rows=39 loops=1)

131. 23.102 23.102 ↑ 1.0 1 11,551

Index Only Scan using internal_order_pkey on internal_order io_4 (cost=0.29..1.20 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=11,551)

  • Index Cond: (id = pi_4.internal_order_id)
  • Heap Fetches: 9568
132. 1.402 11.051 ↑ 1.0 7,332 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 437kB
133. 2.690 9.649 ↑ 1.0 7,332 1

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

  • Hash Cond: ((po_5.vendor_code)::text = (c_3.code)::text)
134. 1.141 1.141 ↑ 1.0 7,400 1

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

135. 2.138 5.818 ↑ 4.0 4,009 1

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

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

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

137. 0.019 0.064 ↑ 1.0 98 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
138. 0.045 0.045 ↑ 1.0 98 1

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

Planning time : 15.501 ms
Execution time : 10,937.713 ms