explain.depesz.com

PostgreSQL's explain analyze made readable

Result: G3tvb

Settings
# exclusive inclusive rows x rows loops node
1. 4.755 1,072.925 ↓ 3,628.0 3,628 1

Nested Loop (cost=10,257.17..10,282.3 rows=1 width=722) (actual time=874.819..1,072.925 rows=3,628 loops=1)

  • Buffers: shared hit=862,818
2. 2.838 1,024.634 ↓ 3,628.0 3,628 1

Nested Loop (cost=10,256.75..10,268.25 rows=1 width=679) (actual time=874.752..1,024.634 rows=3,628 loops=1)

  • Buffers: shared hit=833,759
3. 5.162 1,007.284 ↓ 3,628.0 3,628 1

Nested Loop (cost=10,256.33..10,267.66 rows=1 width=672) (actual time=874.74..1,007.284 rows=3,628 loops=1)

  • Buffers: shared hit=819,232
4. 5.366 991.238 ↓ 3,628.0 3,628 1

Nested Loop (cost=10,256.04..10,267.26 rows=1 width=643) (actual time=874.731..991.238 rows=3,628 loops=1)

  • Buffers: shared hit=808,336
5. 2.110 974.988 ↓ 3,628.0 3,628 1

Nested Loop (cost=10,255.75..10,266.87 rows=1 width=635) (actual time=874.722..974.988 rows=3,628 loops=1)

  • Buffers: shared hit=797,435
6. 4.943 958.366 ↓ 3,628.0 3,628 1

Nested Loop (cost=10,255.45..10,266.45 rows=1 width=623) (actual time=874.711..958.366 rows=3,628 loops=1)

  • Buffers: shared hit=786,528
7. 4.028 953.423 ↓ 3,628.0 3,628 1

Nested Loop (cost=10,255.17..10,266.14 rows=1 width=589) (actual time=874.706..953.423 rows=3,628 loops=1)

  • Buffers: shared hit=786,417
8. 3.967 949.395 ↓ 3,628.0 3,628 1

Nested Loop (cost=10,254.75..10,265.42 rows=1 width=538) (actual time=874.702..949.395 rows=3,628 loops=1)

  • Buffers: shared hit=786,269
9. 1.409 945.428 ↓ 3,628.0 3,628 1

Nested Loop (cost=10,254.46..10,263.81 rows=1 width=499) (actual time=874.698..945.428 rows=3,628 loops=1)

  • Buffers: shared hit=786,158
10. 2.116 936.763 ↓ 3,628.0 3,628 1

Nested Loop (cost=10,254.18..10,263.49 rows=1 width=475) (actual time=874.69..936.763 rows=3,628 loops=1)

  • Buffers: shared hit=778,865
11. 1.216 926.127 ↓ 1,704.0 1,704 1

Nested Loop (cost=10,253.76..10,262.98 rows=1 width=467) (actual time=874.678..926.127 rows=1,704 loops=1)

  • Buffers: shared hit=770,109
12. 1.482 921.503 ↓ 1,704.0 1,704 1

Nested Loop (cost=10,253.63..10,262.82 rows=1 width=435) (actual time=874.671..921.503 rows=1,704 loops=1)

  • Buffers: shared hit=766,701
13. 1.550 913.353 ↓ 833.5 1,667 1

Nested Loop (cost=10,253.34..10,262.04 rows=2 width=414) (actual time=874.66..913.353 rows=1,667 loops=1)

  • Buffers: shared hit=761,622
14. 1.149 906.683 ↓ 1,024.0 1,024 1

Nested Loop (cost=10,253.05..10,261.58 rows=1 width=397) (actual time=874.642..906.683 rows=1,024 loops=1)

  • Buffers: shared hit=757,946
15. 0.880 893.246 ↓ 1,024.0 1,024 1

Nested Loop (cost=10,252.77..10,261.26 rows=1 width=363) (actual time=874.62..893.246 rows=1,024 loops=1)

  • Buffers: shared hit=754,875
16. 1.108 888.270 ↓ 1,024.0 1,024 1

Nested Loop (cost=10,252.35..10,260.54 rows=1 width=280) (actual time=874.609..888.27 rows=1,024 loops=1)

  • Buffers: shared hit=750,774
17. 0.830 887.162 ↓ 1,024.0 1,024 1

Nested Loop (cost=10,252.21..10,260.38 rows=1 width=87) (actual time=874.605..887.162 rows=1,024 loops=1)

  • Buffers: shared hit=750,686
