explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cK3U

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 13,740.453 ↓ 1.0 23,936 1

Gather Merge (cost=532,226.70..534,921.66 rows=23,098 width=449) (actual time=13,652.894..13,740.453 rows=23,936 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 28.965 40,933.890 ↑ 1.4 7,979 3

Sort (cost=531,226.68..531,255.55 rows=11,549 width=449) (actual time=13,643.743..13,644.630 rows=7,979 loops=3)

  • Sort Key: ((subordertable.time_stamp + '05:30:00'::interval)) DESC
  • Sort Method: quicksort Memory: 4221kB
  • Worker 0: Sort Method: quicksort Memory: 4333kB
  • Worker 1: Sort Method: quicksort Memory: 4276kB
3. 43.425 40,904.925 ↑ 1.4 7,979 3

Parallel Hash Left Join (cost=337,567.52..530,447.38 rows=11,549 width=449) (actual time=11,402.203..13,634.975 rows=7,979 loops=3)

  • Hash Cond: (pd.invoiceid = sellerpaymentrecordtable.invoiceid)
4. 15.879 39,190.650 ↑ 1.4 7,979 3

Parallel Hash Left Join (cost=307,463.94..500,024.15 rows=11,549 width=285) (actual time=10,841.927..13,063.550 rows=7,979 loops=3)

  • Hash Cond: ((subordertable.parentstoreid = pd.storeid) AND (subordertable.suborderid = pd.suborderid))
5. 17.298 33,864.672 ↑ 1.4 7,979 3

Parallel Hash Left Join (cost=218,552.35..411,051.93 rows=11,549 width=260) (actual time=9,071.752..11,288.224 rows=7,979 loops=3)

  • Hash Cond: ((subordertable.parentstoreid = ssg.storeid) AND (subordertable.suborderid = ssg.suborderid))
6. 14.226 14,358.210 ↑ 1.4 7,979 3

Nested Loop Left Join (cost=160,428.86..352,841.29 rows=11,549 width=260) (actual time=2,575.221..4,786.070 rows=7,979 loops=3)

7. 25.655 9,030.192 ↑ 1.4 7,979 3

Nested Loop Left Join (cost=160,428.43..258,233.64 rows=11,549 width=264) (actual time=2,574.441..3,010.064 rows=7,979 loops=3)

8. 592.875 8,669.433 ↑ 1.4 7,979 3

Parallel Hash Join (cost=160,420.25..240,569.93 rows=11,549 width=232) (actual time=2,574.372..2,889.811 rows=7,979 loops=3)

  • Hash Cond: (subordertablewithouttax.suborderid = subordertable.suborderid)
9. 568.785 568.785 ↑ 1.2 1,493,249 3

Parallel Seq Scan on subordertablewithouttax (cost=0.00..73,102.68 rows=1,866,368 width=26) (actual time=0.011..189.595 rows=1,493,249 loops=3)

10. 14.697 7,507.773 ↑ 1.5 7,979 3

Parallel Hash (cost=160,275.38..160,275.38 rows=11,590 width=222) (actual time=2,502.591..2,502.591 rows=7,979 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 6304kB
11. 9.768 7,493.076 ↑ 1.5 7,979 3

Merge Join (cost=160,092.42..160,275.38 rows=11,590 width=222) (actual time=2,493.329..2,497.692 rows=7,979 loops=3)

  • Merge Cond: (subordertable.suborderstatusvalue = convertortable.suborderstatusvalue)
12. 18.192 7,481.973 ↓ 4.4 7,979 3

Sort (cost=160,004.25..160,008.81 rows=1,825 width=198) (actual time=2,493.266..2,493.991 rows=7,979 loops=3)

  • Sort Key: subordertable.suborderstatusvalue
  • Sort Method: quicksort Memory: 2695kB
  • Worker 0: Sort Method: quicksort Memory: 2423kB
  • Worker 1: Sort Method: quicksort Memory: 2388kB
13. 21.430 7,463.781 ↓ 4.4 7,979 3

Nested Loop Left Join (cost=20,426.67..159,905.39 rows=1,825 width=198) (actual time=127.296..2,487.927 rows=7,979 loops=3)

14. 22.192 7,418.415 ↓ 4.4 7,979 3

Nested Loop (cost=20,426.24..145,004.44 rows=1,825 width=198) (actual time=127.111..2,472.805 rows=7,979 loops=3)

15. 12.221 5,361.663 ↓ 4.4 7,979 3

Nested Loop Left Join (cost=20,425.81..130,098.31 rows=1,825 width=184) (actual time=126.180..1,787.221 rows=7,979 loops=3)

16. 11.500 4,727.106 ↓ 4.4 7,979 3

Nested Loop Left Join (cost=20,425.39..115,200.26 rows=1,825 width=158) (actual time=125.735..1,575.702 rows=7,979 loops=3)

17. 0.000 4,260.822 ↓ 4.4 7,979 3

Nested Loop (cost=20,424.96..100,305.59 rows=1,825 width=152) (actual time=125.139..1,420.274 rows=7,979 loops=3)

18. 13.931 3,113.391 ↓ 4.4 7,979 3

Nested Loop (cost=20,424.39..84,686.46 rows=1,825 width=123) (actual time=124.859..1,037.797 rows=7,979 loops=3)

19. 1,853.498 1,974.468 ↓ 2.2 7,979 3

Parallel Bitmap Heap Scan on subordertable (cost=20,423.96..55,124.73 rows=3,612 width=89) (actual time=124.332..658.156 rows=7,979 loops=3)

  • Recheck Cond: ((parentstoreid = 2167498) AND (date((time_stamp + '05:30:00'::interval)) >= '2019-07-05'::date) AND (date((time_stamp + '05:30:00'::interval)) <= '2019-09-05'::date))
  • Filter: (suborderstatusvalue = ANY ('{3,4,5,6,7,8,9}'::bigint[]))
  • Rows Removed by Filter: 511
  • Heap Blocks: exact=5602
20. 2.939 120.970 ↓ 0.0 0 1

BitmapAnd (cost=20,423.96..20,423.96 rows=10,247 width=0) (actual time=120.970..120.970 rows=0 loops=1)

21. 10.009 10.009 ↓ 1.0 50,484 1

Bitmap Index Scan on idx_parentstoreid_subordertable (cost=0.00..915.28 rows=49,447 width=0) (actual time=10.009..10.009 rows=50,484 loops=1)

  • Index Cond: (parentstoreid = 2167498)
22. 108.022 108.022 ↑ 1.0 923,720 1

Bitmap Index Scan on idx_date_subordertable (cost=0.00..19,504.09 rows=931,566 width=0) (actual time=108.022..108.022 rows=923,720 loops=1)

  • Index Cond: ((date((time_stamp + '05:30:00'::interval)) >= '2019-07-05'::date) AND (date((time_stamp + '05:30:00'::interval)) <= '2019-09-05'::date))
23. 1,124.992 1,124.992 ↑ 1.0 1 23,936

Index Scan using idx_suborderid_subordertableforresellers on subordertableforresellers (cost=0.43..8.17 rows=1 width=34) (actual time=0.047..0.047 rows=1 loops=23,936)

  • Index Cond: (suborderid = subordertable.suborderid)
24. 1,148.928 1,148.928 ↑ 1.0 1 23,936

Index Scan using producttable_pkey on producttable (cost=0.57..8.56 rows=1 width=37) (actual time=0.048..0.048 rows=1 loops=23,936)

  • Index Cond: (productid = subordertable.productid)
25. 454.784 454.784 ↓ 0.0 0 23,936

Index Scan using productskudetails_productvariantid on productskudetails (cost=0.42..8.16 rows=1 width=22) (actual time=0.019..0.019 rows=0 loops=23,936)

  • Index Cond: (subordertable.productvariantid = productvariantid)
26. 622.336 622.336 ↓ 0.0 0 23,936

Index Scan using subordermetadetails_pkey on subordermetadetails (cost=0.42..8.16 rows=1 width=34) (actual time=0.026..0.026 rows=0 loops=23,936)

  • Index Cond: (subordertable.suborderid = suborderid)
27. 2,034.560 2,034.560 ↑ 1.0 1 23,936

Index Scan using storetable_pkey on storetable (cost=0.43..8.17 rows=1 width=22) (actual time=0.085..0.085 rows=1 loops=23,936)

  • Index Cond: (storeid = subordertableforresellers.resellerid)
28. 23.936 23.936 ↑ 1.0 1 23,936

Index Scan using storesellingstates_pkey on storesellingstates ws (cost=0.43..8.16 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=23,936)

  • Index Cond: (storeid = subordertableforresellers.wholesellerid)
29. 1.272 1.335 ↓ 6.2 7,866 3

Sort (cost=88.17..91.35 rows=1,270 width=36) (actual time=0.056..0.445 rows=7,866 loops=3)

  • Sort Key: convertortable.suborderstatusvalue
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
30. 0.063 0.063 ↑ 141.1 9 3

Seq Scan on convertortable (cost=0.00..22.70 rows=1,270 width=36) (actual time=0.020..0.021 rows=9 loops=3)

31. 143.616 335.104 ↑ 1.0 1 23,936

Hash Right Join (cost=8.18..9.70 rows=1 width=48) (actual time=0.011..0.014 rows=1 loops=23,936)

  • Hash Cond: (statecodemappingtable.id = rs.sellingstateid)
32. 71.808 71.808 ↑ 1.0 37 23,936

Seq Scan on statecodemappingtable (cost=0.00..1.37 rows=37 width=40) (actual time=0.001..0.003 rows=37 loops=23,936)

33. 0.000 119.680 ↑ 1.0 1 23,936

Hash (cost=8.16..8.16 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=23,936)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
34. 119.680 119.680 ↑ 1.0 1 23,936

Index Scan using storesellingstates_pkey on storesellingstates rs (cost=0.43..8.16 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=23,936)

  • Index Cond: (storeid = subordertableforresellers.resellerid)
35. 5,313.792 5,313.792 ↑ 1.0 1 23,936

Index Scan using shipmenttable_pkey on shipmenttable (cost=0.43..8.19 rows=1 width=12) (actual time=0.222..0.222 rows=1 loops=23,936)

  • Index Cond: (subordertable.subordershipmentid = shipmentid)
36. 22.020 19,489.164 ↑ 1.2 16,828 3

Parallel Hash (cost=57,811.00..57,811.00 rows=20,832 width=16) (actual time=6,496.388..6,496.388 rows=16,828 loops=3)

  • Buckets: 65536 Batches: 1 Memory Usage: 2912kB
37. 19,457.368 19,467.144 ↑ 1.2 16,828 3

Parallel Bitmap Heap Scan on subordersellergstdetailstable ssg (cost=939.90..57,811.00 rows=20,832 width=16) (actual time=13.971..6,489.048 rows=16,828 loops=3)

  • Recheck Cond: (storeid = 2167498)
  • Heap Blocks: exact=7610
38. 9.776 9.776 ↓ 1.0 50,484 1

Bitmap Index Scan on idx_storeid_subordersellergstdetailstable (cost=0.00..927.40 rows=49,996 width=0) (actual time=9.776..9.776 rows=50,484 loops=1)

  • Index Cond: (storeid = 2167498)
39. 17.637 5,310.099 ↑ 1.3 12,884 3

Parallel Hash (cost=88,664.24..88,664.24 rows=16,490 width=49) (actual time=1,770.033..1,770.033 rows=12,884 loops=3)

  • Buckets: 65536 Batches: 1 Memory Usage: 4032kB
40. 5,292.462 5,292.462 ↑ 1.3 12,884 3

Parallel Seq Scan on payoutdetailstable pd (cost=0.00..88,664.24 rows=16,490 width=49) (actual time=308.510..1,764.154 rows=12,884 loops=3)

  • Filter: (storeid = 2167498)
  • Rows Removed by Filter: 1480726
41. 199.941 1,670.850 ↑ 1.3 213,325 3

Parallel Hash (cost=26,749.38..26,749.38 rows=268,336 width=15) (actual time=556.950..556.950 rows=213,325 loops=3)

  • Buckets: 1048576 Batches: 1 Memory Usage: 45024kB
42. 1,470.909 1,470.909 ↑ 1.3 213,325 3

Parallel Seq Scan on sellerpaymentrecordtable (cost=0.00..26,749.38 rows=268,336 width=15) (actual time=0.014..490.303 rows=213,325 loops=3)

  • Filter: success
  • Rows Removed by Filter: 403186
Planning time : 11.420 ms
Execution time : 13,742.011 ms