explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZDXs

Settings
# exclusive inclusive rows x rows loops node
1. 88.183 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. 8.458 1,262.868 ↑ 1.4 7,979 3 / 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: 4,625kB
  • Worker 0: Sort Method: quicksort Memory: 4,205kB
  • Worker 1: Sort Method: quicksort Memory: 4,191kB
3. 11.802 1,254.410 ↑ 1.4 7,979 3 / 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. 5.010 1,066.681 ↑ 1.4 7,979 3 / 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. 8.197 792.439 ↑ 1.4 7,979 3 / 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. 11.385 752.327 ↑ 1.4 7,979 3 / 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. 178.423 653.177 ↑ 1.4 7,979 3 / 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. 181.761 181.761 ↑ 1.2 1,493,249 3 / 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. 5.153 292.993 ↑ 1.5 7,979 3 / 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: 32,768 Batches: 1 Memory Usage: 6,304kB
10. 3.224 287.840 ↑ 1.5 7,979 3 / 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. 5.121 284.172 ↓ 4.4 7,979 3 / 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: 2,882kB
  • Worker 0: Sort Method: quicksort Memory: 2,311kB
  • Worker 1: Sort Method: quicksort Memory: 2,312kB
12. 1.160 279.051 ↓ 4.4 7,979 3 / 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. 7.729 261.934 ↓ 4.4 7,979 3 / 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. 5.794 238.248 ↓ 4.4 7,979 3 / 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.124 216.497 ↓ 4.4 7,979 3 / 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. 6.244 200.416 ↓ 4.4 7,979 3 / 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. 6.301 170.236 ↓ 4.4 7,979 3 / 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. 93.379 124.042 ↓ 2.2 7,979 3 / 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 = 2,167,498) 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=6,143
19. 0.902 30.663 ↓ 0.0 0 1 / 3

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

20. 3.008 3.008 ↓ 1.0 50,484 1 / 3

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 = 2,167,498)
21. 26.753 26.753 ↑ 1.0 923,720 1 / 3

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. 39.893 39.893 ↑ 1.0 1 23,936 / 3

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. 23.936 23.936 ↑ 1.0 1 23,936 / 3

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. 15.957 15.957 ↓ 0.0 0 23,936 / 3

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. 15.957 15.957 ↓ 0.0 0 23,936 / 3

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. 15.957 15.957 ↑ 1.0 1 23,936 / 3

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. 15.957 15.957 ↑ 1.0 1 23,936 / 3

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. 0.423 0.444 ↓ 6.2 7,866 3 / 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.021 0.021 ↑ 141.1 9 3 / 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. 39.893 87.765 ↑ 1.0 1 23,936 / 3

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. 23.936 23.936 ↑ 1.0 37 23,936 / 3

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. 7.979 23.936 ↑ 1.0 1 23,936 / 3

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
33. 15.957 15.957 ↑ 1.0 1 23,936 / 3

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. 31.915 31.915 ↑ 1.0 1 23,936 / 3

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. 5.504 269.232 ↑ 1.3 12,884 3 / 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: 65,536 Batches: 1 Memory Usage: 4,032kB
36. 263.728 263.728 ↑ 1.3 12,884 3 / 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 = 2,167,498)
  • Rows Removed by Filter: 1,480,726
37. 63.477 175.927 ↑ 1.3 213,325 3 / 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: 1,048,576 Batches: 1 Memory Usage: 45,024kB
38. 112.450 112.450 ↑ 1.3 213,325 3 / 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: 403,186
Planning time : 5.331 ms
Execution time : 1,352.129 ms