explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ualK

Settings
# exclusive inclusive rows x rows loops node
1. 0.172 3,643.323 ↓ 11.6 721 1

Unique (cost=311,542.92..311,543.54 rows=62 width=53) (actual time=3,643.098..3,643.323 rows=721 loops=1)

2. 0.334 3,643.151 ↓ 11.6 721 1

Sort (cost=311,542.92..311,543.07 rows=62 width=53) (actual time=3,643.098..3,643.151 rows=721 loops=1)

  • Sort Key: nc_contractor_info.proj_id, nc_contractor_info.internal_order_id, nc_contractor_info.main_contractor, nc_contractor_info.warranty_expiry DESC
  • Sort Method: quicksort Memory: 125kB
3. 0.148 3,642.817 ↓ 11.6 721 1

Subquery Scan on nc_contractor_info (cost=311,538.43..311,541.07 rows=62 width=53) (actual time=3,642.344..3,642.817 rows=721 loops=1)

4. 0.263 3,642.669 ↓ 11.6 721 1

Unique (cost=311,538.43..311,540.45 rows=62 width=249) (actual time=3,642.342..3,642.669 rows=721 loops=1)

5. 0.600 3,642.406 ↓ 11.6 721 1

Sort (cost=311,538.43..311,538.59 rows=62 width=249) (actual time=3,642.342..3,642.406 rows=721 loops=1)

  • Sort Key: v.proj_id, v.internal_order_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: 126kB
6. 0.126 3,641.806 ↓ 11.6 721 1

Subquery Scan on v (cost=311,535.35..311,536.59 rows=62 width=249) (actual time=3,641.423..3,641.806 rows=721 loops=1)

7. 0.813 3,641.680 ↓ 11.6 721 1

