explain.depesz.com

PostgreSQL's explain analyze made readable

Result: v6Pv

Settings
# exclusive inclusive rows x rows loops node
1. 85.073 13,888.294 ↑ 1.7 7,687 1

Gather Merge (cost=349,551.87..351,091.27 rows=13,194 width=464) (actual time=13,856.941..13,888.294 rows=7,687 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 4.619 13,803.221 ↑ 2.6 2,562 3 / 3

Sort (cost=348,551.84..348,568.34 rows=6,597 width=464) (actual time=13,802.763..13,803.221 rows=2,562 loops=3)

  • Sort Key: ((subordertable.time_stamp + '05:30:00'::interval)) DESC
  • Sort Method: quicksort Memory: 1382kB
  • Worker 0: Sort Method: quicksort Memory: 1521kB
  • Worker 1: Sort Method: quicksort Memory: 1341kB
3. 7.393 13,798.602 ↑ 2.6 2,562 3 / 3

Nested Loop (cost=121,543.89..348,133.34 rows=6,597 width=464) (actual time=5,869.858..13,798.602 rows=2,562 loops=3)

  • Join Filter: (subordertable.suborderid = subordertablewithouttax.suborderid)
4. 2.596 11,795.151 ↑ 2.6 2,562 3 / 3

Nested Loop Left Join (cost=121,543.46..294,099.63 rows=6,587 width=289) (actual time=5,866.450..11,795.151 rows=2,562 loops=3)

5. 5.532 8,789.500 ↑ 2.6 2,562 3 / 3

Parallel Hash Left Join (cost=121,543.03..239,979.24 rows=6,587 width=282) (actual time=5,863.462..8,789.500 rows=2,562 loops=3)

  • Hash Cond: (pd.invoiceid = sellerpaymentrecordtable.invoiceid)
6. 3.606 6,637.497 ↑ 2.6 2,562 3 / 3

Nested Loop Left Join (cost=91,455.17..209,857.33 rows=6,587 width=283) (actual time=3,714.077..6,637.497 rows=2,562 loops=3)

7. 3.450 5,929.249 ↑ 2.6 2,562 3 / 3

Nested Loop Left Join (cost=91,454.74..155,736.93 rows=6,587 width=250) (actual time=3,712.681..5,929.249 rows=2,562 loops=3)

8. 6.207 4,421.709 ↑ 2.6 2,562 3 / 3

Nested Loop Left Join (cost=91,454.31..101,628.24 rows=6,587 width=254) (actual time=3,710.343..4,421.709 rows=2,562 loops=3)

9. 2.749 3,710.860 ↑ 2.6 2,562 3 / 3

Merge Join (cost=91,446.13..91,550.19 rows=6,587 width=222) (actual time=3,707.040..3,710.860 rows=2,562 loops=3)

  • Merge Cond: (subordertable.suborderstatusvalue = convertortable.suborderstatusvalue)
10. 2.686 3,707.583 ↓ 2.5 2,562 3 / 3

Sort (cost=91,357.96..91,360.56 rows=1,038 width=198) (actual time=3,706.981..3,707.583 rows=2,562 loops=3)

  • Sort Key: subordertable.suborderstatusvalue
  • Sort Method: quicksort Memory: 802kB
  • Worker 0: Sort Method: quicksort Memory: 870kB
  • Worker 1: Sort Method: quicksort Memory: 774kB
11. 1.792 3,704.897 ↓ 2.5 2,562 3 / 3

Nested Loop Left Join (cost=11,186.13..91,305.96 rows=1,038 width=198) (actual time=198.413..3,704.897 rows=2,562 loops=3)

12. 2.223 3,695.418 ↓ 2.5 2,562 3 / 3

Nested Loop (cost=11,185.70..82,829.94 rows=1,038 width=198) (actual time=195.011..3,695.418 rows=2,562 loops=3)

13. 0.977 2,983.429 ↓ 2.5 2,562 3 / 3

Nested Loop Left Join (cost=11,185.26..74,351.40 rows=1,038 width=184) (actual time=192.573..2,983.429 rows=2,562 loops=3)

14. 1.029 2,782.590 ↓ 2.5 2,562 3 / 3

Nested Loop Left Join (cost=11,184.84..65,874.11 rows=1,038 width=158) (actual time=188.517..2,782.590 rows=2,562 loops=3)

15. 0.613 2,622.696 ↓ 2.5 2,562 3 / 3

Nested Loop (cost=11,184.41..57,397.66 rows=1,038 width=152) (actual time=184.113..2,622.696 rows=2,562 loops=3)

16. 2.441 1,668.895 ↓ 2.5 2,562 3 / 3

Nested Loop (cost=11,183.84..48,499.96 rows=1,038 width=123) (actual time=182.936..1,668.895 rows=2,562 loops=3)

17. 132.788 190.550 ↓ 1.3 2,562 3 / 3

Parallel Bitmap Heap Scan on subordertable (cost=11,183.41..31,803.49 rows=2,038 width=89) (actual time=175.486..190.550 rows=2,562 loops=3)

  • Recheck Cond: ((parentstoreid = 2167498) AND (date((time_stamp + '05:30:00'::interval)) >= '2019-08-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: 403
  • Heap Blocks: exact=1966
18. 0.684 57.762 ↓ 0.0 0 1 / 3

BitmapAnd (cost=11,183.41..11,183.41 rows=5,807 width=0) (actual time=173.285..173.285 rows=0 loops=1)

19. 6.876 6.876 ↑ 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=20.628..20.628 rows=50,845 loops=1)

  • Index Cond: (parentstoreid = 2167498)
20. 50.202 50.202 ↑ 1.0 464,235 1 / 3

Bitmap Index Scan on idx_date_subordertable (cost=0.00..10,185.33 rows=485,290 width=0) (actual time=150.605..150.605 rows=464,235 loops=1)

  • Index Cond: ((date((time_stamp + '05:30:00'::interval)) >= '2019-08-10'::date) AND (date((time_stamp + '05:30:00'::interval)) <= '2019-09-10'::date))
21. 1,475.904 1,475.904 ↑ 1.0 1 7,687 / 3

Index Scan using idx_suborderid_subordertableforresellers on subordertableforresellers (cost=0.43..8.18 rows=1 width=34) (actual time=0.576..0.576 rows=1 loops=7,687)

  • Index Cond: (suborderid = subordertable.suborderid)
22. 953.188 953.188 ↑ 1.0 1 7,687 / 3

Index Scan using producttable_pkey on producttable (cost=0.57..8.57 rows=1 width=37) (actual time=0.372..0.372 rows=1 loops=7,687)

  • Index Cond: (productid = subordertable.productid)
23. 158.865 158.865 ↓ 0.0 0 7,687 / 3

Index Scan using productskudetails_productvariantid on productskudetails (cost=0.42..8.17 rows=1 width=22) (actual time=0.062..0.062 rows=0 loops=7,687)

  • Index Cond: (subordertable.productvariantid = productvariantid)
24. 199.862 199.862 ↓ 0.0 0 7,687 / 3

Index Scan using subordermetadetails_pkey on subordermetadetails (cost=0.42..8.17 rows=1 width=34) (actual time=0.078..0.078 rows=0 loops=7,687)

  • Index Cond: (subordertable.suborderid = suborderid)
25. 709.766 709.766 ↑ 1.0 1 7,687 / 3

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

  • Index Cond: (storeid = subordertableforresellers.resellerid)
26. 7.687 7.687 ↑ 1.0 1 7,687 / 3

Index Scan using storesellingstates_pkey on storesellingstates ws (cost=0.43..8.17 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=7,687)

  • Index Cond: (storeid = subordertableforresellers.wholesellerid)
27. 0.508 0.528 ↓ 2.0 2,523 3 / 3

Sort (cost=88.17..91.35 rows=1,270 width=36) (actual time=0.052..0.528 rows=2,523 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
28. 0.020 0.020 ↑ 141.1 9 3 / 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)

29. 17.936 704.642 ↑ 1.0 1 7,687 / 3

Hash Right Join (cost=8.18..9.70 rows=1 width=48) (actual time=0.272..0.275 rows=1 loops=7,687)

  • Hash Cond: (statecodemappingtable.id = rs.sellingstateid)
30. 10.249 10.249 ↑ 1.0 37 7,687 / 3

Seq Scan on statecodemappingtable (cost=0.00..1.37 rows=37 width=40) (actual time=0.002..0.004 rows=37 loops=7,687)

31. 2.562 676.456 ↑ 1.0 1 7,687 / 3

Hash (cost=8.17..8.17 rows=1 width=16) (actual time=0.264..0.264 rows=1 loops=7,687)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 673.894 673.894 ↑ 1.0 1 7,687 / 3

Index Scan using storesellingstates_pkey on storesellingstates rs (cost=0.43..8.17 rows=1 width=16) (actual time=0.263..0.263 rows=1 loops=7,687)

  • Index Cond: (storeid = subordertableforresellers.resellerid)
33. 1,504.090 1,504.090 ↑ 1.0 1 7,687 / 3

Index Scan using shipmenttable_pkey on shipmenttable (cost=0.43..8.21 rows=1 width=12) (actual time=0.587..0.587 rows=1 loops=7,687)

  • Index Cond: (subordertable.subordershipmentid = shipmentid)
34. 704.642 704.642 ↓ 0.0 0 7,687 / 3

Index Scan using payoutdetailstable_new_suborderid_storeid_key on payoutdetailstable pd (cost=0.43..8.22 rows=1 width=49) (actual time=0.275..0.275 rows=0 loops=7,687)

  • Index Cond: ((suborderid = subordertable.suborderid) AND (subordertable.parentstoreid = storeid) AND (storeid = 2167498))
35. 68.574 2,146.471 ↑ 1.3 213,325 3 / 3

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

  • Buckets: 1048576 Batches: 1 Memory Usage: 45024kB
36. 2,077.897 2,077.897 ↑ 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.367..2,077.897 rows=213,325 loops=3)

  • Filter: success
  • Rows Removed by Filter: 403186
37. 3,003.055 3,003.055 ↑ 1.0 1 7,687 / 3

Index Scan using idx_soid_store_subordersellergstdetailstable on subordersellergstdetailstable ssg (cost=0.43..8.22 rows=1 width=31) (actual time=1.172..1.172 rows=1 loops=7,687)

  • Index Cond: ((suborderid = subordertable.suborderid) AND (subordertable.parentstoreid = storeid) AND (storeid = 2167498))
38. 1,996.058 1,996.058 ↑ 1.0 1 7,687 / 3

Index Scan using subordertablewithouttax_pkey on subordertablewithouttax (cost=0.43..8.17 rows=1 width=26) (actual time=0.779..0.779 rows=1 loops=7,687)

  • Index Cond: (suborderid = subordertableforresellers.suborderid)
Planning time : 150.993 ms
Execution time : 13,888.892 ms