18. 0.858 884.332 ↓ 1,000.0 1,000 1

Nested Loop (cost=10,251.93..10,260.05 rows=1 width=63) (actual time=874.597..884.332 rows=1,000 loops=1)

  • Buffers: shared hit=748,656
19. 1.277 880.474 ↓ 1,000.0 1,000 1

Nested Loop (cost=10,251.64..10,259.68 rows=1 width=59) (actual time=874.589..880.474 rows=1,000 loops=1)

  • Buffers: shared hit=745,652
20. 0.463 876.197 ↓ 1,000.0 1,000 1

Limit (cost=10,251.34..10,251.34 rows=1 width=4) (actual time=874.578..876.197 rows=1,000 loops=1)

  • Buffers: shared hit=742,647
21.          

CTE customercategory

22. 0.013 0.046 ↓ 16.0 16 1

Bitmap Heap Scan on parametervalue parametervalue (cost=9.09..13.11 rows=1 width=4) (actual time=0.036..0.046 rows=16 loops=1)

  • Heap Blocks: exact=6
  • Buffers: shared hit=10
23. 0.002 0.033 ↓ 0.0 0 1

BitmapAnd (cost=9.09..9.09 rows=1 width=0) (actual time=0.033..0.033 rows=0 loops=1)

  • Buffers: shared hit=4
24. 0.018 0.018 ↑ 1.0 18 1

Bitmap Index Scan on parametervalue_parametercategorycode_idx (cost=0..4.41 rows=18 width=0) (actual time=0.018..0.018 rows=18 loops=1)

  • Index Cond: ((parametervalue.parametercategorycode)::text = 'profileCode'::text)
  • Buffers: shared hit=2
25. 0.013 0.013 ↓ 1.0 22 1

Bitmap Index Scan on parametervalue_internalparametervaluecode_idx (cost=0..4.43 rows=21 width=0) (actual time=0.013..0.013 rows=22 loops=1)

  • Index Cond: ((parametervalue.internalparametervaluecode)::text = 'customer'::text)
  • Buffers: shared hit=2
26. 2.046 875.734 ↓ 2.2 7,000 1

Unique (cost=10,213.94..10,238.23 rows=3,158 width=4) (actual time=870.784..875.734 rows=7,000 loops=1)

  • Buffers: shared hit=742,647
27. 1.855 873.688 ↓ 3.6 11,395 1

Merge Join (cost=10,213.94..10,230.34 rows=3,158 width=4) (actual time=870.782..873.688 rows=11,395 loops=1)

  • Buffers: shared hit=742,647
28. 26.480 871.804 ↓ 3.6 11,395 1

Sort (cost=8,013.14..8,021.03 rows=3,158 width=4) (actual time=870.75..871.804 rows=11,395 loops=1)

  • Sort Key: cu_1.customerid
  • Sort Method: quicksort Memory: 6,225kB
  • Buffers: shared hit=742,645
29. 49.100 845.324 ↓ 21.3 67,253 1

Nested Loop (cost=52.72..7,829.58 rows=3,158 width=4) (actual time=0.535..845.324 rows=67,253 loops=1)

  • Buffers: shared hit=742,645
30. 5.256 594.441 ↓ 20.7 67,261 1

Nested Loop (cost=52.43..6,485.08 rows=3,247 width=8) (actual time=0.53..594.441 rows=67,261 loops=1)

  • Buffers: shared hit=540,530
31. 31.507 386.505 ↓ 15.4 67,560 1

Nested Loop (cost=52.14..4,772.43 rows=4,381 width=8) (actual time=0.526..386.505 rows=67,560 loops=1)

  • Buffers: shared hit=335,958
32. 19.024 225.260 ↓ 13.8 43,246 1

Hash Join (cost=51.85..3,330.18 rows=3,129 width=8) (actual time=0.519..225.26 rows=43,246 loops=1)

  • Filter: ((cu_1.customerdatastatusparamid IS NULL) OR ((pacu.internalparametervaluecode)::text <> 'notValidated'::text))
  • Buffers: shared hit=188,853
33. 45.677 205.809 ↓ 16.7 52,318 1

Nested Loop (cost=0.32..3,260.88 rows=3,130 width=12) (actual time=0.077..205.809 rows=52,318 loops=1)

  • Buffers: shared hit=188,822
34. 25.422 35.016 ↓ 17.1 62,558 1

