explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BXnG

Settings
# exclusive inclusive rows x rows loops node
1. 1.338 51,985.537 ↓ 20.7 1,284 1

Sort (cost=717,207.87..717,208.02 rows=62 width=367) (actual time=51,985.371..51,985.537 rows=1,284 loops=1)

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

Nested Loop Left Join (cost=714,611.14..717,206.02 rows=62 width=367) (actual time=51,666.346..51,984.199 rows=1,284 loops=1)

3. 0.998 51,970.863 ↓ 20.7 1,284 1

Nested Loop Left Join (cost=714,610.71..716,681.19 rows=62 width=334) (actual time=51,666.321..51,970.863 rows=1,284 loops=1)

4. 1.776 51,968.581 ↓ 20.7 1,284 1

Nested Loop Left Join (cost=714,610.27..716,207.97 rows=62 width=334) (actual time=51,666.304..51,968.581 rows=1,284 loops=1)

5. 1.555 51,946.261 ↓ 20.7 1,284 1

Hash Right Join (cost=714,602.63..716,105.55 rows=62 width=302) (actual time=51,665.497..51,946.261 rows=1,284 loops=1)

  • Hash Cond: ((pd.storeid = subordertable.parentstoreid) AND (pd.suborderid = subordertable.suborderid))
6. 433.815 51,843.771 ↓ 2.8 1,192 1

Subquery Scan on pd (cost=706,925.46..708,425.16 rows=428 width=57) (actual time=49,413.015..51,843.771 rows=1,192 loops=1)

  • Filter: (pd.storeid = 2371830)
  • Rows Removed by Filter: 4544310
7. 1,082.917 51,409.956 ↓ 53.0 4,545,502 1

Unique (cost=706,925.46..707,353.95 rows=85,697 width=65) (actual time=48,677.481..51,409.956 rows=4,545,502 loops=1)

8. 7,734.184 50,327.039 ↓ 127.3 10,908,222 1

Sort (cost=706,925.46..707,139.70 rows=85,697 width=65) (actual time=48,677.479..50,327.039 rows=10,908,222 loops=1)

  • Sort Key: pd_1.suborderid
  • Sort Method: external merge Disk: 1065032kB
9. 3,156.123 42,592.855 ↓ 127.3 10,908,222 1

Hash Left Join (cost=116,271.43..699,903.90 rows=85,697 width=65) (actual time=6,819.782..42,592.855 rows=10,908,222 loops=1)

  • Hash Cond: (pd_1.invoiceid = stcs.invoiceid)
10. 0.000 39,417.676 ↓ 127.3 10,908,222 1

Nested Loop (cost=114,468.20..697,015.77 rows=85,697 width=59) (actual time=6,800.657..39,417.676 rows=10,908,222 loops=1)

11. 3,945.998 18,546.138 ↓ 45.4 11,078,195 1

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

  • Merge Cond: ((unnest(sellerinvoicemappingtable.payoutids)) = pd_1.id)
12. 2,735.395 11,190.110 ↓ 45.4 11,078,335 1

Unique (cost=114,467.34..116,906.23 rows=243,889 width=22) (actual time=6,738.230..11,190.110 rows=11,078,335 loops=1)

13. 7,014.461 8,454.715 ↓ 45.4 11,078,335 1

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

  • Sort Key: (unnest(sellerinvoicemappingtable.payoutids)), sellerinvoicemappingtable.invoiceid, sellershop101serviceinvoicetable.igst
  • Sort Method: external merge Disk: 355848kB
14. 944.413 1,440.254 ↓ 45.4 11,078,335 1

Gather (cost=6,582.31..92,644.32 rows=243,889 width=22) (actual time=45.994..1,440.254 rows=11,078,335 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
15. 387.472 495.841 ↑ 2.8 3,692,778 3 / 3

ProjectSet (cost=5,582.31..67,255.42 rows=10,162,000 width=22) (actual time=43.345..495.841 rows=3,692,778 loops=3)

16. 37.967 108.369 ↑ 1.3 80,057 3 / 3

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

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

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

18. 21.704 42.790 ↑ 1.2 80,057 3 / 3

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

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

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

20. 3,410.030 3,410.030 ↓ 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,410.030 rows=8,811,329 loops=1)

