explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nbe4

Settings
# exclusive inclusive rows x rows loops node
1. 202.089 1,983.503 ↑ 1.1 22,648 1

Gather Merge (cost=552,905.50..555,818.64 rows=24,968 width=464) (actual time=1,897.674..1,983.503 rows=22,648 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 7.164 1,781.414 ↑ 1.7 7,549 3 / 3

Sort (cost=551,905.48..551,936.69 rows=12,484 width=464) (actual time=1,780.708..1,781.414 rows=7,549 loops=3)

  • Sort Key: ((subordertable.time_stamp + '05:30:00'::interval)) DESC
  • Sort Method: quicksort Memory: 4730kB
  • Worker 0: Sort Method: quicksort Memory: 3904kB
  • Worker 1: Sort Method: quicksort Memory: 3804kB
3. 196.867 1,774.250 ↑ 1.7 7,549 3 / 3

Parallel Hash Join (cost=470,320.69..551,056.08 rows=12,484 width=464) (actual time=1,487.012..1,774.250 rows=7,549 loops=3)

  • Hash Cond: (subordertablewithouttax.suborderid = subordertable.suborderid)
4. 174.260 174.260 ↑ 1.3 1,498,692 3 / 3

Parallel Seq Scan on subordertablewithouttax (cost=0.00..73,376.68 rows=1,873,568 width=26) (actual time=0.012..174.260 rows=1,498,692 loops=3)

5. 19.678 1,403.123 ↑ 1.7 7,549 3 / 3

Parallel Hash (cost=470,164.88..470,164.88 rows=12,465 width=289) (actual time=1,403.123..1,403.123 rows=7,549 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 7008kB
6. 5.538 1,383.445 ↑ 1.7 7,549 3 / 3

Parallel Hash Left Join (cost=348,584.17..470,164.88 rows=12,465 width=289) (actual time=1,231.850..1,383.445 rows=7,549 loops=3)

  • Hash Cond: ((subordertable.parentstoreid = ssg.storeid) AND (subordertable.suborderid = ssg.suborderid))
7. 4.532 1,315.564 ↑ 1.7 7,549 3 / 3

Parallel Hash Left Join (cost=288,370.18..409,856.72 rows=12,465 width=282) (actual time=1,169.397..1,315.564 rows=7,549 loops=3)

  • Hash Cond: (pd.invoiceid = sellerpaymentrecordtable.invoiceid)
8. 4.731 1,114.038 ↑ 1.7 7,549 3 / 3

Parallel Hash Left Join (cost=258,282.33..379,704.42 rows=12,465 width=283) (actual time=970.602..1,114.038 rows=7,549 loops=3)

  • Hash Cond: ((subordertable.parentstoreid = pd.storeid) AND (subordertable.suborderid = pd.suborderid))
9. 5.469 645.995 ↑ 1.7 7,549 3 / 3

Nested Loop Left Join (cost=169,410.82..290,767.47 rows=12,465 width=250) (actual time=507.148..645.995 rows=7,549 loops=3)

10. 6.518 610.329 ↑ 1.7 7,549 3 / 3

Nested Loop Left Join (cost=169,410.39..188,663.05 rows=12,465 width=254) (actual time=507.121..610.329 rows=7,549 loops=3)

11. 4.295 513.219 ↑ 1.7 7,549 3 / 3

Merge Join (cost=169,402.21..169,599.00 rows=12,465 width=222) (actual time=507.074..513.219 rows=7,549 loops=3)

  • Merge Cond: (subordertable.suborderstatusvalue = convertortable.suborderstatusvalue)
12. 5.582 508.161 ↓ 3.8 7,549 3 / 3

Sort (cost=169,314.04..169,318.95 rows=1,963 width=198) (actual time=507.016..508.161 rows=7,549 loops=3)

  • Sort Key: subordertable.suborderstatusvalue
  • Sort Method: quicksort Memory: 2978kB
  • Worker 0: Sort Method: quicksort Memory: 1933kB
  • Worker 1: Sort Method: quicksort Memory: 2244kB
13. 2.095 502.579 ↓ 3.8 7,549 3 / 3

Nested Loop Left Join (cost=35,772.83..169,206.68 rows=1,963 width=198) (actual time=239.847..502.579 rows=7,549 loops=3)

14. 4.819 485.385 ↓ 3.8 7,549 3 / 3

Nested Loop (cost=35,772.40..153,177.37 rows=1,963 width=198) (actual time=239.811..485.385 rows=7,549 loops=3)

15. 9.676 450.369 ↓ 3.8 7,549 3 / 3

Parallel Hash Left Join (cost=35,771.97..137,143.28 rows=1,963 width=184) (actual time=239.779..450.369 rows=7,549 loops=3)

  • Hash Cond: (subordertable.suborderid = subordermetadetails.suborderid)
16. 0.837 291.498 ↓ 3.8 7,549 3 / 3

Nested Loop Left Join (cost=20,277.64..121,643.80 rows=1,963 width=158) (actual time=88.750..291.498 rows=7,549 loops=3)

17. 2.650 275.562 ↓ 3.8 7,549 3 / 3

Nested Loop (cost=20,277.22..105,619.91 rows=1,963 width=152) (actual time=88.727..275.562 rows=7,549 loops=3)

18. 1.688 212.517 ↓ 3.8 7,549 3 / 3

Nested Loop (cost=20,276.65..88,823.72 rows=1,963 width=123) (actual time=88.689..212.517 rows=7,549 loops=3)

19. 84.156 112.688 ↓ 2.0 7,549 3 / 3

Parallel Bitmap Heap Scan on subordertable (cost=20,276.22..57,248.15 rows=3,858 width=89) (actual time=88.643..112.688 rows=7,549 loops=3)

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

BitmapAnd (cost=20,276.22..20,276.22 rows=10,990 width=0) (actual time=85.595..85.595 rows=0 loops=1)

21. 2.803 2.803 ↑ 1.1 50,845 1 / 3

Bitmap Index Scan on idx_parentstoreid_subordertable (cost=0.00..995.38 rows=53,727 width=0) (actual time=8.409..8.409 rows=50,845 loops=1)

  • Index Cond: (parentstoreid = 2167498)
22. 24.997 24.997 ↑ 1.0 884,958 1 / 3

Bitmap Index Scan on idx_date_subordertable (cost=0.00..19,275.95 rows=918,352 width=0) (actual time=74.991..74.991 rows=884,958 loops=1)

  • Index Cond: ((date((time_stamp + '05:30:00'::interval)) >= '2019-07-10'::date) AND (date((time_stamp + '05:30:00'::interval)) <= '2019-09-10'::date))
23. 98.141 98.141 ↑ 1.0 1 22,648 / 3

Index Scan using idx_suborderid_subordertableforresellers on subordertableforresellers (cost=0.43..8.17 rows=1 width=34) (actual time=0.012..0.013 rows=1 loops=22,648)

  • Index Cond: (suborderid = subordertable.suborderid)
24. 60.395 60.395 ↑ 1.0 1 22,648 / 3

Index Scan using producttable_pkey on producttable (cost=0.57..8.56 rows=1 width=37) (actual time=0.008..0.008 rows=1 loops=22,648)

  • Index Cond: (productid = subordertable.productid)
25. 15.099 15.099 ↓ 0.0 0 22,648 / 3

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

  • Index Cond: (subordertable.productvariantid = productvariantid)
26. 87.759 149.195 ↑ 1.3 275,709 3 / 3

Parallel Hash (cost=11,186.37..11,186.37 rows=344,637 width=34) (actual time=149.194..149.195 rows=275,709 loops=3)

  • Buckets: 1048576 Batches: 1 Memory Usage: 64928kB
27. 61.436 61.436 ↑ 1.3 275,709 3 / 3

Parallel Seq Scan on subordermetadetails (cost=0.00..11,186.37 rows=344,637 width=34) (actual time=0.027..61.436 rows=275,709 loops=3)

28. 30.197 30.197 ↑ 1.0 1 22,648 / 3

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

  • Index Cond: (storeid = subordertableforresellers.resellerid)
29. 15.099 15.099 ↑ 1.0 1 22,648 / 3

Index Scan using storesellingstates_pkey on storesellingstates ws (cost=0.43..8.17 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=22,648)

  • Index Cond: (storeid = subordertableforresellers.wholesellerid)
30. 0.742 0.763 ↓ 5.9 7,441 3 / 3

Sort (cost=88.17..91.35 rows=1,270 width=36) (actual time=0.053..0.763 rows=7,441 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
31. 0.021 0.021 ↑ 141.1 9 3 / 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)

32. 45.296 90.592 ↑ 1.0 1 22,648 / 3

Hash Right Join (cost=8.18..9.70 rows=1 width=48) (actual time=0.009..0.012 rows=1 loops=22,648)

  • Hash Cond: (statecodemappingtable.id = rs.sellingstateid)
33. 22.648 22.648 ↑ 1.0 37 22,648 / 3

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

34. 7.549 22.648 ↑ 1.0 1 22,648 / 3

Hash (cost=8.17..8.17 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=22,648)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
35. 15.099 15.099 ↑ 1.0 1 22,648 / 3

Index Scan using storesellingstates_pkey on storesellingstates rs (cost=0.43..8.17 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=22,648)

  • Index Cond: (storeid = subordertableforresellers.resellerid)
36. 30.197 30.197 ↑ 1.0 1 22,648 / 3

Index Scan using shipmenttable_pkey on shipmenttable (cost=0.43..8.19 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=22,648)

  • Index Cond: (subordertable.subordershipmentid = shipmentid)
37. 5.590 463.312 ↑ 1.1 12,884 3 / 3

Parallel Hash (cost=88,665.23..88,665.23 rows=13,752 width=49) (actual time=463.312..463.312 rows=12,884 loops=3)

  • Buckets: 65536 Batches: 1 Memory Usage: 4032kB
38. 457.722 457.722 ↑ 1.1 12,884 3 / 3

Parallel Seq Scan on payoutdetailstable pd (cost=0.00..88,665.23 rows=13,752 width=49) (actual time=80.964..457.722 rows=12,884 loops=3)

  • Filter: (storeid = 2167498)
  • Rows Removed by Filter: 1480726
39. 70.947 196.994 ↑ 1.3 213,325 3 / 3

Parallel Hash (cost=26,749.38..26,749.38 rows=267,078 width=15) (actual time=196.994..196.994 rows=213,325 loops=3)

  • Buckets: 1048576 Batches: 1 Memory Usage: 45024kB
40. 126.047 126.047 ↑ 1.3 213,325 3 / 3

Parallel Seq Scan on sellerpaymentrecordtable (cost=0.00..26,749.38 rows=267,078 width=15) (actual time=0.019..126.047 rows=213,325 loops=3)

  • Filter: success
  • Rows Removed by Filter: 403186
41. 5.057 62.343 ↑ 1.5 16,948 3 / 3

Parallel Hash (cost=59,839.61..59,839.61 rows=24,958 width=31) (actual time=62.343..62.343 rows=16,948 loops=3)

  • Buckets: 65536 Batches: 1 Memory Usage: 3712kB
42. 55.194 57.286 ↓ 2.0 50,845 1 / 3

Parallel Bitmap Heap Scan on subordersellergstdetailstable ssg (cost=1,124.65..59,839.61 rows=24,958 width=31) (actual time=10.233..171.859 rows=50,845 loops=1)

  • Recheck Cond: (storeid = 2167498)
  • Heap Blocks: exact=23033
43. 2.092 2.092 ↑ 1.2 50,845 1 / 3

Bitmap Index Scan on idx_storeid_subordersellergstdetailstable (cost=0.00..1,109.67 rows=59,899 width=0) (actual time=6.277..6.277 rows=50,845 loops=1)

  • Index Cond: (storeid = 2167498)
Planning time : 10.330 ms
Execution time : 1,984.927 ms