Hash Join (cost=0.03..1,745.39 rows=3,660 width=4) (actual time=0.069..35.016 rows=62,558 loops=1)

  • Buffers: shared hit=813
35. 9.538 9.538 ↑ 1.1 62,668 1

Seq Scan on crmdata cd_1 (cost=0..1,461.73 rows=65,873 width=8) (actual time=0.005..9.538 rows=62,668 loops=1)

  • Buffers: shared hit=803
36. 0.003 0.056 ↓ 16.0 16 1

Hash (cost=0.02..0.02 rows=1 width=4) (actual time=0.056..0.056 rows=16 loops=1)

  • Buffers: shared hit=10
37. 0.053 0.053 ↓ 16.0 16 1

CTE Scan on customercategory cc (cost=0..0.02 rows=1 width=4) (actual time=0.038..0.053 rows=16 loops=1)

  • Buffers: shared hit=10
38. 125.116 125.116 ↑ 1.0 1 62,558

Index Scan using customer_pkey on customer cu_1 (cost=0.29..0.4 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=62,558)

  • Index Cond: (cu_1.customerid = cd_1.customerid)
  • Filter: cu_1.individual
  • Buffers: shared hit=188,009
39. 0.141 0.427 ↓ 1.0 913 1

Hash (cost=40.12..40.12 rows=912 width=14) (actual time=0.427..0.427 rows=913 loops=1)

  • Buffers: shared hit=31
40. 0.286 0.286 ↓ 1.0 913 1

Seq Scan on parametervalue pacu (cost=0..40.12 rows=912 width=14) (actual time=0.005..0.286 rows=913 loops=1)

  • Buffers: shared hit=31
41. 129.738 129.738 ↑ 1.0 2 43,246

Index Scan using idx_contract_customerid_pk on contract co_1 (cost=0.29..0.44 rows=2 width=8) (actual time=0.002..0.003 rows=2 loops=43,246)

  • Index Cond: (co_1.customerid = cd_1.customerid)
  • Filter: (co_1.supplierid = ANY ('{76,77}'::integer[]))
  • Buffers: shared hit=147,105
42. 202.680 202.680 ↑ 1.0 1 67,560

Index Scan using contractdelively_contract_id_idx on contractdelivery cdp_1 (cost=0.29..0.38 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=67,560)

  • Index Cond: (cdp_1.contractid = co_1.contractid)
  • Filter: ((cdp_1.todate IS NULL) OR (cdp_1.fromdate < cdp_1.todate))
  • Buffers: shared hit=204,572
43. 201.783 201.783 ↑ 1.0 1 67,261

Index Scan using deliveryperiod_pkey on deliveryperiod deliveryperiod_1 (cost=0.29..0.4 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=67,261)

  • Index Cond: (deliveryperiod_1.deliveryperiodid = cdp_1.deliveryperiodid)
  • Filter: (deliveryperiod_1.deleted IS FALSE)
  • Buffers: shared hit=202,115
44. 0.013 0.029 ↓ 0.0 0 1

Sort (cost=2,200.8..2,201.08 rows=112 width=4) (actual time=0.029..0.029 rows=0 loops=1)

  • Sort Key: cuf.customerid
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2
45. 0.000 0.016 ↓ 0.0 0 1

Nested Loop (cost=489.94..2,196.99 rows=112 width=4) (actual time=0.016..0.016 rows=0 loops=1)

  • Buffers: shared hit=2
46. 0.016 0.016 ↓ 0.0 0 1

Index Scan using parametervalue_valuecode_idx on parametervalue cufpv (cost=0.28..8.29 rows=1 width=4) (actual time=0.016..0.016 rows=0 loops=1)

  • Index Cond: ((cufpv.valuecode)::text = 'RAPPEL ATTESTATION'::text)
  • Buffers: shared hit=2
47. 0.000 0.000 ↓ 0.0 0 0

Bitmap Heap Scan on crmuserfields cuf (cost=489.67..1,934.02 rows=25,468 width=8) (never executed)

48. 0.000 0.000 ↓ 0.0 0 0

Bitmap Index Scan on idx_crmuserfields_useritemcodeparamid (cost=0..483.3 rows=25,468 width=0) (never executed)

  • Index Cond: (cuf.useritemcodeparamid = cufpv.parametervalueid)
49. 3.000 3.000 ↑ 1.0 1 1,000

Index Scan using customer_pkey on customer cu (cost=0.29..8.31 rows=1 width=55) (actual time=0.003..0.003 rows=1 loops=1,000)

  • Index Cond: (cu.customerid = cu_1.customerid)
  • Buffers: shared hit=3,005
