explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pVkO

Settings
# exclusive inclusive rows x rows loops node
1. 1.255 68,134.963 ↓ 21.1 1,285 1

Sort (cost=716,944.04..716,944.19 rows=61 width=367) (actual time=68,134.822..68,134.963 rows=1,285 loops=1)

  • Sort Key: ((subordertable.time_stamp + '05:30:00'::interval)) DESC
  • Sort Method: quicksort Memory: 699kB
2. 1.795 68,133.708 ↓ 21.1 1,285 1

Nested Loop Left Join (cost=714,416.48..716,942.23 rows=61 width=367) (actual time=67,819.864..68,133.708 rows=1,285 loops=1)

3. 1.088 68,129.343 ↓ 21.1 1,285 1

Nested Loop Left Join (cost=714,416.05..716,425.86 rows=61 width=334) (actual time=67,819.848..68,129.343 rows=1,285 loops=1)

4. 1.964 68,126.970 ↓ 21.1 1,285 1

Nested Loop Left Join (cost=714,415.62..716,011.79 rows=61 width=334) (actual time=67,819.839..68,126.970 rows=1,285 loops=1)

5. 1.334 68,110.871 ↓ 21.1 1,285 1

Hash Right Join (cost=714,408.82..715,911.74 rows=61 width=302) (actual time=67,819.791..68,110.871 rows=1,285 loops=1)

  • Hash Cond: ((pd.storeid = subordertable.parentstoreid) AND (pd.suborderid = subordertable.suborderid))
6. 396.965 68,077.800 ↓ 2.8 1,192 1

Subquery Scan on pd (cost=706,929.46..708,429.16 rows=428 width=57) (actual time=65,861.435..68,077.800 rows=1,192 loops=1)

  • Filter: (pd.storeid = 2371830)
  • Rows Removed by Filter: 4544310
7. 1,007.208 67,680.835 ↓ 53.0 4,545,502 1

Unique (cost=706,929.46..707,357.95 rows=85,697 width=65) (actual time=65,173.691..67,680.835 rows=4,545,502 loops=1)

8. 8,509.207 66,673.627 ↓ 127.3 10,908,222 1

Sort (cost=706,929.46..707,143.70 rows=85,697 width=65) (actual time=65,173.690..66,673.627 rows=10,908,222 loops=1)

  • Sort Key: pd_1.suborderid
  • Sort Method: external merge Disk: 1065032kB
9. 3,329.922 58,164.420 ↓ 127.3 10,908,222 1

Hash Left Join (cost=116,271.43..699,907.90 rows=85,697 width=65) (actual time=7,273.173..58,164.420 rows=10,908,222 loops=1)

  • Hash Cond: (pd_1.invoiceid = stcs.invoiceid)
10. 6,778.317 54,809.941 ↓ 127.3 10,908,222 1

Nested Loop (cost=114,468.20..697,019.77 rows=85,697 width=59) (actual time=7,248.557..54,809.941 rows=10,908,222 loops=1)

11. 4,712.676 25,874.598 ↓ 45.4 11,078,513 1

Merge Join (cost=114,467.77..578,811.09 rows=243,889 width=52) (actual time=7,248.536..25,874.598 rows=11,078,513 loops=1)

  • Merge Cond: ((unnest(sellerinvoicemappingtable.payoutids)) = pd_1.id)
12. 3,042.841 12,112.373 ↓ 45.4 11,078,653 1

Unique (cost=114,467.34..116,906.23 rows=243,889 width=22) (actual time=7,150.274..12,112.373 rows=11,078,653 loops=1)

13. 7,420.252 9,069.532 ↓ 45.4 11,078,653 1

Sort (cost=114,467.34..115,077.06 rows=243,889 width=22) (actual time=7,150.272..9,069.532 rows=11,078,653 loops=1)

  • Sort Key: (unnest(sellerinvoicemappingtable.payoutids)), sellerinvoicemappingtable.invoiceid, sellershop101serviceinvoicetable.igst
  • Sort Method: external merge Disk: 355888kB
14. 1,041.738 1,649.280 ↓ 45.4 11,078,653 1

