explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Xyqd : v2

Settings
# exclusive inclusive rows x rows loops node
1. 18.699 1,565.512 ↓ 20.6 10,859 1

Nested Loop Left Join (cost=2,839.88..22,471.65 rows=526 width=2,067) (actual time=308.167..1,565.512 rows=10,859 loops=1)

2.          

CTE codeinfo

3. 0.740 0.740 ↓ 1.1 66 1

Seq Scan on cmm_mst_code_info (cost=0.00..51.33 rows=61 width=31) (actual time=0.108..0.740 rows=66 loops=1)

  • Filter: ((code_id_)::text = ANY ('{C017,C180,C093,C171,C030,C172,C181,C182,C170}'::text[]))
  • Rows Removed by Filter: 1084
4. 9.941 1,427.364 ↓ 20.6 10,859 1

Nested Loop Left Join (cost=2,787.99..20,003.35 rows=526 width=2,092) (actual time=308.121..1,427.364 rows=10,859 loops=1)

5. 9.400 1,232.820 ↓ 20.6 10,859 1

Hash Left Join (cost=2,787.57..17,965.51 rows=526 width=2,127) (actual time=308.052..1,232.820 rows=10,859 loops=1)

  • Hash Cond: ((crd.user_expending_range_)::text = (expending.code_dbid_)::text)
6. 8.579 1,223.388 ↓ 20.6 10,859 1

Hash Left Join (cost=2,785.59..17,956.42 rows=526 width=2,050) (actual time=308.007..1,223.388 rows=10,859 loops=1)

  • Hash Cond: ((crd.user_education_)::text = (education.code_dbid_)::text)
7. 7.006 1,214.784 ↓ 20.6 10,859 1

Hash Left Join (cost=2,783.61..17,947.36 rows=526 width=1,960) (actual time=307.970..1,214.784 rows=10,859 loops=1)

  • Hash Cond: ((crd.tenor_range_)::text = (term.code_dbid_)::text)
8. 7.358 1,207.753 ↓ 20.6 10,859 1

Hash Left Join (cost=2,781.62..17,941.50 rows=526 width=1,868) (actual time=307.937..1,207.753 rows=10,859 loops=1)

  • Hash Cond: ((crd.down_payment_range_)::text = (down.code_dbid_)::text)
9. 7.945 1,200.369 ↓ 20.6 10,859 1

Hash Left Join (cost=2,779.64..17,934.13 rows=526 width=1,776) (actual time=307.899..1,200.369 rows=10,859 loops=1)

  • Hash Cond: ((crd.payment_method_type_)::text = (payment.code_dbid_)::text)
10. 8.358 1,192.397 ↓ 20.6 10,859 1

Hash Left Join (cost=2,777.66..17,924.91 rows=526 width=1,260) (actual time=307.856..1,192.397 rows=10,859 loops=1)

  • Hash Cond: ((crd.user_occupation_)::text = (job.code_dbid_)::text)
11. 7.987 1,184.014 ↓ 20.6 10,859 1

Hash Left Join (cost=2,775.68..17,915.83 rows=526 width=1,175) (actual time=307.820..1,184.014 rows=10,859 loops=1)

  • Hash Cond: ((crd.user_gender_type_id_)::text = (gender.code_dbid_)::text)
12. 7.645 1,176.002 ↓ 20.6 10,859 1

Hash Left Join (cost=2,773.69..17,906.74 rows=526 width=1,086) (actual time=307.783..1,176.002 rows=10,859 loops=1)

  • Hash Cond: ((crd.sales_order_type_id_)::text = (instansi.code_dbid_)::text)
13. 8.224 1,168.331 ↓ 20.6 10,859 1

Hash Left Join (cost=2,771.71..17,897.54 rows=526 width=1,006) (actual time=307.746..1,168.331 rows=10,859 loops=1)

  • Hash Cond: ((crd.credit_agent_)::text = (leasing.code_dbid_)::text)
14. 10.767 1,159.304 ↓ 20.6 10,859 1

Hash Left Join (cost=2,769.73..17,892.18 rows=526 width=498) (actual time=306.930..1,159.304 rows=10,859 loops=1)

  • Hash Cond: ((grcity.from_geography_id_)::text = (gbpro.geography_id_)::text)
15. 12.955 1,145.082 ↓ 20.6 10,859 1

Hash Left Join (cost=2,437.54..17,548.81 rows=526 width=525) (actual time=303.461..1,145.082 rows=10,859 loops=1)

  • Hash Cond: ((gbctiy.geography_id_)::text = (grcity.to_geography_id_)::text)
16. 13.219 1,128.879 ↓ 20.6 10,859 1

