explain.depesz.com

PostgreSQL's explain analyze made readable

Result: COLB

Settings
# exclusive inclusive rows x rows loops node
1. 1.240 69,463.142 ↓ 20.7 1,284 1

Sort (cost=755,786.93..755,787.09 rows=62 width=367) (actual time=69,462.974..69,463.142 rows=1,284 loops=1)

  • Sort Key: ((subordertable.time_stamp + '05:30:00'::interval)) DESC
  • Sort Method: quicksort Memory: 699kB
2. 0.885 69,461.902 ↓ 20.7 1,284 1

Nested Loop Left Join (cost=753,485.56..755,785.08 rows=62 width=367) (actual time=69,178.804..69,461.902 rows=1,284 loops=1)

3. 0.858 69,457.165 ↓ 20.7 1,284 1

Nested Loop Left Join (cost=753,485.13..755,260.25 rows=62 width=334) (actual time=69,178.777..69,457.165 rows=1,284 loops=1)

4. 2.120 69,455.023 ↓ 20.7 1,284 1

Nested Loop Left Join (cost=753,484.69..754,787.03 rows=62 width=334) (actual time=69,178.767..69,455.023 rows=1,284 loops=1)

5. 1.462 69,437.495 ↓ 20.7 1,284 1

Hash Right Join (cost=753,477.05..754,684.60 rows=62 width=302) (actual time=69,178.720..69,437.495 rows=1,284 loops=1)

  • Hash Cond: ((pd.storeid = subordertable.parentstoreid) AND (pd.suborderid = subordertable.suborderid))
6. 306.192 69,294.654 ↓ 3.5 1,192 1

Subquery Scan on pd (cost=745,803.11..747,008.08 rows=344 width=57) (actual time=67,243.531..69,294.654 rows=1,192 loops=1)

  • Filter: (pd.storeid = 2371830)
  • Rows Removed by Filter: 3566035
7. 836.743 68,988.462 ↓ 51.8 3,567,227 1

Unique (cost=745,803.11..746,147.39 rows=68,855 width=65) (actual time=66,873.044..68,988.462 rows=3,567,227 loops=1)

8. 6,354.571 68,151.719 ↓ 135.3 9,318,392 1

Sort (cost=745,803.11..745,975.25 rows=68,855 width=65) (actual time=66,873.042..68,151.719 rows=9,318,392 loops=1)

  • Sort Key: pd_1.suborderid
  • Sort Method: external merge Disk: 927576kB
9. 3,021.326 61,797.148 ↓ 135.3 9,318,392 1

Hash Left Join (cost=116,271.86..740,270.17 rows=68,855 width=65) (actual time=6,933.833..61,797.148 rows=9,318,392 loops=1)

  • Hash Cond: (pd_1.invoiceid = stcs.invoiceid)
10. 7,287.895 58,757.657 ↓ 135.3 9,318,392 1

Nested Loop (cost=114,468.63..737,595.27 rows=68,855 width=59) (actual time=6,915.601..58,757.657 rows=9,318,392 loops=1)

11. 0.000 40,561.540 ↓ 127.3 10,908,222 1

Nested Loop (cost=114,468.20..697,015.77 rows=85,697 width=67) (actual time=6,912.761..40,561.540 rows=10,908,222 loops=1)

12. 4,069.499 18,625.056 ↓ 45.4 11,078,171 1

Merge Join (cost=114,467.77..578,807.09 rows=243,889 width=52) (actual time=6,912.738..18,625.056 rows=11,078,171 loops=1)

  • Merge Cond: ((unnest(sellerinvoicemappingtable.payoutids)) = pd_1.id)
13. 2,865.988 11,322.571 ↓ 45.4 11,078,311 1

Unique (cost=114,467.34..116,906.23 rows=243,889 width=22) (actual time=6,845.842..11,322.571 rows=11,078,311 loops=1)

