explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TPD1

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 22.112 ↓ 3.0 6 1

WindowAgg (cost=611.97..612.00 rows=2 width=88) (actual time=22.106..22.112 rows=6 loops=1)

2.          

CTE pin_avail

3. 0.011 0.127 ↓ 18.0 18 1

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

4. 0.014 0.098 ↓ 18.0 18 1

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

  • Join Filter: (a.shipperid = c.shipperid)
  • Rows Removed by Join Filter: 171
5. 0.032 0.055 ↓ 1.9 29 1

Bitmap Heap Scan on pincodeavailability a (cost=8.96..64.83 rows=15 width=18) (actual time=0.028..0.055 rows=29 loops=1)

  • Recheck Cond: (pincode = ANY ('{400076,110050}'::text[]))
  • Heap Blocks: exact=27
6. 0.023 0.023 ↓ 1.9 29 1

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

  • Index Cond: (pincode = ANY ('{400076,110050}'::text[]))
7. 0.020 0.029 ↓ 2.3 7 29

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

8. 0.009 0.009 ↓ 3.0 9 1

Seq Scan on shippersdetail c (cost=0.00..16.88 rows=3 width=40) (actual time=0.006..0.009 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.008 0.008 ↑ 1.0 1 1

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

  • Index Cond: (productid = 89,335,158)
12.          

CTE zone

13. 0.022 2.687 ↓ 1.5 3 1

Bitmap Heap Scan on shippingpartner_zonecode (cost=462.94..470.96 rows=2 width=37) (actual time=2.684..2.687 rows=3 loops=1)

  • Recheck Cond: ((drop_pincode = 110,050) AND (pickup_pincode = 400,076))
  • Heap Blocks: exact=3
14. 0.250 2.665 ↓ 0.0 0 1

BitmapAnd (cost=462.94..462.94 rows=2 width=0) (actual time=2.665..2.665 rows=0 loops=1)

15. 0.599 0.599 ↓ 1.0 4,574 1

Bitmap Index Scan on idx_shippingpartner_zonecode_drop_pincode (cost=0.00..97.56 rows=4,399 width=0) (actual time=0.598..0.599 rows=4,574 loops=1)

  • Index Cond: (drop_pincode = 110,050)
16. 1.816 1.816 ↓ 1.1 19,504 1

Bitmap Index Scan on idx_shippingpartner_zonecode_pickup_pincode (cost=0.00..365.13 rows=17,142 width=0) (actual time=1.816..1.816 rows=19,504 loops=1)

  • Index Cond: (pickup_pincode = 400,076)
17.          

CTE cost1

18. 0.088 12.506 ↓ 3.0 3 1

Nested Loop (cost=0.03..22.98 rows=1 width=136) (actual time=4.831..12.506 rows=3 loops=1)

  • Join Filter: ((a_1.zone = c_1.zone) AND (a_1.shipperid = c_1.shipperid) AND (d1.pincode = c_1.pickup_pincode) AND (d2.pincode = c_1.drop_pincode))
  • Rows Removed by Join Filter: 477
19. 1.085 9.698 ↓ 160.0 160 1

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

  • Join Filter: ((a_1.weight)::double precision = b_1.final_weight)
  • Rows Removed by Join Filter: 3,200
20. 2.005 8.613 ↓ 3,360.0 3,360 1

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

  • Join Filter: (a_1.shipperid = d2.shipperid)
  • Rows Removed by Join Filter: 26,880
21. 0.056 0.056 ↓ 18.0 18 1

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

  • Filter: supply_cod
22. 5.361 6.552 ↓ 420.0 1,680 18

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

  • Hash Cond: (a_1.shipperid = d1.shipperid)
23. 1.098 1.098 ↓ 1.1 840 18

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

24. 0.005 0.093 ↓ 17.0 17 1

Hash (cost=0.02..0.02 rows=1 width=44) (actual time=0.093..0.093 rows=17 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
25. 0.088 0.088 ↓ 17.0 17 1

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

  • Filter: supply_pickup
  • Rows Removed by Filter: 1
26. 0.000 0.000 ↑ 1.0 1 3,360

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

27. 2.720 2.720 ↓ 1.5 3 160

CTE Scan on zone c_1 (cost=0.00..0.04 rows=2 width=56) (actual time=0.017..0.017 rows=3 loops=160)

28.          

CTE cost2

29. 0.117 9.550 ↓ 3.0 3 1

Nested Loop (cost=0.03..22.98 rows=1 width=136) (actual time=2.006..9.550 rows=3 loops=1)

  • Join Filter: ((a_2.zone = c_2.zone) AND (a_2.shipperid = c_2.shipperid) AND (d1_1.pincode = c_2.pickup_pincode) AND (d2_1.pincode = c_2.drop_pincode))
  • Rows Removed by Join Filter: 477
30. 1.064 9.433 ↓ 160.0 160 1

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

  • Join Filter: ((a_2.weight)::double precision = b_2.final_weight)
  • Rows Removed by Join Filter: 3,200
31. 1.954 8.369 ↓ 3,360.0 3,360 1

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

  • Join Filter: (a_2.shipperid = d2_1.shipperid)
  • Rows Removed by Join Filter: 26,880
32. 0.007 0.007 ↓ 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.007 rows=18 loops=1)

  • Filter: supply_onlinepayment
33. 5.356 6.408 ↓ 420.0 1,680 18

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

  • Hash Cond: (a_2.shipperid = d1_1.shipperid)
34. 1.044 1.044 ↓ 1.1 840 18

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

35. 0.004 0.008 ↓ 17.0 17 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
36. 0.004 0.004 ↓ 17.0 17 1

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

  • Filter: supply_pickup
  • Rows Removed by Filter: 1
37. 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)

38. 0.000 0.000 ↓ 1.5 3 160

CTE Scan on zone c_2 (cost=0.00..0.04 rows=2 width=56) (actual time=0.000..0.000 rows=3 loops=160)

39.          

CTE cost

40. 0.004 22.087 ↓ 3.0 6 1

Unique (cost=0.08..0.12 rows=2 width=136) (actual time=22.083..22.087 rows=6 loops=1)

41. 0.016 22.083 ↓ 3.0 6 1

Sort (cost=0.08..0.09 rows=2 width=136) (actual time=22.082..22.083 rows=6 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: 25kB
42. 0.003 22.067 ↓ 3.0 6 1

Append (cost=0.00..0.07 rows=2 width=136) (actual time=4.833..22.067 rows=6 loops=1)

43. 12.509 12.509 ↓ 3.0 3 1

CTE Scan on cost1 (cost=0.00..0.02 rows=1 width=136) (actual time=4.832..12.509 rows=3 loops=1)

44. 9.555 9.555 ↓ 3.0 3 1

CTE Scan on cost2 (cost=0.00..0.02 rows=1 width=136) (actual time=2.007..9.555 rows=3 loops=1)

45. 0.005 22.100 ↓ 3.0 6 1

Sort (cost=0.06..0.06 rows=2 width=80) (actual time=22.100..22.100 rows=6 loops=1)

  • Sort Key: cost.cost
  • Sort Method: quicksort Memory: 25kB
46. 22.095 22.095 ↓ 3.0 6 1

CTE Scan on cost (cost=0.00..0.04 rows=2 width=80) (actual time=22.088..22.095 rows=6 loops=1)

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