Hash Left Join (cost=2,062.75..17,162.86 rows=526 width=525) (actual time=300.193..1,128.879 rows=10,859 loops=1)

  • Hash Cond: ((grdis.from_geography_id_)::text = (gbctiy.geography_id_)::text)
17. 17.809 1,112.130 ↓ 20.6 10,859 1

Hash Left Join (cost=1,730.56..16,819.49 rows=526 width=515) (actual time=296.647..1,112.130 rows=10,859 loops=1)

  • Hash Cond: ((crd.user_district_id_)::text = (grdis.to_geography_id_)::text)
18. 17.971 1,089.746 ↓ 20.6 10,859 1

Hash Left Join (cost=1,355.78..16,433.53 rows=526 width=511) (actual time=292.054..1,089.746 rows=10,859 loops=1)

  • Hash Cond: ((crd.user_district_id_)::text = (gbdis.geography_id_)::text)
19. 9.633 1,067.338 ↓ 20.6 10,859 1

Hash Left Join (cost=1,023.59..16,090.16 rows=526 width=501) (actual time=287.598..1,067.338 rows=10,859 loops=1)

  • Hash Cond: ((crd.color_id_)::text = (color.color_id_)::text)
20. 9.600 1,057.682 ↓ 20.6 10,859 1

Nested Loop Left Join (cost=1,021.58..16,080.92 rows=526 width=528) (actual time=287.556..1,057.682 rows=10,859 loops=1)

21. 10.017 1,004.646 ↓ 20.6 10,859 1

Nested Loop Left Join (cost=1,021.02..14,054.76 rows=526 width=547) (actual time=287.542..1,004.646 rows=10,859 loops=1)

22. 9.661 886.039 ↓ 20.6 10,859 1

Nested Loop (cost=1,020.46..12,028.60 rows=526 width=538) (actual time=287.492..886.039 rows=10,859 loops=1)

23. 13.797 746.070 ↓ 23.4 10,859 1

Nested Loop (cost=1,019.90..9,223.56 rows=464 width=534) (actual time=287.451..746.070 rows=10,859 loops=1)

24. 11.882 677.978 ↓ 23.4 10,859 1

Nested Loop (cost=1,019.48..7,383.92 rows=464 width=498) (actual time=287.419..677.978 rows=10,859 loops=1)

  • Join Filter: ((crd.serialized_product_id_)::text = (manifest.serialized_product_id_)::text)
25. 13.882 524.929 ↓ 23.4 10,859 1

Nested Loop (cost=1,018.92..5,591.95 rows=464 width=535) (actual time=287.357..524.929 rows=10,859 loops=1)

26. 69.187 369.880 ↓ 23.4 10,859 1

Hash Right Join (cost=1,018.35..3,461.03 rows=464 width=478) (actual time=287.288..369.880 rows=10,859 loops=1)

  • Hash Cond: ((monitoring.registration_document_id_)::text = (crd.registration_document_id_)::text)
27. 14.064 14.064 ↑ 1.0 54,479 1

Seq Scan on cmm_registration_document_monitoring monitoring (cost=0.00..2,234.63 rows=55,463 width=42) (actual time=0.022..14.064 rows=54,479 loops=1)

28. 20.822 286.629 ↓ 23.4 10,859 1

Hash (cost=1,012.55..1,012.55 rows=464 width=472) (actual time=286.629..286.629 rows=10,859 loops=1)

  • Buckets: 1024 Batches: 2 (originally 1) Memory Usage: 4097kB
29. 265.807 265.807 ↓ 23.4 10,859 1

Index Scan using cmm_registration_doc_idx02 on cmm_registration_document crd (cost=0.63..1,012.55 rows=464 width=472) (actual time=3.201..265.807 rows=10,859 loops=1)

  • Index Cond: (((site_id_)::text = ANY ('{VA23002,24029,VQ00001,VB23103,V500001,VG00001,VA23004,VA00008,VA23001,VA23007,VA23009,VA23008,VA23006,VB23003,VB23002,VF00001,VC23001,VA23005,9V0001,V100001,V100002,V123002,V123004,VA00001,VA00002,VA00003,VA00004,VA00005,VA00006,VA00007,VA00009,VA00010,VA00011,VA00012,VAVA001,VAVA002,VC23002,VE00001,VF00002,VH23001,VI23001,VI23002,VJ23001,VK23001,VL23001,VM23001,VM23002,VM23003,VM23004,VP23002,VB23005,924114,VB23101,VM00001,VL23002,VD23001,9VM001,VG23001,V123003,VAVA003,VP23001,VP00001,VA23003}'::text[])) AND ((request_date_)::text >= '20190601'::text) AND ((request_date_)::text <= '20190701'::text))
30. 141.167 141.167 ↑ 1.0 1 10,859