14. 6,927.001 8,456.583 ↓ 45.4 11,078,311 1

Sort (cost=114,467.34..115,077.06 rows=243,889 width=22) (actual time=6,845.840..8,456.583 rows=11,078,311 loops=1)

  • Sort Key: (unnest(sellerinvoicemappingtable.payoutids)), sellerinvoicemappingtable.invoiceid, sellershop101serviceinvoicetable.igst
  • Sort Method: external merge Disk: 355848kB
15. 1,036.151 1,529.582 ↓ 45.4 11,078,311 1

Gather (cost=6,582.31..92,644.32 rows=243,889 width=22) (actual time=43.650..1,529.582 rows=11,078,311 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
16. 386.849 493.431 ↑ 2.8 3,692,770 3 / 3

ProjectSet (cost=5,582.31..67,255.42 rows=10,162,000 width=22) (actual time=41.119..493.431 rows=3,692,770 loops=3)

17. 38.454 106.582 ↑ 1.3 80,053 3 / 3

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

  • Hash Cond: (sellerinvoicemappingtable.invoiceid = sellershop101serviceinvoicetable.invoiceid)
18. 27.565 27.565 ↑ 1.3 80,053 3 / 3

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

19. 21.879 40.563 ↑ 1.2 80,053 3 / 3

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

  • Buckets: 262144 Batches: 1 Memory Usage: 13376kB
20. 18.684 18.684 ↑ 1.2 80,053 3 / 3

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

21. 3,232.986 3,232.986 ↓ 1.0 8,811,329 1

Index Scan using payoutdetailstable_new_pkey on payoutdetailstable pd_1 (cost=0.43..434,938.47 rows=8,589,956 width=54) (actual time=0.042..3,232.986 rows=8,811,329 loops=1)

22. 22,156.342 22,156.342 ↑ 1.0 1 11,078,171

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,171)

  • Index Cond: ((invoiceid = pd_1.invoiceid) AND (success = true))
  • Filter: success
23. 10,908.222 10,908.222 ↑ 1.0 1 10,908,222

Index Scan using sellerinvoiceinfotable_pkey on sellerinvoiceinfotable si (cost=0.43..0.47 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=10,908,222)

  • Index Cond: (invoiceid = pd_1.invoiceid)
  • Filter: reselling
  • Rows Removed by Filter: 0
24. 8.216 18.165 ↑ 1.0 54,866 1

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

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

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

26. 1.379 141.379 ↓ 20.7 1,284 1