HashAggregate (cost=311,535.35..311,535.97 rows=62 width=249) (actual time=3,641.422..3,641.680 rows=721 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.092 3,640.867 ↓ 11.6 721 1

Append (cost=3,179.96..311,533.18 rows=62 width=249) (actual time=566.697..3,640.867 rows=721 loops=1)

9. 0.228 566.974 ↓ 11.4 663 1

Unique (cost=3,179.96..3,181.84 rows=58 width=249) (actual time=566.696..566.974 rows=663 loops=1)

10. 0.372 566.746 ↓ 11.4 663 1

Sort (cost=3,179.96..3,180.10 rows=58 width=249) (actual time=566.695..566.746 rows=663 loops=1)

  • Sort Key: nc_contractor_info_excl_migrate.source, nc_contractor_info_excl_migrate.proj_id, nc_contractor_info_excl_migrate.internal_order_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: 118kB
11. 0.159 566.374 ↓ 11.4 663 1

Subquery Scan on nc_contractor_info_excl_migrate (cost=3,175.79..3,178.26 rows=58 width=249) (actual time=565.927..566.374 rows=663 loops=1)

12. 0.232 566.215 ↓ 11.4 663 1

Unique (cost=3,175.79..3,177.68 rows=58 width=249) (actual time=565.925..566.215 rows=663 loops=1)

13. 0.559 565.983 ↓ 11.4 663 1

Sort (cost=3,175.79..3,175.94 rows=58 width=249) (actual time=565.925..565.983 rows=663 loops=1)

  • Sort Key: "*SELECT* 1".proj_id, (NULL::uuid), (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: 118kB
14. 0.214 565.424 ↓ 11.4 663 1

Hash Join (cost=3,172.03..3,174.10 rows=58 width=249) (actual time=564.987..565.424 rows=663 loops=1)

  • Hash Cond: ("*SELECT* 1".proj_id = proj.id)
15. 0.877 564.959 ↓ 8.5 769 1

HashAggregate (cost=3,158.03..3,158.94 rows=91 width=245) (actual time=564.718..564.959 rows=769 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.101 564.082 ↓ 8.5 769 1

Append (cost=31.24..3,155.07 rows=91 width=245) (actual time=557.605..564.082 rows=769 loops=1)

17. 0.000 0.175 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=31.24..31.27 rows=2 width=1,697) (actual time=0.175..0.175 rows=0 loops=1)

18. 0.001 0.175 ↓ 0.0 0 1

Unique (cost=31.24..31.25 rows=2 width=1,697) (actual time=0.175..0.175 rows=0 loops=1)

19. 0.017 0.174 ↓ 0.0 0 1

Sort (cost=31.24..31.24 rows=2 width=1,697) (actual time=0.174..0.174 rows=0 loops=1)

  • Sort Key: proj_1.id
  • Sort Method: quicksort Memory: 25kB
20. 0.002 0.157 ↓ 0.0 0 1

Nested Loop (cost=9.94..31.23 rows=2 width=1,697) (actual time=0.157..0.157 rows=0 loops=1)

21. 0.078 0.078 ↑ 1.0 1 1

Index Only Scan using contractor_pkey on contractor c (cost=0.29..8.30 rows=1 width=16) (actual time=0.077..0.078 rows=1 loops=1)

  • Index Cond: (id = '072c2e39-cbfe-4084-b93c-dd880b8742cf'::uuid)
  • Heap Fetches: 1
22. 0.016 0.077 ↓ 0.0 0 1

Hash Join (cost=9.66..22.91 rows=2 width=32) (actual time=0.077..0.077 rows=0 loops=1)

  • Hash Cond: (proj_1.id = pc.proj_id)
23. 0.005 0.005 ↑ 98.0 1 1

Seq Scan on proj proj_1 (cost=0.00..12.98 rows=98 width=16) (actual time=0.005..0.005 rows=1 loops=1)

24. 0.000 0.056 ↓ 0.0 0 1

Hash (cost=9.63..9.63 rows=2 width=32) (actual time=0.056..0.056 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
25. 0.006 0.056 ↓ 0.0 0 1

Bitmap Heap Scan on proj_contractor pc (cost=4.29..9.63 rows=2 width=32) (actual time=0.056..0.056 rows=0 loops=1)

  • Recheck Cond: (contractor_id = '072c2e39-cbfe-4084-b93c-dd880b8742cf'::uuid)
26. 0.050 0.050 ↓ 0.0 0 1

Bitmap Index Scan on proj_contractor_uindex (cost=0.00..4.29 rows=2 width=0) (actual time=0.050..0.050 rows=0 loops=1)

  • Index Cond: (contractor_id = '072c2e39-cbfe-4084-b93c-dd880b8742cf'::uuid)
27. 0.001 11.026 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=268.56..268.71 rows=4 width=784) (actual time=11.026..11.026 rows=0 loops=1)

28. 0.001 11.025 ↓ 0.0 0 1

Unique (cost=268.56..268.67 rows=4 width=1,268) (actual time=11.025..11.025 rows=0 loops=1)

29. 0.019 11.024 ↓ 0.0 0 1

Sort (cost=268.56..268.57 rows=4 width=1,268) (actual time=11.024..11.024 rows=0 loops=1)

  • Sort Key: ((po.po_type)::character varying(255)), po.proj_id, pi.internal_order_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
30. 0.000 11.005 ↓ 0.0 0 1

Nested Loop Left Join (cost=4.60..268.52 rows=4 width=1,268) (actual time=11.005..11.005 rows=0 loops=1)

31. 0.001 11.005 ↓ 0.0 0 1

Nested Loop (cost=4.31..256.90 rows=4 width=228) (actual time=11.005..11.005 rows=0 loops=1)

32. 11.004 11.004 ↓ 0.0 0 1

Seq Scan on po (cost=0.00..237.74 rows=1 width=188) (actual time=11.004..11.004 rows=0 loops=1)

  • Filter: ((po_type = ANY ('{LOW_CONTRACT,LOW_PO_HOUSE}'::po_type[])) AND (contractor_id = '072c2e39-cbfe-4084-b93c-dd880b8742cf'::uuid) 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: 1095
33. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on po_item pi (cost=4.31..19.11 rows=4 width=56) (never executed)

  • Recheck Cond: (po_id = po.id)
34. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on po_item_po_id_ord_key (cost=0.00..4.31 rows=4 width=0) (never executed)

  • Index Cond: (po_id = po.id)
35. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = pi.internal_order_id)
36. 0.001 0.356 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=706.95..706.98 rows=1 width=784) (actual time=0.356..0.356 rows=0 loops=1)

37. 0.001 0.355 ↓ 0.0 0 1

Unique (cost=706.95..706.97 rows=1 width=1,268) (actual time=0.354..0.355 rows=0 loops=1)

38. 0.025 0.354 ↓ 0.0 0 1

Sort (cost=706.95..706.95 rows=1 width=1,268) (actual time=0.354..0.354 rows=0 loops=1)

  • Sort Key: ((po_1.po_type)::character varying(255)), po_1.proj_id, po_1.id, (COALESCE(po_1.contract_doc_no, po_1.po_no)), po_1.objective_id, po_1.objective_snap, po_1.budget_sub_cat_id, ((bschr.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: 25kB
39. 0.000 0.329 ↓ 0.0 0 1

Nested Loop Left Join (cost=680.26..706.94 rows=1 width=1,268) (actual time=0.329..0.329 rows=0 loops=1)

  • Join Filter: (po_1.budget_sub_cat_id = bschr.id)
40. 0.020 0.329 ↓ 0.0 0 1

Hash Right Join (cost=680.26..705.05 rows=1 width=212) (actual time=0.328..0.329 rows=0 loops=1)

  • Hash Cond: (po_item.po_id = po_1.id)
41. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=439.77..450.72 rows=1,095 width=24) (never executed)

  • Group Key: po_item.po_id
42. 0.000 0.000 ↓ 0.0 0

Seq Scan on po_item (cost=0.00..409.44 rows=4,044 width=24) (never executed)

43. 0.000 0.309 ↓ 0.0 0 1

Hash (cost=240.48..240.48 rows=1 width=204) (actual time=0.309..0.309 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
44. 0.309 0.309 ↓ 0.0 0 1

Seq Scan on po po_1 (cost=0.00..240.48 rows=1 width=204) (actual time=0.309..0.309 rows=0 loops=1)

  • Filter: ((contractor_id = '072c2e39-cbfe-4084-b93c-dd880b8742cf'::uuid) AND (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: 1095
45. 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)

46. 0.000 0.261 ↓ 0.0 0 1

Subquery Scan on *SELECT* 4 (cost=257.63..257.79 rows=4 width=784) (actual time=0.261..0.261 rows=0 loops=1)

47. 0.001 0.261 ↓ 0.0 0 1

Unique (cost=257.63..257.75 rows=4 width=1,268) (actual time=0.260..0.261 rows=0 loops=1)

48. 0.017 0.260 ↓ 0.0 0 1

Sort (cost=257.63..257.64 rows=4 width=1,268) (actual time=0.260..0.260 rows=0 loops=1)

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

Nested Loop (cost=4.45..257.59 rows=4 width=1,268) (actual time=0.243..0.243 rows=0 loops=1)

50. 0.001 0.243 ↓ 0.0 0 1

Nested Loop (cost=4.31..256.90 rows=4 width=228) (actual time=0.243..0.243 rows=0 loops=1)

51. 0.242 0.242 ↓ 0.0 0 1

Seq Scan on po po_2 (cost=0.00..237.74 rows=1 width=172) (actual time=0.242..0.242 rows=0 loops=1)

  • Filter: ((po_type = 'HIGH_CONTRACT'::po_type) AND (contractor_id = '072c2e39-cbfe-4084-b93c-dd880b8742cf'::uuid) 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: 1095
52. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on po_item pi_1 (cost=4.31..19.11 rows=4 width=72) (never executed)

  • Recheck Cond: (po_id = po_2.id)
53. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on po_item_po_id_ord_key (cost=0.00..4.31 rows=4 width=0) (never executed)

  • Index Cond: (po_id = po_2.id)
54. 0.000 0.000 ↓ 0.0 0

Index Scan using budget_sub_category_high_rise_pkey on budget_sub_category_high_rise bschr_1 (cost=0.14..0.16 rows=1 width=1,048) (never executed)

  • Index Cond: (id = pi_1.budget_sub_cat_id)
55. 3.842 552.163 ↓ 9.6 769 1

Unique (cost=1,888.52..1,889.52 rows=80 width=729) (actual time=545.783..552.163 rows=769 loops=1)

56. 26.561 548.321 ↓ 275.7 22,056 1

Sort (cost=1,888.52..1,888.72 rows=80 width=729) (actual time=545.781..548.321 rows=22,056 loops=1)

  • Sort Key: pi_2.proj_id, pi_2.internal_order_id, pi_2.budget_sub_cat_high_rise_id, ((bschr_2.name)::character varying(500)) COLLATE thai
  • Sort Method: quicksort Memory: 4027kB
57. 17.315 521.760 ↓ 275.7 22,056 1

Nested Loop Left Join (cost=9.30..1,885.99 rows=80 width=729) (actual time=0.152..521.760 rows=22,056 loops=1)

58. 26.923 504.445 ↓ 275.7 22,056 1

Nested Loop (cost=9.16..1,873.33 rows=80 width=64) (actual time=0.146..504.445 rows=22,056 loops=1)

59. 22.181 433.410 ↓ 51.5 22,056 1

Nested Loop (cost=8.88..655.86 rows=428 width=36) (actual time=0.111..433.410 rows=22,056 loops=1)

60. 4.160 164.895 ↓ 52.3 22,394 1

Nested Loop (cost=8.60..508.40 rows=428 width=31) (actual time=0.072..164.895 rows=22,394 loops=1)

61. 2.618 4.301 ↓ 214.0 214 1

Hash Join (cost=8.31..419.74 rows=1 width=20) (actual time=0.050..4.301 rows=214 loops=1)

  • Hash Cond: ((po_3.vendor_code)::text = (c_1.code)::text)
62. 1.667 1.667 ↑ 1.0 7,400 1

Seq Scan on sap_po po_3 (cost=0.00..392.00 rows=7,400 width=12) (actual time=0.005..1.667 rows=7,400 loops=1)

63. 0.002 0.016 ↑ 1.0 1 1

Hash (cost=8.30..8.30 rows=1 width=25) (actual time=0.016..0.016 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
64. 0.014 0.014 ↑ 1.0 1 1

Index Scan using contractor_pkey on contractor c_1 (cost=0.29..8.30 rows=1 width=25) (actual time=0.013..0.014 rows=1 loops=1)

  • Index Cond: (id = '072c2e39-cbfe-4084-b93c-dd880b8742cf'::uuid)
65. 156.434 156.434 ↓ 2.0 105 214

Index Only Scan using sap_pr_po_map_uindex on sap_pr_po_map sppm (cost=0.29..88.14 rows=52 width=19) (actual time=0.086..0.731 rows=105 loops=214)

  • Index Cond: (po_item = po_3.po_item)
  • Heap Fetches: 22394
66. 246.334 246.334 ↑ 1.0 1 22,394

Index Scan using pr_index_pr_code on pr (cost=0.28..0.33 rows=1 width=27) (actual time=0.011..0.011 rows=1 loops=22,394)

  • Index Cond: ((pr_no)::text = (sppm.pr_doc_no)::text)
67. 44.112 44.112 ↑ 1.0 1 22,056

Index Scan using pr_item_pr_id_ord_key on pr_item pi_2 (cost=0.29..2.84 rows=1 width=68) (actual time=0.002..0.002 rows=1 loops=22,056)

  • Index Cond: ((pr_id = pr.id) AND (ord = sppm.pr_item))
68. 0.000 0.000 ↓ 0.0 0 22,056

Index Scan using budget_sub_category_high_rise_pkey on budget_sub_category_high_rise bschr_2 (cost=0.14..0.16 rows=1 width=532) (actual time=0.000..0.000 rows=0 loops=22,056)

  • Index Cond: (pi_2.budget_sub_cat_high_rise_id = id)
69. 0.018 0.251 ↑ 1.0 62 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
70. 0.233 0.233 ↑ 1.0 62 1

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

  • Filter: (proj_type = 'LOW_RISE'::proj_type)
  • Rows Removed by Filter: 36
71. 0.041 3,073.801 ↓ 14.5 58 1

Unique (cost=308,350.66..308,350.72 rows=4 width=1,701) (actual time=3,073.747..3,073.801 rows=58 loops=1)

72. 0.806 3,073.760 ↓ 46.8 187 1

Sort (cost=308,350.66..308,350.67 rows=4 width=1,701) (actual time=3,073.745..3,073.760 rows=187 loops=1)

  • Sort Key: ('MIGRATE_LIVE1_CONTRACTOR'::text), proj_2.id, io_1.id, (COALESCE(migrate_live1_contractor.is_main, false)), migrate_live1_contractor.warranty_expiry
  • Sort Method: quicksort Memory: 51kB
73. 1.228 3,072.954 ↓ 46.8 187 1

Merge Anti Join (cost=296,900.79..308,350.62 rows=4 width=1,701) (actual time=2,012.470..3,072.954 rows=187 loops=1)

  • Merge Cond: (proj_2.id = "*SELECT* 1_1".proj_id)
  • Join Filter: ((NOT ((NULL::uuid) IS DISTINCT FROM io_1.id)) AND ("*SELECT* 1_1".contractor_id = c_2.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: 1954
74. 650.924 2,485.656 ↓ 60.8 243 1

Nested Loop Left Join (cost=293,723.53..305,168.70 rows=4 width=89) (actual time=1,427.007..2,485.656 rows=243 loops=1)

  • Join Filter: (((((migrate_live1_contractor.io_code)::character varying(255)))::text = (io_1.io_code)::text) AND (io_1.proj_id = proj_2.id))
  • Rows Removed by Join Filter: 5329298
75. 3.792 1,272.187 ↓ 60.8 243 1

Nested Loop (cost=293,723.53..299,598.76 rows=4 width=589) (actual time=1,266.052..1,272.187 rows=243 loops=1)

  • Join Filter: ((((migrate_live1_contractor.proj_code)::character varying(255)))::text = (proj_2.code)::text)
  • Rows Removed by Join Filter: 26975
76. 0.123 0.123 ↑ 1.0 62 1

Index Scan using proj_pkey on proj proj_2 (cost=0.14..57.53 rows=62 width=25) (actual time=0.018..0.123 rows=62 loops=1)

  • Filter: (proj_type = 'LOW_RISE'::proj_type)
  • Rows Removed by Filter: 36
77. 2.245 1,268.272 ↓ 36.6 439 62

Materialize (cost=293,723.39..299,530.11 rows=12 width=1,085) (actual time=20.414..20.456 rows=439 loops=62)

78. 24.284 1,266.027 ↓ 36.6 439 1

Merge Join (cost=293,723.39..299,530.05 rows=12 width=1,085) (actual time=1,265.600..1,266.027 rows=439 loops=1)

  • Merge Cond: ((((migrate_live1_contractor.code)::character varying(255)))::text = (c_2.code)::text)
79. 64.810 1,241.622 ↑ 1.1 176,301 1

Unique (cost=293,715.08..297,102.22 rows=193,551 width=1,585) (actual time=1,138.834..1,241.622 rows=176,301 loops=1)

80. 885.610 1,176.812 ↑ 1.1 176,306 1

Sort (cost=293,715.08..294,198.95 rows=193,551 width=1,585) (actual time=1,138.833..1,176.812 rows=176,306 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
81. 26.506 291.202 ↑ 1.0 193,551 1

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

82. 29.491 29.491 ↑ 1.0 22,687 1

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

83. 38.148 235.205 ↑ 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=1.862..235.205 rows=170,864 loops=1)

84. 197.057 197.057 ↑ 1.0 170,864 1

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

85. 0.051 0.121 ↑ 1.0 1 1

Sort (cost=8.31..8.32 rows=1 width=25) (actual time=0.120..0.121 rows=1 loops=1)

  • Sort Key: c_2.code COLLATE thai
  • Sort Method: quicksort Memory: 25kB
86. 0.070 0.070 ↑ 1.0 1 1

Index Scan using contractor_pkey on contractor c_2 (cost=0.29..8.30 rows=1 width=25) (actual time=0.069..0.070 rows=1 loops=1)

  • Index Cond: (id = '072c2e39-cbfe-4084-b93c-dd880b8742cf'::uuid)
87. 414.250 562.545 ↑ 1.3 21,932 243

Materialize (cost=0.00..3,695.53 rows=27,769 width=43) (actual time=0.001..2.315 rows=21,932 loops=243)

88. 148.295 148.295 ↑ 1.0 27,769 1

Seq Scan on internal_order io_1 (cost=0.00..3,556.69 rows=27,769 width=43) (actual time=0.091..148.295 rows=27,769 loops=1)

89. 0.271 586.070 ↓ 26.9 2,449 1

Materialize (cost=3,177.26..3,181.58 rows=91 width=52) (actual time=585.458..586.070 rows=2,449 loops=1)

90. 0.272 585.799 ↓ 5.7 515 1

Unique (cost=3,177.26..3,180.45 rows=91 width=249) (actual time=585.451..585.799 rows=515 loops=1)

91. 0.808 585.527 ↓ 5.7 515 1

Sort (cost=3,177.26..3,177.49 rows=91 width=249) (actual time=585.450..585.527 rows=515 loops=1)

  • Sort Key: "*SELECT* 1_1".proj_id, proj_3.proj_type, (NULL::uuid), (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: 138kB
92. 0.229 584.719 ↓ 8.4 768 1

Hash Join (cost=3,172.23..3,174.30 rows=91 width=249) (actual time=584.267..584.719 rows=768 loops=1)

  • Hash Cond: ("*SELECT* 1_1".proj_id = proj_3.id)
93. 0.717 584.423 ↓ 8.5 769 1

HashAggregate (cost=3,158.03..3,158.94 rows=91 width=245) (actual time=584.185..584.423 rows=769 loops=1)

  • Group 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)), (NULL::date)
94. 0.096 583.706 ↓ 8.5 769 1

Append (cost=31.24..3,155.07 rows=91 width=245) (actual time=578.311..583.706 rows=769 loops=1)

95. 0.001 0.038 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1_1 (cost=31.24..31.27 rows=2 width=1,697) (actual time=0.038..0.038 rows=0 loops=1)

96. 0.000 0.037 ↓ 0.0 0 1

Unique (cost=31.24..31.25 rows=2 width=1,697) (actual time=0.037..0.037 rows=0 loops=1)

97. 0.008 0.037 ↓ 0.0 0 1

Sort (cost=31.24..31.24 rows=2 width=1,697) (actual time=0.037..0.037 rows=0 loops=1)

  • Sort Key: proj_1_1.id
  • Sort Method: quicksort Memory: 25kB
98. 0.002 0.029 ↓ 0.0 0 1

Nested Loop (cost=9.94..31.23 rows=2 width=1,697) (actual time=0.029..0.029 rows=0 loops=1)

99. 0.009 0.009 ↑ 1.0 1 1

Index Only Scan using contractor_pkey on contractor c_3 (cost=0.29..8.30 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: (id = '072c2e39-cbfe-4084-b93c-dd880b8742cf'::uuid)
  • Heap Fetches: 1
100. 0.010 0.018 ↓ 0.0 0 1

Hash Join (cost=9.66..22.91 rows=2 width=32) (actual time=0.018..0.018 rows=0 loops=1)

  • Hash Cond: (proj_1_1.id = pc_1.proj_id)
101. 0.002 0.002 ↑ 98.0 1 1

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

102. 0.000 0.006 ↓ 0.0 0 1

Hash (cost=9.63..9.63 rows=2 width=32) (actual time=0.006..0.006 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
103. 0.001 0.006 ↓ 0.0 0 1

Bitmap Heap Scan on proj_contractor pc_1 (cost=4.29..9.63 rows=2 width=32) (actual time=0.006..0.006 rows=0 loops=1)

  • Recheck Cond: (contractor_id = '072c2e39-cbfe-4084-b93c-dd880b8742cf'::uuid)
104. 0.005 0.005 ↓ 0.0 0 1

Bitmap Index Scan on proj_contractor_uindex (cost=0.00..4.29 rows=2 width=0) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: (contractor_id = '072c2e39-cbfe-4084-b93c-dd880b8742cf'::uuid)
105. 0.001 0.422 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_2 (cost=268.56..268.71 rows=4 width=784) (actual time=0.422..0.422 rows=0 loops=1)

106. 0.000 0.421 ↓ 0.0 0 1

Unique (cost=268.56..268.67 rows=4 width=1,268) (actual time=0.421..0.421 rows=0 loops=1)

107. 0.014 0.421 ↓ 0.0 0 1

Sort (cost=268.56..268.57 rows=4 width=1,268) (actual time=0.421..0.421 rows=0 loops=1)

  • Sort Key: ((po_4.po_type)::character varying(255)), po_4.proj_id, pi_3.internal_order_id, (CASE WHEN ((po_4.po_type = 'LOW_CONTRACT'::po_type) AND (po_4.contractor_id = io_2.main_contractor_id)) THEN true ELSE false END), po_4.id, (CASE WHEN (po_4.po_type = 'LOW_CONTRACT'::po_type) THEN pi_3.id ELSE NULL::uuid END), (COALESCE(po_4.contract_doc_no, po_4.po_no)), po_4.objective_id, po_4.objective_snap, (COALESCE((pi_3.allow_rebate_retention_bond_start_date - 1), pi_3.retention_end_date))
  • Sort Method: quicksort Memory: 25kB
108. 0.000 0.407 ↓ 0.0 0 1

Nested Loop Left Join (cost=4.60..268.52 rows=4 width=1,268) (actual time=0.407..0.407 rows=0 loops=1)

109. 0.000 0.407 ↓ 0.0 0 1

Nested Loop (cost=4.31..256.90 rows=4 width=228) (actual time=0.407..0.407 rows=0 loops=1)

110. 0.407 0.407 ↓ 0.0 0 1

Seq Scan on po po_4 (cost=0.00..237.74 rows=1 width=188) (actual time=0.406..0.407 rows=0 loops=1)

  • Filter: ((po_type = ANY ('{LOW_CONTRACT,LOW_PO_HOUSE}'::po_type[])) AND (contractor_id = '072c2e39-cbfe-4084-b93c-dd880b8742cf'::uuid) 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: 1095
111. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on po_item pi_3 (cost=4.31..19.11 rows=4 width=56) (never executed)

  • Recheck Cond: (po_id = po_4.id)
112. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on po_item_po_id_ord_key (cost=0.00..4.31 rows=4 width=0) (never executed)

  • Index Cond: (po_id = po_4.id)
113. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = pi_3.internal_order_id)
114. 0.001 0.336 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3_1 (cost=706.95..706.98 rows=1 width=784) (actual time=0.335..0.336 rows=0 loops=1)

115. 0.000 0.335 ↓ 0.0 0 1

Unique (cost=706.95..706.97 rows=1 width=1,268) (actual time=0.335..0.335 rows=0 loops=1)

116. 0.020 0.335 ↓ 0.0 0 1

Sort (cost=706.95..706.95 rows=1 width=1,268) (actual time=0.335..0.335 rows=0 loops=1)

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

Nested Loop Left Join (cost=680.26..706.94 rows=1 width=1,268) (actual time=0.314..0.315 rows=0 loops=1)

  • Join Filter: (po_5.budget_sub_cat_id = bschr_3.id)
118. 0.012 0.314 ↓ 0.0 0 1

Hash Right Join (cost=680.26..705.05 rows=1 width=212) (actual time=0.314..0.314 rows=0 loops=1)

  • Hash Cond: (po_item_1.po_id = po_5.id)
119. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=439.77..450.72 rows=1,095 width=24) (never executed)

  • Group Key: po_item_1.po_id
120. 0.000 0.000 ↓ 0.0 0

Seq Scan on po_item po_item_1 (cost=0.00..409.44 rows=4,044 width=24) (never executed)

121. 0.000 0.302 ↓ 0.0 0 1

Hash (cost=240.48..240.48 rows=1 width=204) (actual time=0.302..0.302 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
122. 0.302 0.302 ↓ 0.0 0 1

Seq Scan on po po_5 (cost=0.00..240.48 rows=1 width=204) (actual time=0.302..0.302 rows=0 loops=1)

  • Filter: ((contractor_id = '072c2e39-cbfe-4084-b93c-dd880b8742cf'::uuid) AND (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: 1095
123. 0.000 0.000 ↓ 0.0 0

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

124. 0.001 0.242 ↓ 0.0 0 1

Subquery Scan on *SELECT* 4_1 (cost=257.63..257.79 rows=4 width=784) (actual time=0.242..0.242 rows=0 loops=1)

125. 0.000 0.241 ↓ 0.0 0 1

Unique (cost=257.63..257.75 rows=4 width=1,268) (actual time=0.241..0.241 rows=0 loops=1)

126. 0.016 0.241 ↓ 0.0 0 1

Sort (cost=257.63..257.64 rows=4 width=1,268) (actual time=0.241..0.241 rows=0 loops=1)

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

Nested Loop (cost=4.45..257.59 rows=4 width=1,268) (actual time=0.225..0.225 rows=0 loops=1)

128. 0.001 0.225 ↓ 0.0 0 1

Nested Loop (cost=4.31..256.90 rows=4 width=228) (actual time=0.225..0.225 rows=0 loops=1)

129. 0.224 0.224 ↓ 0.0 0 1

Seq Scan on po po_6 (cost=0.00..237.74 rows=1 width=172) (actual time=0.224..0.224 rows=0 loops=1)

  • Filter: ((po_type = 'HIGH_CONTRACT'::po_type) AND (contractor_id = '072c2e39-cbfe-4084-b93c-dd880b8742cf'::uuid) 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: 1095
130. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on po_item pi_4 (cost=4.31..19.11 rows=4 width=72) (never executed)

  • Recheck Cond: (po_id = po_6.id)
131. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on po_item_po_id_ord_key (cost=0.00..4.31 rows=4 width=0) (never executed)

  • Index Cond: (po_id = po_6.id)
132. 0.000 0.000 ↓ 0.0 0

Index Scan using budget_sub_category_high_rise_pkey on budget_sub_category_high_rise bschr_4 (cost=0.14..0.16 rows=1 width=1,048) (never executed)

  • Index Cond: (id = pi_4.budget_sub_cat_id)
133. 3.503 582.572 ↓ 9.6 769 1

Unique (cost=1,888.52..1,889.52 rows=80 width=729) (actual time=577.272..582.572 rows=769 loops=1)

134. 26.455 579.069 ↓ 275.7 22,056 1

Sort (cost=1,888.52..1,888.72 rows=80 width=729) (actual time=577.270..579.069 rows=22,056 loops=1)

  • Sort Key: pi_5.proj_id, pi_5.internal_order_id, pi_5.budget_sub_cat_high_rise_id, ((bschr_5.name)::character varying(500)) COLLATE thai
  • Sort Method: quicksort Memory: 4027kB
135. 20.450 552.614 ↓ 275.7 22,056 1

Nested Loop Left Join (cost=9.30..1,885.99 rows=80 width=729) (actual time=0.128..552.614 rows=22,056 loops=1)

136. 19.913 532.164 ↓ 275.7 22,056 1

Nested Loop (cost=9.16..1,873.33 rows=80 width=64) (actual time=0.121..532.164 rows=22,056 loops=1)

137. 16.852 446.083 ↓ 51.5 22,056 1

Nested Loop (cost=8.88..655.86 rows=428 width=36) (actual time=0.107..446.083 rows=22,056 loops=1)

138. 5.056 138.109 ↓ 52.3 22,394 1

Nested Loop (cost=8.60..508.40 rows=428 width=31) (actual time=0.067..138.109 rows=22,394 loops=1)

139. 2.223 3.797 ↓ 214.0 214 1

Hash Join (cost=8.31..419.74 rows=1 width=20) (actual time=0.046..3.797 rows=214 loops=1)

  • Hash Cond: ((po_7.vendor_code)::text = (c_4.code)::text)
140. 1.562 1.562 ↑ 1.0 7,400 1

Seq Scan on sap_po po_7 (cost=0.00..392.00 rows=7,400 width=12) (actual time=0.005..1.562 rows=7,400 loops=1)

141. 0.002 0.012 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
142. 0.010 0.010 ↑ 1.0 1 1

Index Scan using contractor_pkey on contractor c_4 (cost=0.29..8.30 rows=1 width=25) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: (id = '072c2e39-cbfe-4084-b93c-dd880b8742cf'::uuid)
143. 129.256 129.256 ↓ 2.0 105 214

Index Only Scan using sap_pr_po_map_uindex on sap_pr_po_map sppm_1 (cost=0.29..88.14 rows=52 width=19) (actual time=0.079..0.604 rows=105 loops=214)

  • Index Cond: (po_item = po_7.po_item)
  • Heap Fetches: 22394
144. 291.122 291.122 ↑ 1.0 1 22,394

Index Scan using pr_index_pr_code on pr pr_1 (cost=0.28..0.33 rows=1 width=27) (actual time=0.013..0.013 rows=1 loops=22,394)

  • Index Cond: ((pr_no)::text = (sppm_1.pr_doc_no)::text)
145. 66.168 66.168 ↑ 1.0 1 22,056

Index Scan using pr_item_pr_id_ord_key on pr_item pi_5 (cost=0.29..2.84 rows=1 width=68) (actual time=0.003..0.003 rows=1 loops=22,056)

  • Index Cond: ((pr_id = pr_1.id) AND (ord = sppm_1.pr_item))
146. 0.000 0.000 ↓ 0.0 0 22,056

Index Scan using budget_sub_category_high_rise_pkey on budget_sub_category_high_rise bschr_5 (cost=0.14..0.16 rows=1 width=532) (actual time=0.000..0.000 rows=0 loops=22,056)

  • Index Cond: (pi_5.budget_sub_cat_high_rise_id = id)
147. 0.021 0.067 ↑ 1.0 98 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
148. 0.046 0.046 ↑ 1.0 98 1

Seq Scan on proj proj_3 (cost=0.00..12.98 rows=98 width=20) (actual time=0.011..0.046 rows=98 loops=1)

Planning time : 44.714 ms
Execution time : 3,647.321 ms