Index Scan using serialized_product_identifier_idx01 on cmm_serialized_product_identifier identifier (cost=0.56..4.58 rows=1 width=57) (actual time=0.013..0.013 rows=1 loops=10,859)

  • Index Cond: ((serialized_product_id_)::text = (crd.serialized_product_id_)::text)
31. 141.167 141.167 ↑ 1.0 1 10,859

Index Scan using serialized_product_manifest_info_idx01 on cmm_serialized_product_manifest_info manifest (cost=0.56..3.85 rows=1 width=41) (actual time=0.013..0.013 rows=1 loops=10,859)

  • Index Cond: ((serialized_product_id_)::text = (identifier.serialized_product_id_)::text)
  • Filter: ((manifest_type_)::text = 'C186MANUFACTUREMANIFEST'::text)
  • Rows Removed by Filter: 0
32. 54.295 54.295 ↑ 1.0 1 10,859

Index Scan using pk_product on cmm_product pro (cost=0.42..3.95 rows=1 width=73) (actual time=0.005..0.005 rows=1 loops=10,859)

  • Index Cond: ((product_id_)::text = (crd.product_id_)::text)
33. 130.308 130.308 ↑ 1.0 1 10,859

Index Scan using cmm_registration_document_dtl_idx01 on cmm_registration_document_dtl crdd (cost=0.56..6.04 rows=1 width=60) (actual time=0.011..0.012 rows=1 loops=10,859)

  • Index Cond: ((registration_document_id_)::text = (crd.registration_document_id_)::text)
  • Filter: ((document_type_)::text = 'C174FAKTUR'::text)
  • Rows Removed by Filter: 1
34. 108.590 108.590 ↑ 1.0 1 10,859

Index Only Scan using cmm_registration_doc_role_idx01 on cmm_registration_document_role crdr (cost=0.56..3.84 rows=1 width=42) (actual time=0.010..0.010 rows=1 loops=10,859)

  • Index Cond: ((registration_document_detail_id_ = (crdd.registration_document_detail_id_)::text) AND (registration_document_role_type_ = 'C173APPROVE'::text))
  • Heap Fetches: 7069
35. 43.436 43.436 ↑ 1.0 1 10,859

Index Only Scan using cmm_registration_doc_role_idx01 on cmm_registration_document_role crdra (cost=0.56..3.84 rows=1 width=42) (actual time=0.004..0.004 rows=1 loops=10,859)

  • Index Cond: ((registration_document_detail_id_ = (crdd.registration_document_detail_id_)::text) AND (registration_document_role_type_ = 'C173PRINT'::text))
  • Heap Fetches: 10808
36. 0.013 0.023 ↑ 1.0 45 1