Hash (cost=7,673.01..7,673.01 rows=62 width=269) (actual time=141.379..141.379 rows=1,284 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 400kB
27. 0.510 140.000 ↓ 20.7 1,284 1

Nested Loop Left Join (cost=4.15..7,673.01 rows=62 width=269) (actual time=0.251..140.000 rows=1,284 loops=1)

28. 0.301 113.810 ↓ 20.7 1,284 1

Nested Loop (cost=3.59..7,140.86 rows=62 width=254) (actual time=0.226..113.810 rows=1,284 loops=1)

  • Join Filter: (subordertable.suborderid = subordertablewithouttax.suborderid)
29. 0.273 82.693 ↓ 19.8 1,284 1

Nested Loop Left Join (cost=3.16..6,643.90 rows=65 width=243) (actual time=0.204..82.693 rows=1,284 loops=1)

30. 0.290 79.852 ↓ 19.8 1,284 1

Nested Loop (cost=2.73..6,148.20 rows=65 width=237) (actual time=0.192..79.852 rows=1,284 loops=1)

31. 0.039 0.039 ↑ 1.0 1 1

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

  • Index Cond: (storeid = 2371830)
32. 1.025 79.523 ↓ 19.8 1,284 1

Nested Loop (cost=2.29..6,139.10 rows=65 width=221) (actual time=0.152..79.523 rows=1,284 loops=1)

33. 0.118 68.226 ↓ 19.8 1,284 1

Nested Loop Left Join (cost=1.86..5,642.96 rows=65 width=207) (actual time=0.141..68.226 rows=1,284 loops=1)

34. 0.713 62.972 ↓ 19.8 1,284 1

Nested Loop (cost=1.44..5,096.49 rows=65 width=180) (actual time=0.130..62.972 rows=1,284 loops=1)

35. 1.298 39.147 ↓ 19.8 1,284 1

Nested Loop (cost=0.86..4,538.30 rows=65 width=147) (actual time=0.110..39.147 rows=1,284 loops=1)

  • Join Filter: (subordertable.suborderstatusvalue = convertortable.suborderstatusvalue)
  • Rows Removed by Join Filter: 14124
36. 0.505 36.565 ↓ 19.8 1,284 1

Nested Loop (cost=0.86..4,525.45 rows=65 width=123) (actual time=0.096..36.565 rows=1,284 loops=1)

37. 6.528 6.528 ↓ 12.1 1,284 1

Index Scan using idx_parentstoreid_subordertable on subordertable (cost=0.43..3,628.69 rows=106 width=81) (actual time=0.072..6.528 rows=1,284 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: 1879
38. 29.532 29.532 ↑ 1.0 1 1,284

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

  • Index Cond: (suborderid = subordertable.suborderid)
39. 1.277 1.284 ↑ 1.0 12 1,284

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

40. 0.007 0.007 ↑ 1.0 12 1

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

41. 23.112 23.112 ↑ 1.0 1 1,284

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

  • Index Cond: (productid = subordertable.productid)
42. 5.136 5.136 ↓ 0.0 0 1,284

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

  • Index Cond: (subordertable.suborderid = suborderid)
43. 10.272 10.272 ↑ 1.0 1 1,284

Index Scan using storetable_pkey on storetable (cost=0.43..7.63 rows=1 width=22) (actual time=0.008..0.008 rows=1 loops=1,284)

  • Index Cond: (storeid = subordertableforresellers.resellerid)
44. 2.568 2.568 ↑ 1.0 1 1,284

Index Scan using productskudetails_productvariantid on productskudetails (cost=0.43..7.63 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=1,284)

  • Index Cond: (productvariantid = subordertableforresellers.productvariantid)
45. 30.816 30.816 ↑ 1.0 1 1,284

Index Scan using subordertablewithouttax_pkey on subordertablewithouttax (cost=0.43..7.63 rows=1 width=27) (actual time=0.024..0.024 rows=1 loops=1,284)

  • Index Cond: (suborderid = subordertableforresellers.suborderid)
46. 25.680 25.680 ↑ 1.0 1 1,284

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

  • Index Cond: ((suborderid = subordertable.suborderid) AND (subordertable.parentstoreid = storeid) AND (storeid = 2371830))
47. 6.420 15.408 ↑ 1.0 1 1,284

Hash Right Join (cost=7.65..9.16 rows=1 width=48) (actual time=0.009..0.012 rows=1 loops=1,284)

  • Hash Cond: (statecodemappingtable.id = rs.sellingstateid)
48. 3.852 3.852 ↑ 1.0 37 1,284

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

49. 0.000 5.136 ↑ 1.0 1 1,284

Hash (cost=7.63..7.63 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=1,284)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
50. 5.136 5.136 ↑ 1.0 1 1,284

Index Scan using storesellingstates_pkey on storesellingstates rs (cost=0.43..7.63 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=1,284)

  • Index Cond: (storeid = subordertableforresellers.resellerid)
51. 1.284 1.284 ↑ 1.0 1 1,284

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

  • Index Cond: (storeid = subordertableforresellers.wholesellerid)
52. 3.852 3.852 ↑ 1.0 1 1,284

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

  • Index Cond: (subordertable.subordershipmentid = shipmentid)
Planning time : 10.455 ms
Execution time : 69,647.537 ms