explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TCNT

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 45,871.679 ↑ 1.1 22,648 1

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

  • Workers Planned: 2
  • Workers Launched: 2
2. 36.705 136,938.774 ↑ 1.7 7,549 3

Sort (cost=551,905.48..551,936.69 rows=12,484 width=464) (actual time=45,645.602..45,646.258 rows=7,549 loops=3)

  • Sort Key: ((subordertable.time_stamp + '05:30:00'::interval)) DESC
  • Sort Method: quicksort Memory: 4076kB
  • Worker 0: Sort Method: quicksort Memory: 4060kB
  • Worker 1: Sort Method: quicksort Memory: 4110kB
3. 580.032 136,902.069 ↑ 1.7 7,549 3

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

  • Hash Cond: (subordertablewithouttax.suborderid = subordertable.suborderid)
4. 545.421 545.421 ↑ 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.013..181.807 rows=1,498,692 loops=3)

5. 428.055 135,776.616 ↑ 1.7 7,549 3

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

  • Buckets: 32768 Batches: 1 Memory Usage: 7040kB
6. 19.110 135,348.561 ↑ 1.7 7,549 3

Parallel Hash Left Join (cost=348,584.17..470,164.88 rows=12,465 width=289) (actual time=39,890.812..45,116.187 rows=7,549 loops=3)

  • Hash Cond: ((subordertable.parentstoreid = ssg.storeid) AND (subordertable.suborderid = ssg.suborderid))
7. 14.718 29,267.562 ↑ 1.7 7,549 3

Parallel Hash Left Join (cost=288,370.18..409,856.72 rows=12,465 width=282) (actual time=4,536.712..9,755.854 rows=7,549 loops=3)

  • Hash Cond: (pd.invoiceid = sellerpaymentrecordtable.invoiceid)
8. 16.011 20,409.369 ↑ 1.7 7,549 3

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

  • Hash Cond: ((subordertable.parentstoreid = pd.storeid) AND (subordertable.suborderid = pd.suborderid))
9. 19.517 19,574.073 ↑ 1.7 7,549 3

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

10. 34.084 6,078.996 ↑ 1.7 7,549 3

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

11. 14.094 3,961.296 ↑ 1.7 7,549 3

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

  • Merge Cond: (subordertable.suborderstatusvalue = convertortable.suborderstatusvalue)
12. 17.079 3,944.661 ↓ 3.8 7,549 3

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

  • Sort Key: subordertable.suborderstatusvalue
  • Sort Method: quicksort Memory: 2346kB
  • Worker 0: Sort Method: quicksort Memory: 2064kB
  • Worker 1: Sort Method: quicksort Memory: 2745kB
13. 19.913 3,927.582 ↓ 3.8 7,549 3

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

14. 3.204 3,885.021 ↓ 3.8 7,549 3

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

15. 29.736 3,338.265 ↓ 3.8 7,549 3

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

  • Hash Cond: (subordertable.suborderid = subordermetadetails.suborderid)
16. 0.000 2,885.304 ↓ 3.8 7,549 3

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

17. 8.937 2,841.720 ↓ 3.8 7,549 3

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

18. 16.672 1,813.623 ↓ 3.8 7,549 3

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

19. 1,579.357 1,683.711 ↓ 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=107.293..561.237 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=5168
20. 3.034 104.354 ↓ 0.0 0 1

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

21. 12.584 12.584 ↑ 1.1 50,845 1

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

  • Index Cond: (parentstoreid = 2167498)
22. 88.736 88.736 ↑ 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=88.736..88.736 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. 113.240 113.240 ↑ 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.005..0.005 rows=1 loops=22,648)

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

Index Scan using producttable_pkey on producttable (cost=0.57..8.56 rows=1 width=37) (actual time=0.045..0.045 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. 260.511 423.225 ↑ 1.3 275,709 3

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

  • Buckets: 1048576 Batches: 1 Memory Usage: 64960kB
27. 162.714 162.714 ↑ 1.3 275,709 3

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

28. 543.552 543.552 ↑ 1.0 1 22,648

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

  • Index Cond: (storeid = subordertableforresellers.resellerid)
29. 22.648 22.648 ↑ 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.001..0.001 rows=1 loops=22,648)

  • Index Cond: (storeid = subordertableforresellers.wholesellerid)
30. 2.481 2.541 ↓ 5.9 7,441 3

Sort (cost=88.17..91.35 rows=1,270 width=36) (actual time=0.051..0.847 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.060 0.060 ↑ 141.1 9 3

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

32. 113.240 2,083.616 ↑ 1.0 1 22,648

Hash Right Join (cost=8.18..9.70 rows=1 width=48) (actual time=0.089..0.092 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 1,902.432 ↑ 1.0 1 22,648

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
35. 1,879.784 1,879.784 ↑ 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.083..0.083 rows=1 loops=22,648)

  • Index Cond: (storeid = subordertableforresellers.resellerid)
36. 13,475.560 13,475.560 ↑ 1.0 1 22,648

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

  • Index Cond: (subordertable.subordershipmentid = shipmentid)
37. 16.761 819.285 ↑ 1.1 12,884 3

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

  • Buckets: 65536 Batches: 1 Memory Usage: 4064kB
38. 802.524 802.524 ↑ 1.1 12,884 3

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

  • Filter: (storeid = 2167498)
  • Rows Removed by Filter: 1480726
39. 239.313 8,843.475 ↑ 1.3 213,325 3

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

  • Buckets: 1048576 Batches: 1 Memory Usage: 44992kB
40. 8,604.162 8,604.162 ↑ 1.3 213,325 3

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

  • Filter: success
  • Rows Removed by Filter: 403186
41. 40.005 106,061.889 ↑ 1.5 16,948 3

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3744kB
42. 105,983.580 106,021.884 ↑ 1.5 16,948 3

Parallel Bitmap Heap Scan on subordersellergstdetailstable ssg (cost=1,124.65..59,839.61 rows=24,958 width=31) (actual time=18.219..35,340.628 rows=16,948 loops=3)

  • Recheck Cond: (storeid = 2167498)
  • Heap Blocks: exact=7721
43. 38.304 38.304 ↑ 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=38.304..38.304 rows=50,845 loops=1)

  • Index Cond: (storeid = 2167498)
Planning time : 163.510 ms
Execution time : 45,873.148 ms