Hash (cost=1.45..1.45 rows=45 width=47) (actual time=0.023..0.023 rows=45 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
37. 0.010 0.010 ↑ 1.0 45 1

Seq Scan on cmm_color_info color (cost=0.00..1.45 rows=45 width=47) (actual time=0.004..0.010 rows=45 loops=1)

38. 2.109 4.437 ↑ 1.0 7,563 1

Hash (cost=237.64..237.64 rows=7,564 width=47) (actual time=4.437..4.437 rows=7,563 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 591kB
39. 2.328 2.328 ↑ 1.0 7,563 1

Seq Scan on cmm_geography_boundary gbdis (cost=0.00..237.64 rows=7,564 width=47) (actual time=0.008..2.328 rows=7,563 loops=1)

40. 2.004 4.575 ↑ 1.0 7,517 1

Hash (cost=280.46..280.46 rows=7,546 width=74) (actual time=4.575..4.575 rows=7,517 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 779kB
41. 2.571 2.571 ↑ 1.0 7,517 1

Seq Scan on cmm_geography_rollup grdis (cost=0.00..280.46 rows=7,546 width=74) (actual time=0.010..2.571 rows=7,517 loops=1)

42. 2.087 3.530 ↑ 1.0 7,563 1

Hash (cost=237.64..237.64 rows=7,564 width=47) (actual time=3.530..3.530 rows=7,563 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 591kB
43. 1.443 1.443 ↑ 1.0 7,563 1

Seq Scan on cmm_geography_boundary gbctiy (cost=0.00..237.64 rows=7,564 width=47) (actual time=0.003..1.443 rows=7,563 loops=1)

44. 1.813 3.248 ↑ 1.0 7,517 1

Hash (cost=280.46..280.46 rows=7,546 width=74) (actual time=3.248..3.248 rows=7,517 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 779kB
45. 1.435 1.435 ↑ 1.0 7,517 1

Seq Scan on cmm_geography_rollup grcity (cost=0.00..280.46 rows=7,546 width=74) (actual time=0.002..1.435 rows=7,517 loops=1)

46. 2.100 3.455 ↑ 1.0 7,563 1

Hash (cost=237.64..237.64 rows=7,564 width=47) (actual time=3.455..3.455 rows=7,563 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 591kB
47. 1.355 1.355 ↑ 1.0 7,563 1

Seq Scan on cmm_geography_boundary gbpro (cost=0.00..237.64 rows=7,564 width=47) (actual time=0.002..1.355 rows=7,563 loops=1)

48. 0.014 0.803 ↓ 1.1 66 1

Hash (cost=1.22..1.22 rows=61 width=614) (actual time=0.803..0.803 rows=66 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
49. 0.789 0.789 ↓ 1.1 66 1

CTE Scan on codeinfo leasing (cost=0.00..1.22 rows=61 width=614) (actual time=0.112..0.789 rows=66 loops=1)

50. 0.014 0.026 ↓ 1.1 66 1

Hash (cost=1.22..1.22 rows=61 width=196) (actual time=0.026..0.026 rows=66 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
51. 0.012 0.012 ↓ 1.1 66 1

CTE Scan on codeinfo instansi (cost=0.00..1.22 rows=61 width=196) (actual time=0.001..0.012 rows=66 loops=1)

52. 0.020 0.025 ↓ 1.1 66 1

Hash (cost=1.22..1.22 rows=61 width=196) (actual time=0.025..0.025 rows=66 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
53. 0.005 0.005 ↓ 1.1 66 1

CTE Scan on codeinfo gender (cost=0.00..1.22 rows=61 width=196) (actual time=0.000..0.005 rows=66 loops=1)

54. 0.014 0.025 ↓ 1.1 66 1

Hash (cost=1.22..1.22 rows=61 width=196) (actual time=0.025..0.025 rows=66 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
55. 0.011 0.011 ↓ 1.1 66 1

CTE Scan on codeinfo job (cost=0.00..1.22 rows=61 width=196) (actual time=0.001..0.011 rows=66 loops=1)

56. 0.014 0.027 ↓ 1.1 66 1

Hash (cost=1.22..1.22 rows=61 width=614) (actual time=0.027..0.027 rows=66 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
57. 0.013 0.013 ↓ 1.1 66 1

CTE Scan on codeinfo payment (cost=0.00..1.22 rows=61 width=614) (actual time=0.001..0.013 rows=66 loops=1)

58. 0.014 0.026 ↓ 1.1 66 1

Hash (cost=1.22..1.22 rows=61 width=196) (actual time=0.026..0.026 rows=66 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
59. 0.012 0.012 ↓ 1.1 66 1

CTE Scan on codeinfo down (cost=0.00..1.22 rows=61 width=196) (actual time=0.001..0.012 rows=66 loops=1)

60. 0.014 0.025 ↓ 1.1 66 1

Hash (cost=1.22..1.22 rows=61 width=196) (actual time=0.025..0.025 rows=66 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
61. 0.011 0.011 ↓ 1.1 66 1

CTE Scan on codeinfo term (cost=0.00..1.22 rows=61 width=196) (actual time=0.001..0.011 rows=66 loops=1)

62. 0.018 0.025 ↓ 1.1 66 1

Hash (cost=1.22..1.22 rows=61 width=196) (actual time=0.025..0.025 rows=66 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
63. 0.007 0.007 ↓ 1.1 66 1

CTE Scan on codeinfo education (cost=0.00..1.22 rows=61 width=196) (actual time=0.000..0.007 rows=66 loops=1)

64. 0.016 0.032 ↓ 1.1 66 1

Hash (cost=1.22..1.22 rows=61 width=196) (actual time=0.032..0.032 rows=66 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
65. 0.016 0.016 ↓ 1.1 66 1

CTE Scan on codeinfo expending (cost=0.00..1.22 rows=61 width=196) (actual time=0.001..0.016 rows=66 loops=1)

66. 184.603 184.603 ↑ 1.0 1 10,859

Index Scan using "index_productId_01" on cmm_product_feature profeature (cost=0.42..3.86 rows=1 width=39) (actual time=0.017..0.017 rows=1 loops=10,859)

  • Index Cond: ((product_id_)::text = (crd.product_id_)::text)
  • Filter: ((product_feature_category_id_)::text = 'GOODSDISPLACEMENT'::text)
  • Rows Removed by Filter: 1
67. 119.449 119.449 ↑ 1.0 1 10,859

Index Scan using pk_consumer_info on cmm_consumer_info consumer (cost=0.56..4.58 rows=1 width=29) (actual time=0.011..0.011 rows=1 loops=10,859)

  • Index Cond: ((consumer_id_)::text = (crd.user_consumer_id_)::text)
Planning time : 16.942 ms
Execution time : 1,567.585 ms