explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HKbW

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 65.842 ↓ 6.0 12 1

WindowAgg (cost=46,612.46..46,612.49 rows=2 width=88) (actual time=65.833..65.842 rows=12 loops=1)

2.          

CTE pin_avail

3. 0.011 0.108 ↓ 18.0 18 1

Nested Loop (cost=9.24..86.43 rows=1 width=47) (actual time=0.041..0.108 rows=18 loops=1)

4. 0.009 0.079 ↓ 18.0 18 1

Nested Loop (cost=8.96..82.39 rows=1 width=50) (actual time=0.034..0.079 rows=18 loops=1)

  • Join Filter: (a.shipperid = c.shipperid)
  • Rows Removed by Join Filter: 180
5. 0.020 0.040 ↓ 2.0 30 1

Bitmap Heap Scan on pincodeavailability a (cost=8.96..64.83 rows=15 width=18) (actual time=0.024..0.040 rows=30 loops=1)

  • Recheck Cond: (pincode = ANY ('{400076,400072}'::text[]))
  • Heap Blocks: exact=14
6. 0.020 0.020 ↓ 2.0 30 1

Bitmap Index Scan on idx_pincodeavailability_pincode (cost=0.00..8.96 rows=15 width=0) (actual time=0.020..0.020 rows=30 loops=1)

  • Index Cond: (pincode = ANY ('{400076,400072}'::text[]))
7. 0.022 0.030 ↓ 2.3 7 30

Materialize (cost=0.00..16.89 rows=3 width=40) (actual time=0.000..0.001 rows=7 loops=30)

8. 0.008 0.008 ↓ 3.0 9 1

Seq Scan on shippersdetail c (cost=0.00..16.88 rows=3 width=40) (actual time=0.006..0.008 rows=9 loops=1)

  • Filter: (shipperstatus = 'visible'::text)
  • Rows Removed by Filter: 6
9. 0.018 0.018 ↑ 1.0 1 18

Index Scan using pincodeavailabilitycodedetail_pkey on pincodeavailabilitycodedetail b (cost=0.28..4.03 rows=1 width=7) (actual time=0.001..0.001 rows=1 loops=18)

  • Index Cond: (code = a.code)
10.          

CTE prod

11. 0.007 0.007 ↑ 1.0 1 1

Index Scan using productweight_pkey on productweight (cost=0.42..8.44 rows=1 width=16) (actual time=0.006..0.007 rows=1 loops=1)

  • Index Cond: (productid = 89335158)
12.          

CTE pick_pincodecity

13. 0.018 0.025 ↓ 2.0 6 1

Bitmap Heap Scan on citypincodemapping (cost=4.46..27.11 rows=3 width=30) (actual time=0.015..0.025 rows=6 loops=1)

  • Recheck Cond: (pincode = 400072)
  • Filter: (type = 'origin'::text)
  • Rows Removed by Filter: 6
  • Heap Blocks: exact=8
14. 0.007 0.007 ↓ 2.0 12 1

Bitmap Index Scan on idx_citypincodemapping_pincode (cost=0.00..4.46 rows=6 width=0) (actual time=0.007..0.007 rows=12 loops=1)

  • Index Cond: (pincode = 400072)
15.          

CTE drop_pincodecity

16. 0.008 0.012 ↓ 2.0 6 1

Bitmap Heap Scan on citypincodemapping citypincodemapping_1 (cost=4.46..27.11 rows=3 width=30) (actual time=0.005..0.012 rows=6 loops=1)

  • Recheck Cond: (pincode = 400076)
  • Filter: (type = 'dest'::text)
  • Rows Removed by Filter: 6
  • Heap Blocks: exact=8
17. 0.004 0.004 ↓ 2.0 12 1

Bitmap Index Scan on idx_citypincodemapping_pincode (cost=0.00..4.46 rows=6 width=0) (actual time=0.004..0.004 rows=12 loops=1)

  • Index Cond: (pincode = 400076)
18.          

CTE zone

19. 0.546 47.503 ↓ 6.0 6 1

Nested Loop (cost=114.01..46,417.31 rows=1 width=59) (actual time=4.374..47.503 rows=6 loops=1)

  • Join Filter: ((b_1.city = a_1.origin_city) AND (b_1.shipperid = a_1.shipperid))
  • Rows Removed by Join Filter: 8213