Gather (cost=6,582.31..92,644.32 rows=243,889 width=22) (actual time=72.245..1,649.280 rows=11,078,653 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
15. 463.138 607.542 ↑ 2.8 3,692,884 3 / 3

ProjectSet (cost=5,582.31..67,255.42 rows=10,162,000 width=22) (actual time=69.791..607.542 rows=3,692,884 loops=3)

16. 38.892 144.404 ↑ 1.3 80,064 3 / 3

Parallel Hash Left Join (cost=5,582.31..15,683.26 rows=101,620 width=269) (actual time=69.787..144.404 rows=80,064 loops=3)

  • Hash Cond: (sellerinvoicemappingtable.invoiceid = sellershop101serviceinvoicetable.invoiceid)
17. 36.277 36.277 ↑ 1.3 80,064 3 / 3

Parallel Seq Scan on sellerinvoicemappingtable (cost=0.00..9,834.20 rows=101,620 width=263) (actual time=0.025..36.277 rows=80,064 loops=3)

18. 23.462 69.235 ↑ 1.2 80,064 3 / 3

Parallel Hash (cost=4,331.58..4,331.58 rows=100,058 width=14) (actual time=69.235..69.235 rows=80,064 loops=3)

  • Buckets: 262144 Batches: 1 Memory Usage: 13376kB
19. 45.773 45.773 ↑ 1.2 80,064 3 / 3

Parallel Seq Scan on sellershop101serviceinvoicetable (cost=0.00..4,331.58 rows=100,058 width=14) (actual time=0.057..45.773 rows=80,064 loops=3)

20. 9,049.549 9,049.549 ↓ 1.0 8,811,329 1

Index Scan using payoutdetailstable_new_pkey on payoutdetailstable pd_1 (cost=0.43..434,942.47 rows=8,589,956 width=54) (actual time=1.181..9,049.549 rows=8,811,329 loops=1)

21. 22,157.026 22,157.026 ↑ 1.0 1 11,078,513

Index Scan using sellerpaymentrecordtable_invoiceid_success_key on sellerpaymentrecordtable (cost=0.43..0.47 rows=1 width=15) (actual time=0.002..0.002 rows=1 loops=11,078,513)

  • Index Cond: ((invoiceid = pd_1.invoiceid) AND (success = true))
  • Filter: success
22. 8.062 24.557 ↑ 1.0 54,866 1

Hash (cost=1,116.99..1,116.99 rows=54,899 width=14) (actual time=24.557..24.557 rows=54,866 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3084kB
23. 16.495 16.495 ↑ 1.0 54,866 1

Seq Scan on subordertcsgsttable stcs (cost=0.00..1,116.99 rows=54,899 width=14) (actual time=1.795..16.495 rows=54,866 loops=1)

24. 1.027 31.737 ↓ 21.1 1,285 1

Hash (cost=7,478.44..7,478.44 rows=61 width=269) (actual time=31.737..31.737 rows=1,285 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 400kB
25. 0.948 30.710 ↓ 21.1 1,285 1

Nested Loop Left Join (cost=4.15..7,478.44 rows=61 width=269) (actual time=0.174..30.710 rows=1,285 loops=1)

26. 0.169 25.907 ↓ 21.1 1,285 1

Nested Loop (cost=3.59..6,954.88 rows=61 width=254) (actual time=0.158..25.907 rows=1,285 loops=1)

  • Join Filter: (subordertable.suborderid = subordertablewithouttax.suborderid)
27. 0.818 21.883 ↓ 20.1 1,285 1

Nested Loop Left Join (cost=3.16..6,519.57 rows=64 width=243) (actual time=0.147..21.883 rows=1,285 loops=1)

28. 0.238 19.780 ↓ 20.1 1,285 1

Nested Loop (cost=2.73..6,085.64 rows=64 width=237) (actual time=0.135..19.780 rows=1,285 loops=1)

29. 0.014 0.014 ↑ 1.0 1 1

Index Scan using storegstdetailstable_pkey on storegstdetailstable (cost=0.43..8.45 rows=1 width=24) (actual time=0.013..0.014 rows=1 loops=1)

  • Index Cond: (storeid = 2371830)
30. 0.702 19.528 ↓ 20.1 1,285 1

Nested Loop (cost=2.29..6,076.55 rows=64 width=221) (actual time=0.120..19.528 rows=1,285 loops=1)

31. 0.008 16.256 ↓ 20.1 1,285 1

Nested Loop Left Join (cost=1.86..5,642.03 rows=64 width=207) (actual time=0.108..16.256 rows=1,285 loops=1)

32. 0.000 13.678 ↓ 20.1 1,285 1

Nested Loop (cost=1.44..5,103.96 rows=64 width=180) (actual time=0.098..13.678 rows=1,285 loops=1)

33. 0.823 9.857 ↓ 20.1 1,285 1

Nested Loop (cost=0.86..4,554.36 rows=64 width=147) (actual time=0.087..9.857 rows=1,285 loops=1)

  • Join Filter: (subordertable.suborderstatusvalue = convertortable.suborderstatusvalue)
  • Rows Removed by Join Filter: 14135
34. 0.143 7.749 ↓ 20.1 1,285 1

Nested Loop (cost=0.86..4,541.69 rows=64 width=123) (actual time=0.074..7.749 rows=1,285 loops=1)

35. 3.751 3.751 ↓ 12.1 1,285 1

Index Scan using idx_parentstoreid_subordertable on subordertable (cost=0.43..3,644.93 rows=106 width=81) (actual time=0.062..3.751 rows=1,285 loops=1)

  • Index Cond: (parentstoreid = 2371830)
  • Filter: ((date((time_stamp + '05:30:00'::interval)) >= '2019-12-01'::date) AND (date((time_stamp + '05:30:00'::interval)) <= '2019-12-31'::date) AND (suborderstatusvalue = ANY ('{3,4,5,6,7,8,9,10,11,12}'::bigint[])))
  • Rows Removed by Filter: 1910
36. 3.855 3.855 ↑ 1.0 1 1,285

Index Scan using idx_suborderid_subordertableforresellers on subordertableforresellers (cost=0.43..8.45 rows=1 width=42) (actual time=0.003..0.003 rows=1 loops=1,285)

  • Index Cond: (suborderid = subordertable.suborderid)
37. 1.279 1.285 ↑ 1.0 12 1,285

Materialize (cost=0.00..1.18 rows=12 width=36) (actual time=0.000..0.001 rows=12 loops=1,285)

38. 0.006 0.006 ↑ 1.0 12 1

Seq Scan on convertortable (cost=0.00..1.12 rows=12 width=36) (actual time=0.005..0.006 rows=12 loops=1)

39. 3.855 3.855 ↑ 1.0 1 1,285

Index Scan using producttable_pkey on producttable (cost=0.57..8.59 rows=1 width=41) (actual time=0.003..0.003 rows=1 loops=1,285)

  • Index Cond: (productid = subordertable.productid)
40. 2.570 2.570 ↓ 0.0 0 1,285

Index Scan using subordermetadetails_pkey on subordermetadetails (cost=0.43..8.41 rows=1 width=35) (actual time=0.002..0.002 rows=0 loops=1,285)

  • Index Cond: (subordertable.suborderid = suborderid)
41. 2.570 2.570 ↑ 1.0 1 1,285

Index Scan using storetable_pkey on storetable (cost=0.43..6.79 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=1,285)

  • Index Cond: (storeid = subordertableforresellers.resellerid)
42. 1.285 1.285 ↑ 1.0 1 1,285

Index Scan using productskudetails_productvariantid_unique on productskudetails (cost=0.43..6.78 rows=1 width=22) (actual time=0.001..0.001 rows=1 loops=1,285)

  • Index Cond: (productvariantid = subordertableforresellers.productvariantid)
43. 3.855 3.855 ↑ 1.0 1 1,285

Index Scan using subordertablewithouttax_pkey on subordertablewithouttax (cost=0.43..6.79 rows=1 width=27) (actual time=0.003..0.003 rows=1 loops=1,285)

  • Index Cond: (suborderid = subordertableforresellers.suborderid)
44. 3.855 3.855 ↑ 1.0 1 1,285

Index Scan using idx_soid_store_subordersellergstdetailstable on subordersellergstdetailstable ssg (cost=0.56..8.58 rows=1 width=31) (actual time=0.003..0.003 rows=1 loops=1,285)

  • Index Cond: ((suborderid = subordertable.suborderid) AND (subordertable.parentstoreid = storeid) AND (storeid = 2371830))
45. 7.710 14.135 ↑ 1.0 1 1,285

Hash Right Join (cost=6.80..8.32 rows=1 width=48) (actual time=0.007..0.011 rows=1 loops=1,285)

  • Hash Cond: (statecodemappingtable.id = rs.sellingstateid)
46. 2.570 2.570 ↑ 1.0 37 1,285

Seq Scan on statecodemappingtable (cost=0.00..1.37 rows=37 width=40) (actual time=0.000..0.002 rows=37 loops=1,285)

47. 0.000 3.855 ↑ 1.0 1 1,285

Hash (cost=6.79..6.79 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=1,285)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
48. 3.855 3.855 ↑ 1.0 1 1,285

Index Scan using storesellingstates_pkey on storesellingstates rs (cost=0.43..6.79 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=1,285)

  • Index Cond: (storeid = subordertableforresellers.resellerid)
49. 1.285 1.285 ↑ 1.0 1 1,285

Index Scan using storesellingstates_pkey on storesellingstates ws (cost=0.43..6.79 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=1,285)

  • Index Cond: (storeid = subordertableforresellers.wholesellerid)
50. 2.570 2.570 ↑ 1.0 1 1,285

Index Scan using shipmenttable_pkey on shipmenttable (cost=0.43..8.45 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=1,285)

  • Index Cond: (subordertable.subordershipmentid = shipmentid)
Planning time : 14.880 ms
Execution time : 68,337.755 ms