explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZDXs

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 1,351.051 ↓ 1.0 23,936 1

Gather Merge (cost=473,997.71..476,692.66 rows=23,098 width=449) (actual time=1,268.853..1,351.051 rows=23,936 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 25.374 3,788.604 ↑ 1.4 7,979 3

Sort (cost=472,997.68..473,026.56 rows=11,549 width=449) (actual time=1,262.128..1,262.868 rows=7,979 loops=3)

  • Sort Key: ((subordertable.time_stamp + '05:30:00'::interval)) DESC
  • Sort Method: quicksort Memory: 4625kB
  • Worker 0: Sort Method: quicksort Memory: 4205kB
  • Worker 1: Sort Method: quicksort Memory: 4191kB
3. 35.406 3,763.230 ↑ 1.4 7,979 3

Parallel Hash Left Join (cost=279,431.01..472,218.39 rows=11,549 width=449) (actual time=811.512..1,254.410 rows=7,979 loops=3)

  • Hash Cond: (pd.invoiceid = sellerpaymentrecordtable.invoiceid)
4. 15.030 3,200.043 ↑ 1.4 7,979 3

Parallel Hash Left Join (cost=249,327.43..441,795.16 rows=11,549 width=285) (actual time=633.297..1,066.681 rows=7,979 loops=3)

  • Hash Cond: ((subordertable.parentstoreid = pd.storeid) AND (subordertable.suborderid = pd.suborderid))
5. 24.592 2,377.317 ↑ 1.4 7,979 3

Nested Loop Left Join (cost=160,415.84..352,822.93 rows=11,549 width=260) (actual time=363.927..792.439 rows=7,979 loops=3)

6. 34.154 2,256.981 ↑ 1.4 7,979 3

Nested Loop Left Join (cost=160,415.41..258,220.62 rows=11,549 width=264) (actual time=363.881..752.327 rows=7,979 loops=3)

7. 535.269 1,959.531 ↑ 1.4 7,979 3

Parallel Hash Join (cost=160,407.23..240,556.91 rows=11,549 width=232) (actual time=363.809..653.177 rows=7,979 loops=3)

  • Hash Cond: (subordertablewithouttax.suborderid = subordertable.suborderid)
8. 545.283 545.283 ↑ 1.2 1,493,249 3

Parallel Seq Scan on subordertablewithouttax (cost=0.00..73,102.68 rows=1,866,368 width=26) (actual time=0.010..181.761 rows=1,493,249 loops=3)

9. 15.459 878.979 ↑ 1.5 7,979 3

Parallel Hash (cost=160,262.35..160,262.35 rows=11,590 width=222) (actual time=292.993..292.993 rows=7,979 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 6304kB
10. 9.672 863.520 ↑ 1.5 7,979 3

Merge Join (cost=160,079.39..160,262.35 rows=11,590 width=222) (actual time=283.514..287.840 rows=7,979 loops=3)

  • Merge Cond: (subordertable.suborderstatusvalue = convertortable.suborderstatusvalue)
11. 15.363 852.516 ↓ 4.4 7,979 3

Sort (cost=159,991.22..159,995.78 rows=1,825 width=198) (actual time=283.460..284.172 rows=7,979 loops=3)

  • Sort Key: subordertable.suborderstatusvalue
  • Sort Method: quicksort Memory: 2882kB
  • Worker 0: Sort Method: quicksort Memory: 2311kB
  • Worker 1: Sort Method: quicksort Memory: 2312kB
12. 3.479 837.153 ↓ 4.4 7,979 3

Nested Loop Left Join (cost=20,426.67..159,892.37 rows=1,825 width=198) (actual time=95.308..279.051 rows=7,979 loops=3)

13. 23.186 785.802 ↓ 4.4 7,979 3

Nested Loop (cost=20,426.24..144,993.02 rows=1,825 width=198) (actual time=95.276..261.934 rows=7,979 loops=3)

14. 17.381 714.744 ↓ 4.4 7,979 3

Nested Loop Left Join (cost=20,425.81..130,089.08 rows=1,825 width=184) (actual time=95.239..238.248 rows=7,979 loops=3)

15. 0.371 649.491 ↓ 4.4 7,979 3

Nested Loop Left Join (cost=20,425.39..115,196.09 rows=1,825 width=158) (actual time=95.210..216.497 rows=7,979 loops=3)

16. 18.732 601.248 ↓ 4.4 7,979 3

Nested Loop (cost=20,424.96..100,302.25 rows=1,825 width=152) (actual time=95.176..200.416 rows=7,979 loops=3)

17. 18.902 510.708 ↓ 4.4 7,979 3

Nested Loop (cost=20,424.39..84,683.12 rows=1,825 width=123) (actual time=95.099..170.236 rows=7,979 loops=3)

18. 280.136 372.126 ↓ 2.2 7,979 3

Parallel Bitmap Heap Scan on subordertable (cost=20,423.96..55,124.73 rows=3,612 width=89) (actual time=95.049..124.042 rows=7,979 loops=3)

  • Recheck Cond: ((parentstoreid = 2167498) AND (date((time_stamp + '05:30:00'::interval)) >= '2019-07-05'::date) AND (date((time_stamp + '05:30:00'::interval)) <= '2019-09-05'::date))
  • Filter: (suborderstatusvalue = ANY ('{3,4,5,6,7,8,9}'::bigint[]))
  • Rows Removed by Filter: 511
  • Heap Blocks: exact=6143
19. 2.705 91.990 ↓ 0.0 0 1

BitmapAnd (cost=20,423.96..20,423.96 rows=10,247 width=0) (actual time=91.990..91.990 rows=0 loops=1)

20. 9.025 9.025 ↓ 1.0 50,484 1

Bitmap Index Scan on idx_parentstoreid_subordertable (cost=0.00..915.28 rows=49,447 width=0) (actual time=9.025..9.025 rows=50,484 loops=1)

  • Index Cond: (parentstoreid = 2167498)
21. 80.260 80.260 ↑ 1.0 923,720 1

Bitmap Index Scan on idx_date_subordertable (cost=0.00..19,504.09 rows=931,566 width=0) (actual time=80.260..80.260 rows=923,720 loops=1)

  • Index Cond: ((date((time_stamp + '05:30:00'::interval)) >= '2019-07-05'::date) AND (date((time_stamp + '05:30:00'::interval)) <= '2019-09-05'::date))
22. 119.680 119.680 ↑ 1.0 1 23,936

Index Scan using idx_suborderid_subordertableforresellers on subordertableforresellers (cost=0.43..8.17 rows=1 width=34) (actual time=0.005..0.005 rows=1 loops=23,936)

  • Index Cond: (suborderid = subordertable.suborderid)
23. 71.808 71.808 ↑ 1.0 1 23,936

Index Scan using producttable_pkey on producttable (cost=0.57..8.56 rows=1 width=37) (actual time=0.003..0.003 rows=1 loops=23,936)

  • Index Cond: (productid = subordertable.productid)
24. 47.872 47.872 ↓ 0.0 0 23,936

Index Scan using productskudetails_productvariantid on productskudetails (cost=0.42..8.16 rows=1 width=22) (actual time=0.002..0.002 rows=0 loops=23,936)

  • Index Cond: (subordertable.productvariantid = productvariantid)
25. 47.872 47.872 ↓ 0.0 0 23,936

Index Scan using subordermetadetails_pkey on subordermetadetails (cost=0.42..8.16 rows=1 width=34) (actual time=0.002..0.002 rows=0 loops=23,936)

  • Index Cond: (subordertable.suborderid = suborderid)
26. 47.872 47.872 ↑ 1.0 1 23,936

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

  • Index Cond: (storeid = subordertableforresellers.resellerid)
27. 47.872 47.872 ↑ 1.0 1 23,936

Index Scan using storesellingstates_pkey on storesellingstates ws (cost=0.43..8.16 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=23,936)

  • Index Cond: (storeid = subordertableforresellers.wholesellerid)
28. 1.269 1.332 ↓ 6.2 7,866 3

Sort (cost=88.17..91.35 rows=1,270 width=36) (actual time=0.048..0.444 rows=7,866 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
29. 0.063 0.063 ↑ 141.1 9 3

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

30. 119.680 263.296 ↑ 1.0 1 23,936

Hash Right Join (cost=8.18..9.70 rows=1 width=48) (actual time=0.008..0.011 rows=1 loops=23,936)

  • Hash Cond: (statecodemappingtable.id = rs.sellingstateid)
31. 71.808 71.808 ↑ 1.0 37 23,936

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

32. 23.936 71.808 ↑ 1.0 1 23,936

Hash (cost=8.16..8.16 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=23,936)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
33. 47.872 47.872 ↑ 1.0 1 23,936

Index Scan using storesellingstates_pkey on storesellingstates rs (cost=0.43..8.16 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=23,936)

  • Index Cond: (storeid = subordertableforresellers.resellerid)
34. 95.744 95.744 ↑ 1.0 1 23,936

Index Scan using shipmenttable_pkey on shipmenttable (cost=0.43..8.19 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=23,936)

  • Index Cond: (subordertable.subordershipmentid = shipmentid)
35. 16.512 807.696 ↑ 1.3 12,884 3

Parallel Hash (cost=88,664.24..88,664.24 rows=16,490 width=49) (actual time=269.232..269.232 rows=12,884 loops=3)

  • Buckets: 65536 Batches: 1 Memory Usage: 4032kB
36. 791.184 791.184 ↑ 1.3 12,884 3

Parallel Seq Scan on payoutdetailstable pd (cost=0.00..88,664.24 rows=16,490 width=49) (actual time=44.569..263.728 rows=12,884 loops=3)

  • Filter: (storeid = 2167498)
  • Rows Removed by Filter: 1480726
37. 190.431 527.781 ↑ 1.3 213,325 3

Parallel Hash (cost=26,749.38..26,749.38 rows=268,336 width=15) (actual time=175.927..175.927 rows=213,325 loops=3)

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

Parallel Seq Scan on sellerpaymentrecordtable (cost=0.00..26,749.38 rows=268,336 width=15) (actual time=0.016..112.450 rows=213,325 loops=3)

  • Filter: success
  • Rows Removed by Filter: 403186
Planning time : 5.331 ms
Execution time : 1,352.129 ms