20. 0.014 0.067 ↓ 2.0 6 1

Hash Join (cost=0.10..0.20 rows=3 width=96) (actual time=0.035..0.067 rows=6 loops=1)

  • Hash Cond: (b_1.shipperid = c_1.shipperid)
21. 0.036 0.036 ↓ 2.0 6 1

CTE Scan on pick_pincodecity b_1 (cost=0.00..0.06 rows=3 width=48) (actual time=0.016..0.036 rows=6 loops=1)

22. 0.002 0.017 ↓ 2.0 6 1

Hash (cost=0.06..0.06 rows=3 width=48) (actual time=0.016..0.017 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.015 0.015 ↓ 2.0 6 1

CTE Scan on drop_pincodecity c_1 (cost=0.00..0.06 rows=3 width=48) (actual time=0.006..0.015 rows=6 loops=1)

24. 40.056 46.890 ↑ 1.1 1,370 6

Bitmap Heap Scan on allshippingpartnerzone a_1 (cost=113.91..15,450.38 rows=1,466 width=43) (actual time=5.780..7.815 rows=1,370 loops=6)

  • Recheck Cond: (destn_city = c_1.city)
  • Filter: (c_1.shipperid = shipperid)
  • Rows Removed by Filter: 6849
  • Heap Blocks: exact=47970
25. 6.834 6.834 ↓ 1.9 8,219 6

Bitmap Index Scan on idx_allshippingpartnerzone_destn_city (cost=0.00..113.55 rows=4,398 width=0) (actual time=1.139..1.139 rows=8,219 loops=6)

  • Index Cond: (destn_city = c_1.city)
26.          

CTE cost1

27. 0.188 56.728 ↓ 6.0 6 1

Nested Loop (cost=0.03..22.94 rows=1 width=136) (actual time=48.433..56.728 rows=6 loops=1)

  • Join Filter: ((a_2.zone = c_2.zone) AND (a_2.shipperid = c_2.shipperid) AND (d1.pincode = c_2.pickup_pincode) AND (d2.pincode = c_2.drop_pincode))
28. 1.049 9.020 ↓ 160.0 160 1

Nested Loop (cost=0.03..22.90 rows=1 width=120) (actual time=0.324..9.020 rows=160 loops=1)

  • Join Filter: ((a_2.weight)::double precision = b_2.final_weight)
  • Rows Removed by Join Filter: 3200
29. 1.876 7.971 ↓ 3,360.0 3,360 1

Nested Loop (cost=0.03..22.87 rows=1 width=120) (actual time=0.141..7.971 rows=3,360 loops=1)

  • Join Filter: (a_2.shipperid = d2.shipperid)
  • Rows Removed by Join Filter: 26880
30. 0.047 0.047 ↓ 18.0 18 1

CTE Scan on pin_avail d2 (cost=0.00..0.02 rows=1 width=12) (actual time=0.043..0.047 rows=18 loops=1)

  • Filter: supply_cod
31. 4.978 6.048 ↓ 420.0 1,680 18

Hash Join (cost=0.03..22.80 rows=4 width=108) (actual time=0.006..0.336 rows=1,680 loops=18)

  • Hash Cond: (a_2.shipperid = d1.shipperid)
32. 0.990 0.990 ↓ 1.1 840 18

Seq Scan on shippingcostmaster a_2 (cost=0.00..19.80 rows=780 width=64) (actual time=0.001..0.055 rows=840 loops=18)

33. 0.004 0.080 ↓ 18.0 18 1

Hash (cost=0.02..0.02 rows=1 width=44) (actual time=0.080..0.080 rows=18 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
34. 0.076 0.076 ↓ 18.0 18 1

CTE Scan on pin_avail d1 (cost=0.00..0.02 rows=1 width=44) (actual time=0.001..0.076 rows=18 loops=1)

  • Filter: supply_pickup
35. 0.000 0.000 ↑ 1.0 1 3,360

CTE Scan on prod b_2 (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=3,360)

36. 47.520 47.520 ↓ 6.0 6 160

CTE Scan on zone c_2 (cost=0.00..0.02 rows=1 width=56) (actual time=0.027..0.297 rows=6 loops=160)

37.          

CTE cost2

38. 0.028 9.048 ↓ 6.0 6 1

Nested Loop (cost=0.03..22.94 rows=1 width=136) (actual time=0.764..9.048 rows=6 loops=1)

  • Join Filter: ((a_3.zone = c_3.zone) AND (a_3.shipperid = c_3.shipperid) AND (d1_1.pincode = c_3.pickup_pincode) AND (d2_1.pincode = c_3.drop_pincode))
  • Rows Removed by Join Filter: 954
39. 1.029 8.860 ↓ 160.0 160 1

Nested Loop (cost=0.03..22.90 rows=1 width=120) (actual time=0.209..8.860 rows=160 loops=1)

  • Join Filter: ((a_3.weight)::double precision = b_3.final_weight)
  • Rows Removed by Join Filter: 3200
40. 1.888 7.831 ↓ 3,360.0 3,360 1

Nested Loop (cost=0.03..22.87 rows=1 width=120) (actual time=0.030..7.831 rows=3,360 loops=1)

  • Join Filter: (a_3.shipperid = d2_1.shipperid)
  • Rows Removed by Join Filter: 26880
41. 0.003 0.003 ↓ 18.0 18 1

CTE Scan on pin_avail d2_1 (cost=0.00..0.02 rows=1 width=12) (actual time=0.001..0.003 rows=18 loops=1)

  • Filter: supply_onlinepayment
42. 4.942 5.940 ↓ 420.0 1,680 18

Hash Join (cost=0.03..22.80 rows=4 width=108) (actual time=0.002..0.330 rows=1,680 loops=18)

  • Hash Cond: (a_3.shipperid = d1_1.shipperid)
43. 0.990 0.990 ↓ 1.1 840 18

Seq Scan on shippingcostmaster a_3 (cost=0.00..19.80 rows=780 width=64) (actual time=0.001..0.055 rows=840 loops=18)

44. 0.004 0.008 ↓ 18.0 18 1

Hash (cost=0.02..0.02 rows=1 width=44) (actual time=0.008..0.008 rows=18 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
45. 0.004 0.004 ↓ 18.0 18 1

CTE Scan on pin_avail d1_1 (cost=0.00..0.02 rows=1 width=44) (actual time=0.002..0.004 rows=18 loops=1)

  • Filter: supply_pickup
46. 0.000 0.000 ↑ 1.0 1 3,360

CTE Scan on prod b_3 (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=3,360)

47. 0.160 0.160 ↓ 6.0 6 160

CTE Scan on zone c_3 (cost=0.00..0.02 rows=1 width=56) (actual time=0.000..0.001 rows=6 loops=160)

48.          

CTE cost

49. 0.006 65.812 ↓ 6.0 12 1

Unique (cost=0.08..0.12 rows=2 width=136) (actual time=65.806..65.812 rows=12 loops=1)

50. 0.018 65.806 ↓ 6.0 12 1

Sort (cost=0.08..0.09 rows=2 width=136) (actual time=65.806..65.806 rows=12 loops=1)

  • Sort Key: cost1.pay, cost1.shippername, cost1.shipperid, cost1.zone, cost1.weight, cost1.prepaid_cost, cost1.cod_cost, cost1.cost
  • Sort Method: quicksort Memory: 26kB
51. 0.003 65.788 ↓ 6.0 12 1

Append (cost=0.00..0.07 rows=2 width=136) (actual time=48.436..65.788 rows=12 loops=1)

52. 56.733 56.733 ↓ 6.0 6 1

CTE Scan on cost1 (cost=0.00..0.02 rows=1 width=136) (actual time=48.435..56.733 rows=6 loops=1)

53. 9.052 9.052 ↓ 6.0 6 1

CTE Scan on cost2 (cost=0.00..0.02 rows=1 width=136) (actual time=0.766..9.052 rows=6 loops=1)

54. 0.007 65.827 ↓ 6.0 12 1

Sort (cost=0.06..0.06 rows=2 width=80) (actual time=65.826..65.827 rows=12 loops=1)

  • Sort Key: cost.cost
  • Sort Method: quicksort Memory: 25kB
55. 65.820 65.820 ↓ 6.0 12 1

CTE Scan on cost (cost=0.00..0.04 rows=2 width=80) (actual time=65.811..65.820 rows=12 loops=1)

  • Filter: (pay = ANY ('{PREPAID,COD}'::text[]))
Planning time : 1.239 ms
Execution time : 66.002 ms