50. 3.000 3.000 ↑ 1.0 1 1,000

Index Scan using idx_crmdata_customerid_pk on crmdata cd (cost=0.29..0.37 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=1,000)

  • Index Cond: (cu.customerid = cd.customerid)
  • Buffers: shared hit=3,004
51. 2.000 2.000 ↓ 0.0 0 1,000

Index Scan using idx_attestation_customerid_pk on attestation at (cost=0.29..0.31 rows=1 width=24) (actual time=0.002..0.002 rows=0 loops=1,000)

  • Index Cond: (at.customerid = cu.customerid)
  • Buffers: shared hit=2,030
52. 0.000 0.000 ↓ 0.0 0 1,024

Index Scan using attestationcategory_pkey on attestationcategory ac (cost=0.14..0.15 rows=1 width=197) (actual time=0..0 rows=0 loops=1,024)

  • Index Cond: (ac.attestationcategoryid = at.attestationcategoryid)
  • Buffers: shared hit=88
53. 4.096 4.096 ↑ 1.0 1 1,024

Index Scan using freeaddresses_pkey on freeaddresses fa (cost=0.42..0.71 rows=1 width=87) (actual time=0.003..0.004 rows=1 loops=1,024)

  • Index Cond: (fa.addressid = cu.postaddrid)
  • Buffers: shared hit=4,101
54. 12.288 12.288 ↑ 1.0 1 1,024

Index Scan using town_pkey on town town (cost=0.28..0.3 rows=1 width=34) (actual time=0.011..0.012 rows=1 loops=1,024)

  • Index Cond: (((town.country)::text = (fa.towncountrycode)::text) AND ((town.postalcode)::text = (fa.townpostalcode)::text) AND (town.towncode = fa.towntowncode))
  • Buffers: shared hit=3,071
55. 5.120 5.120 ↑ 1.0 2 1,024

Index Scan using idx_contract_customerid_pk on contract co (cost=0.29..0.44 rows=2 width=25) (actual time=0.003..0.005 rows=2 loops=1,024)

  • Index Cond: (co.customerid = cu.customerid)
  • Filter: (co.supplierid = ANY ('{76,77}'::integer[]))
  • Buffers: shared hit=3,676
56. 6.668 6.668 ↑ 1.0 1 1,667

Index Scan using contractdelively_contract_id_idx on contractdelivery cdp (cost=0.29..0.38 rows=1 width=21) (actual time=0.003..0.004 rows=1 loops=1,667)

  • Index Cond: (cdp.contractid = co.contractid)
  • Filter: ((cdp.todate IS NULL) OR (cdp.fromdate < cdp.todate))
  • Buffers: shared hit=5,079
57. 3.408 3.408 ↑ 1.0 1 1,704

Index Scan using supplier_pkey on supplier su (cost=0.13..0.15 rows=1 width=36) (actual time=0.001..0.002 rows=1 loops=1,704)

  • Index Cond: (su.supplierid = co.supplierid)
  • Buffers: shared hit=3,408
58. 8.520 8.520 ↓ 2.0 2 1,704

Index Scan using idx_contracthistory_contractid_pk on contracthistory ch (cost=0.42..0.51 rows=1 width=12) (actual time=0.003..0.005 rows=2 loops=1,704)

  • Index Cond: (ch.contractid = co.contractid)
  • Buffers: shared hit=8,756
59. 7.256 7.256 ↓ 0.0 0 3,628

Index Scan using idx_thirdpartyrole_contractid_pk on thirdpartyrole tpr (cost=0.28..0.3 rows=1 width=24) (actual time=0.002..0.002 rows=0 loops=3,628)

  • Index Cond: (co.contractid = tpr.contractid)
  • Buffers: shared hit=7,293
60. 0.000 0.000 ↓ 0.0 0 3,628

Index Scan using customer_pkey on customer tpr_cu (cost=0.29..1.6 rows=1 width=39) (actual time=0..0 rows=0 loops=3,628)

  • Index Cond: (tpr_cu.customerid = tpr.customerid)
  • Buffers: shared hit=111
61. 0.000 0.000 ↓ 0.0 0 3,628

Index Scan using freeaddresses_pkey on freeaddresses cu_fa (cost=0.42..0.71 rows=1 width=55) (actual time=0..0 rows=0 loops=3,628)

  • Index Cond: (tpr_cu.postaddrid = cu_fa.addressid)
  • Buffers: shared hit=148
