explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fPtg

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 27,392.365 ↑ 1.7 7,687 1

Gather Merge (cost=355,695.23..357,234.64 rows=13,194 width=464) (actual time=27,359.411..27,392.365 rows=7,687 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 9.615 82,015.524 ↑ 2.6 2,562 3

Sort (cost=354,695.21..354,711.70 rows=6,597 width=464) (actual time=27,338.152..27,338.508 rows=2,562 loops=3)

  • Sort Key: ((subordertable.time_stamp + '05:30:00'::interval)) DESC
  • Sort Method: quicksort Memory: 1427kB
  • Worker 0: Sort Method: quicksort Memory: 1306kB
  • Worker 1: Sort Method: quicksort Memory: 1511kB
3. 15.114 82,005.909 ↑ 2.6 2,562 3

Nested Loop (cost=181,757.44..354,276.71 rows=6,597 width=464) (actual time=26,239.059..27,335.303 rows=2,562 loops=3)

  • Join Filter: (subordertable.suborderid = subordertablewithouttax.suborderid)
4. 29.529 80,030.610 ↑ 2.6 2,562 3

Parallel Hash Left Join (cost=181,757.01..300,243.00 rows=6,587 width=289) (actual time=26,234.309..26,676.870 rows=2,562 loops=3)

  • Hash Cond: ((subordertable.parentstoreid = ssg.storeid) AND (subordertable.suborderid = ssg.suborderid))
5. 10.353 12,615.936 ↑ 2.6 2,562 3

Parallel Hash Left Join (cost=121,543.03..239,979.24 rows=6,587 width=282) (actual time=3,765.071..4,205.312 rows=2,562 loops=3)

  • Hash Cond: (pd.invoiceid = sellerpaymentrecordtable.invoiceid)
6. 2.242 10,018.332 ↑ 2.6 2,562 3

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

7. 5.637 8,986.032 ↑ 2.6 2,562 3

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

8. 11.702 8,934.273 ↑ 2.6 2,562 3

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

9. 5.223 8,684.274 ↑ 2.6 2,562 3

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

  • Merge Cond: (subordertable.suborderstatusvalue = convertortable.suborderstatusvalue)
10. 7.920 8,678.154 ↓ 2.5 2,562 3

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

  • Sort Key: subordertable.suborderstatusvalue
  • Sort Method: quicksort Memory: 826kB
  • Worker 0: Sort Method: quicksort Memory: 757kB
  • Worker 1: Sort Method: quicksort Memory: 863kB
11. 6.991 8,670.234 ↓ 2.5 2,562 3

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

12. 5.039 8,647.869 ↓ 2.5 2,562 3

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

13. 0.203 7,989.435 ↓ 2.5 2,562 3

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

14. 3.080 7,681.752 ↓ 2.5 2,562 3

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

15. 6.147 7,348.131 ↓ 2.5 2,562 3

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

16. 9.429 6,534.849 ↓ 2.5 2,562 3

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

17. 4,862.863 5,003.394 ↓ 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=142.806..1,667.798 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=1987
18. 2.264 140.531 ↓ 0.0 0 1

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

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

  • Index Cond: (parentstoreid = 2167498)
20. 129.429 129.429 ↑ 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=129.429..129.429 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,522.026 1,522.026 ↑ 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.198..0.198 rows=1 loops=7,687)

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

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

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

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

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

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

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

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

  • Index Cond: (storeid = subordertableforresellers.resellerid)
26. 15.374 15.374 ↑ 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.002..0.002 rows=1 loops=7,687)

  • Index Cond: (storeid = subordertableforresellers.wholesellerid)
27. 0.840 0.897 ↓ 2.0 2,523 3

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

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

29. 46.122 238.297 ↑ 1.0 1 7,687

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

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

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

31. 0.000 169.114 ↑ 1.0 1 7,687

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 169.114 169.114 ↑ 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.021..0.022 rows=1 loops=7,687)

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

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

  • Index Cond: (subordertable.subordershipmentid = shipmentid)
34. 1,030.058 1,030.058 ↓ 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.134..0.134 rows=0 loops=7,687)

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

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

  • Buckets: 1048576 Batches: 1 Memory Usage: 45024kB
36. 2,365.053 2,365.053 ↑ 1.3 213,325 3

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

  • Filter: success
  • Rows Removed by Filter: 403186
37. 33.201 67,385.145 ↑ 1.5 16,948 3

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3744kB
38. 67,324.351 67,351.944 ↑ 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=14.908..22,450.648 rows=16,948 loops=3)

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

  • Index Cond: (storeid = 2167498)
40. 1,960.185 1,960.185 ↑ 1.0 1 7,687

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

  • Index Cond: (suborderid = subordertableforresellers.suborderid)
Planning time : 152.003 ms
Execution time : 27,392.892 ms