explain.depesz.com

PostgreSQL's explain analyze made readable

Result: v6Pv

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 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. 13.857 41,409.663 ↑ 2.6 2,562 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. 22.180 41,395.806 ↑ 2.6 2,562 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. 7.789 35,385.453 ↑ 2.6 2,562 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. 16.596 26,368.500 ↑ 2.6 2,562 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. 10.819 19,912.491 ↑ 2.6 2,562 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. 10.351 17,787.747 ↑ 2.6 2,562 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. 18.622 13,265.127 ↑ 2.6 2,562 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. 8.247 11,132.580 ↑ 2.6 2,562 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. 8.058 11,122.749 ↓ 2.5 2,562 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. 5.376 11,114.691 ↓ 2.5 2,562 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. 6.668 11,086.254 ↓ 2.5 2,562 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. 2.931 8,950.287 ↓ 2.5 2,562 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. 3.088 8,347.770 ↓ 2.5 2,562 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. 1.839 7,868.088 ↓ 2.5 2,562 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. 7.323 5,006.685 ↓ 2.5 2,562 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. 398.365 571.650 ↓ 1.3 2,562 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. 2.052 173.285 ↓ 0.0 0 1

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

19. 20.628 20.628 ↑ 1.1 50,845 1

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. 150.605 150.605 ↑ 1.0 464,235 1

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. 4,427.712 4,427.712 ↑ 1.0 1 7,687

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. 2,859.564 2,859.564 ↑ 1.0 1 7,687

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. 476.594 476.594 ↓ 0.0 0 7,687

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. 599.586 599.586 ↓ 0.0 0 7,687

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. 2,129.299 2,129.299 ↑ 1.0 1 7,687

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. 23.061 23.061 ↑ 1.0 1 7,687

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. 1.524 1.584 ↓ 2.0 2,523 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.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)

29. 53.809 2,113.925 ↑ 1.0 1 7,687

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. 30.748 30.748 ↑ 1.0 37 7,687

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. 7.687 2,029.368 ↑ 1.0 1 7,687

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. 2,021.681 2,021.681 ↑ 1.0 1 7,687

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. 4,512.269 4,512.269 ↑ 1.0 1 7,687

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. 2,113.925 2,113.925 ↓ 0.0 0 7,687

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. 205.722 6,439.413 ↑ 1.3 213,325 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. 6,233.691 6,233.691 ↑ 1.3 213,325 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. 9,009.164 9,009.164 ↑ 1.0 1 7,687

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. 5,988.173 5,988.173 ↑ 1.0 1 7,687

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