21. 22,156.390 22,156.390 ↑ 1.0 1 11,078,195

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

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

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3084kB
23. 9.657 9.657 ↑ 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.657 rows=54,866 loops=1)

24. 1.074 100.935 ↓ 20.7 1,284 1

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

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 400kB
25. 0.154 99.861 ↓ 20.7 1,284 1

Nested Loop Left Join (cost=4.15..7,676.24 rows=62 width=269) (actual time=0.788..99.861 rows=1,284 loops=1)

26. 0.473 79.163 ↓ 20.7 1,284 1

Nested Loop (cost=3.59..7,144.09 rows=62 width=254) (actual time=0.759..79.163 rows=1,284 loops=1)

  • Join Filter: (subordertable.suborderid = subordertablewithouttax.suborderid)
27. 1.090 59.430 ↓ 19.8 1,284 1

Nested Loop Left Join (cost=3.16..6,647.14 rows=65 width=243) (actual time=0.738..59.430 rows=1,284 loops=1)

28. 0.245 57.056 ↓ 19.8 1,284 1

Nested Loop (cost=2.73..6,151.45 rows=65 width=237) (actual time=0.720..57.056 rows=1,284 loops=1)

29. 0.533 0.533 ↑ 1.0 1 1

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

  • Index Cond: (storeid = 2371830)
30. 1.055 56.278 ↓ 19.8 1,284 1

Nested Loop (cost=2.29..6,142.35 rows=65 width=221) (actual time=0.185..56.278 rows=1,284 loops=1)

31. 0.267 47.519 ↓ 19.8 1,284 1

Nested Loop Left Join (cost=1.86..5,646.21 rows=65 width=207) (actual time=0.162..47.519 rows=1,284 loops=1)

32. 0.829 42.116 ↓ 19.8 1,284 1

Nested Loop (cost=1.44..5,099.74 rows=65 width=180) (actual time=0.144..42.116 rows=1,284 loops=1)

33. 0.902 27.163 ↓ 19.8 1,284 1

Nested Loop (cost=0.86..4,541.55 rows=65 width=147) (actual time=0.121..27.163 rows=1,284 loops=1)

  • Join Filter: (subordertable.suborderstatusvalue = convertortable.suborderstatusvalue)
  • Rows Removed by Join Filter: 14124
34. 0.894 24.977 ↓ 19.8 1,284 1

Nested Loop (cost=0.86..4,528.70 rows=65 width=123) (actual time=0.108..24.977 rows=1,284 loops=1)

35. 4.823 4.823 ↓ 12.1 1,284 1

Index Scan using idx_parentstoreid_subordertable on subordertable (cost=0.43..3,631.94 rows=106 width=81) (actual time=0.083..4.823 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: 1891
36. 19.260 19.260 ↑ 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.015..0.015 rows=1 loops=1,284)

  • Index Cond: (suborderid = subordertable.suborderid)
37. 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)

38. 0.007 0.007 ↑ 1.0 12 1

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

39. 14.124 14.124 ↑ 1.0 1 1,284

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

  • Index Cond: (productid = subordertable.productid)
40. 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)
41. 7.704 7.704 ↑ 1.0 1 1,284

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

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

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

  • Index Cond: (productvariantid = subordertableforresellers.productvariantid)
43. 19.260 19.260 ↑ 1.0 1 1,284

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

  • Index Cond: (suborderid = subordertableforresellers.suborderid)
44. 20.544 20.544 ↑ 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.016..0.016 rows=1 loops=1,284)

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

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

  • Hash Cond: (statecodemappingtable.id = rs.sellingstateid)
46. 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)

47. 0.000 10.272 ↑ 1.0 1 1,284

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
48. 10.272 10.272 ↑ 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.007..0.008 rows=1 loops=1,284)

  • Index Cond: (storeid = subordertableforresellers.resellerid)
49. 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)
50. 11.556 11.556 ↑ 1.0 1 1,284

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

  • Index Cond: (subordertable.subordershipmentid = shipmentid)
Planning time : 7.300 ms
Execution time : 52,202.001 ms