explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DOLV

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

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

  • Workers Planned: 2
  • Workers Launched: 2
2. 8.853 1,330.977 ↑ 2.6 2,562 3

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

  • Sort Key: ((subordertable.time_stamp + '05:30:00'::interval)) DESC
  • Sort Method: quicksort Memory: 1683kB
  • Worker 0: Sort Method: quicksort Memory: 1279kB
  • Worker 1: Sort Method: quicksort Memory: 1282kB
3. 9.375 1,322.124 ↑ 2.6 2,562 3

Nested Loop (cost=121,543.89..348,133.34 rows=6,597 width=464) (actual time=323.684..440.708 rows=2,562 loops=3)

  • Join Filter: (subordertable.suborderid = subordertablewithouttax.suborderid)
4. 7.422 1,266.627 ↑ 2.6 2,562 3

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

5. 12.138 1,190.022 ↑ 2.6 2,562 3

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

  • Hash Cond: (pd.invoiceid = sellerpaymentrecordtable.invoiceid)
6. 4.658 603.417 ↑ 2.6 2,562 3

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

7. 4.342 568.011 ↑ 2.6 2,562 3

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

8. 7.835 502.173 ↑ 2.6 2,562 3

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

9. 4.938 394.407 ↑ 2.6 2,562 3

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

  • Merge Cond: (subordertable.suborderstatusvalue = convertortable.suborderstatusvalue)
10. 6.357 388.674 ↓ 2.5 2,562 3

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

  • Sort Key: subordertable.suborderstatusvalue
  • Sort Method: quicksort Memory: 968kB
  • Worker 0: Sort Method: quicksort Memory: 733kB
  • Worker 1: Sort Method: quicksort Memory: 745kB
11. 3.403 382.317 ↓ 2.5 2,562 3

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

12. 0.944 363.540 ↓ 2.5 2,562 3

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

13. 0.000 331.848 ↓ 2.5 2,562 3

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

14. 3.160 308.895 ↓ 2.5 2,562 3

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

15. 5.456 290.361 ↓ 2.5 2,562 3

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

16. 6.310 208.035 ↓ 2.5 2,562 3

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

17. 119.387 163.290 ↓ 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=45.070..54.430 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=2502
18. 2.016 43.903 ↓ 0.0 0 1

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

19. 9.251 9.251 ↑ 1.1 50,845 1

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

  • Index Cond: (parentstoreid = 2167498)
20. 32.636 32.636 ↑ 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=32.636..32.636 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. 38.435 38.435 ↑ 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.005..0.005 rows=1 loops=7,687)

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

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

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

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

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

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

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

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=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.738 0.795 ↓ 2.0 2,523 3

Sort (cost=88.17..91.35 rows=1,270 width=36) (actual time=0.053..0.265 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. 53.809 99.931 ↑ 1.0 1 7,687

Hash Right Join (cost=8.18..9.70 rows=1 width=48) (actual time=0.010..0.013 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 23.061 ↑ 1.0 1 7,687

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

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

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

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

  • Index Cond: (subordertable.subordershipmentid = shipmentid)
34. 30.748 30.748 ↓ 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.004..0.004 rows=0 loops=7,687)

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

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

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

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

  • Filter: success
  • Rows Removed by Filter: 403186
37. 69.183 69.183 ↑ 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=0.009..0.009 rows=1 loops=7,687)

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

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

  • Index Cond: (suborderid = subordertableforresellers.suborderid)
Planning time : 21.933 ms
Execution time : 485.497 ms