62. 0.000 0.000 ↓ 0.0 0 3,628

Index Scan using town_pkey on town town_1 (cost=0.28..0.3 rows=1 width=34) (actual time=0..0 rows=0 loops=3,628)

  • Index Cond: (((town_1.country)::text = (cu_fa.towncountrycode)::text) AND ((town_1.postalcode)::text = (cu_fa.townpostalcode)::text) AND (town_1.towncode = cu_fa.towntowncode))
  • Buffers: shared hit=111
63. 14.512 14.512 ↑ 1.0 1 3,628

Index Scan using deliveryperiod_pkey on deliveryperiod deliveryperiod (cost=0.29..0.4 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=3,628)

  • Index Cond: (deliveryperiod.deliveryperiodid = cdp.deliveryperiodid)
  • Filter: (deliveryperiod.deleted IS FALSE)
  • Buffers: shared hit=10,907
64. 10.884 10.884 ↑ 1.0 1 3,628

Index Scan using deliverypointcomponent_pk on deliverypointcomponent deliverypointcomponent (cost=0.29..0.38 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=3,628)

  • Index Cond: (deliverypointcomponent.deliverypointcomponentid = deliveryperiod.deliverypointcomponentid)
  • Filter: (deliverypointcomponent.deleted IS FALSE)
  • Buffers: shared hit=10,901
65. 10.884 10.884 ↑ 1.0 1 3,628

Index Scan using deliverypoint_pkey on deliverypoint deliverypoint (cost=0.29..0.4 rows=1 width=29) (actual time=0.003..0.003 rows=1 loops=3,628)

  • Index Cond: (deliverypoint.deliverypointid = deliverypointcomponent.deliverypointid)
  • Filter: (deliverypoint.deleted IS FALSE)
  • Buffers: shared hit=10,896
66. 14.512 14.512 ↑ 1.0 1 3,628

Index Scan using freeaddresses_pkey on freeaddresses freeaddresses (cost=0.42..0.58 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=3,628)

  • Index Cond: (freeaddresses.addressid = deliverypoint.addressid)
  • Buffers: shared hit=14,527
67. 18.140 43.536 ↑ 1.0 1 3,628

Index Scan using freeadddetail_addressid_idx on freeaddressesdetail freeaddressesdetail (cost=0.42..14.04 rows=1 width=47) (actual time=0.012..0.012 rows=1 loops=3,628)

  • Index Cond: (freeaddressesdetail.addressid = freeaddresses.addressid)
  • Filter: ((((freeaddressesdetail.addressid)::text || ';'::text) || (freeaddressesdetail.addressdetailnbr)::text) = (SubPlan 2))
  • Buffers: shared hit=29,059
68.          

SubPlan (for Index Scan)

69. 3.604 25.396 ↑ 1.0 1 3,628

Limit (cost=13.5..13.5 rows=1 width=9) (actual time=0.007..0.007 rows=1 loops=3,628)

  • Buffers: shared hit=14,531
70.          

Initplan (for Limit)

71. 0.024 0.024 ↑ 1.0 1 1

Seq Scan on property property (cost=0..5.03 rows=1 width=7) (actual time=0.01..0.024 rows=1 loops=1)

  • Filter: ((property.propertycode)::text = 'defaultCustomerLanguageCode'::text)
  • Buffers: shared hit=3
72. 7.256 21.768 ↑ 1.0 1 3,628

Sort (cost=8.47..8.48 rows=1 width=9) (actual time=0.006..0.006 rows=1 loops=3,628)

  • Sort Key: (CASE WHEN ((freeaddressesdetail_1.language)::text = ($0)::text) THEN 1 WHEN ((freeaddressesdetail_1.language)::text = 'NL'::text) THEN 2 WHEN ((freeaddressesdetail_1.language)::text = 'FR'::text) THEN 3 ELSE NULL::integer END)
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=14,531
73. 14.512 14.512 ↑ 1.0 1 3,628

Index Scan using freeadddetail_addressid_idx on freeaddressesdetail freeaddressesdetail_1 (cost=0.42..8.46 rows=1 width=9) (actual time=0.003..0.004 rows=1 loops=3,628)

  • Index Cond: (freeaddressesdetail_1.addressid = freeaddressesdetail.addressid)
  • Buffers: shared hit=14,531
Planning time : 13.106 ms
Execution time : 1,074.104 ms