explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nbe4

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 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. 21.492 5,344.242 ↑ 1.7 7,549 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. 590.601 5,322.750 ↑ 1.7 7,549 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. 522.780 522.780 ↑ 1.3 1,498,692 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. 59.034 4,209.369 ↑ 1.7 7,549 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. 16.614 4,150.335 ↑ 1.7 7,549 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. 13.596 3,946.692 ↑ 1.7 7,549 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. 14.193 3,342.114 ↑ 1.7 7,549 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. 16.406 1,937.985 ↑ 1.7 7,549 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. 19.554 1,830.987 ↑ 1.7 7,549 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. 12.885 1,539.657 ↑ 1.7 7,549 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. 16.746 1,524.483 ↓ 3.8 7,549 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. 6.286 1,507.737 ↓ 3.8 7,549 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. 14.456 1,456.155 ↓ 3.8 7,549 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. 29.028 1,351.107 ↓ 3.8 7,549 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. 2.512 874.494 ↓ 3.8 7,549 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. 7.951 826.686 ↓ 3.8 7,549 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. 5.063 637.551 ↓ 3.8 7,549 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. 252.469 338.064 ↓ 2.0 7,549 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. 2.195 85.595 ↓ 0.0 0 1

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

21. 8.409 8.409 ↑ 1.1 50,845 1

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. 74.991 74.991 ↑ 1.0 884,958 1

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. 294.424 294.424 ↑ 1.0 1 22,648

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. 181.184 181.184 ↑ 1.0 1 22,648

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. 45.296 45.296 ↓ 0.0 0 22,648

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. 263.277 447.585 ↑ 1.3 275,709 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. 184.308 184.308 ↑ 1.3 275,709 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. 90.592 90.592 ↑ 1.0 1 22,648

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. 45.296 45.296 ↑ 1.0 1 22,648

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. 2.226 2.289 ↓ 5.9 7,441 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.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)

32. 135.888 271.776 ↑ 1.0 1 22,648

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. 67.944 67.944 ↑ 1.0 37 22,648

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. 22.648 67.944 ↑ 1.0 1 22,648

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. 45.296 45.296 ↑ 1.0 1 22,648

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. 90.592 90.592 ↑ 1.0 1 22,648

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. 16.770 1,389.936 ↑ 1.1 12,884 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. 1,373.166 1,373.166 ↑ 1.1 12,884 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. 212.841 590.982 ↑ 1.3 213,325 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. 378.141 378.141 ↑ 1.3 213,325 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. 15.170 187.029 ↑ 1.5 16,948 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. 165.582 171.859 ↓ 2.0 50,845 1

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. 6.277 6.277 ↑ 1.2 50,845 1

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