explain.depesz.com

PostgreSQL's explain analyze made readable

Result: amce

Settings
# exclusive inclusive rows x rows loops node
1. 31.845 8,234.799 ↓ 353.8 70,761 1

WindowAgg (cost=564,205.29..564,208.79 rows=200 width=35) (actual time=8,197.040..8,234.799 rows=70,761 loops=1)

2. 24.579 8,202.954 ↓ 353.8 70,761 1

Sort (cost=564,205.29..564,205.79 rows=200 width=27) (actual time=8,197.031..8,202.954 rows=70,761 loops=1)

  • Sort Key: (count(DISTINCT orderdata.orderid)) DESC
  • Sort Method: quicksort Memory: 8,601kB
3. 11.362 8,178.375 ↓ 353.8 70,761 1

Merge Left Join (cost=545,281.32..564,197.64 rows=200 width=27) (actual time=3,911.446..8,178.375 rows=70,761 loops=1)

  • Merge Cond: (storetable.storeid = cohortactivatedusers.storeid)
  • Filter: ((cohortactivatedusers.storeid IS NULL) OR (cohortactivatedusers.storeid IS NOT NULL) OR (storetable.storeid > 5729) OR (usersegments.storeid IS NOT NULL))
4. 0.000 8,166.920 ↓ 353.8 70,761 1

Nested Loop Left Join (cost=545,280.89..564,072.11 rows=200 width=35) (actual time=3,911.416..8,166.920 rows=70,761 loops=1)

5. 53.914 4,491.581 ↓ 353.8 70,761 1

Nested Loop (cost=545,280.33..547,641.97 rows=200 width=27) (actual time=3,911.404..4,491.581 rows=70,761 loops=1)

6. 57.053 4,296.145 ↓ 353.8 70,761 1

Nested Loop (cost=545,279.89..547,145.65 rows=200 width=24) (actual time=3,911.393..4,296.145 rows=70,761 loops=1)

7. 121.663 4,097.570 ↓ 353.8 70,761 1

GroupAggregate (cost=545,279.46..545,457.65 rows=200 width=16) (actual time=3,911.369..4,097.570 rows=70,761 loops=1)

  • Group Key: orderdata.storeid
8. 149.380 3,975.907 ↓ 14.3 335,710 1

Sort (cost=545,279.46..545,338.19 rows=23,492 width=16) (actual time=3,911.352..3,975.907 rows=335,710 loops=1)

  • Sort Key: orderdata.storeid
  • Sort Method: quicksort Memory: 28,025kB
9. 29.875 3,826.527 ↓ 14.3 335,710 1

Subquery Scan on orderdata (cost=542,281.90..543,573.96 rows=23,492 width=16) (actual time=3,620.633..3,826.527 rows=335,710 loops=1)

10. 420.282 3,796.652 ↓ 14.3 335,710 1

HashAggregate (cost=542,281.90..543,339.04 rows=23,492 width=16) (actual time=3,620.632..3,796.652 rows=335,710 loops=1)

  • Group Key: subordertable.orderid
  • Filter: (sum((subordertableforresellers.variantwholesaleprice * (subordertable.suborderquantity)::numeric)) >= '0'::numeric)
11. 72.489 3,376.370 ↓ 4.9 344,373 1

Hash Left Join (cost=408,825.37..541,400.95 rows=70,476 width=29) (actual time=2,694.786..3,376.370 rows=344,373 loops=1)

  • Hash Cond: (subordertable.suborderpaymentid = paymenttable.paymentid)
  • Filter: ((subordertable.subordermodeofpayment <> 'onlinepayment'::text) OR (paymenttable.paymentgatewaystatus = 'success'::text))
  • Rows Removed by Filter: 33,711
12. 1,273.264 2,972.614 ↓ 4.9 378,084 1

Hash Join (cost=377,876.49..510,249.12 rows=77,315 width=43) (actual time=2,358.655..2,972.614 rows=378,084 loops=1)

  • Hash Cond: (subordertableforresellers.suborderid = subordertable.suborderid)
13. 511.194 511.194 ↓ 1.0 5,586,059 1

Seq Scan on subordertableforresellers (cost=0.00..117,709.29 rows=5,586,029 width=13) (actual time=0.009..511.194 rows=5,586,059 loops=1)

14. 99.836 1,188.156 ↓ 3.2 378,084 1

Hash (cost=376,391.21..376,391.21 rows=118,823 width=46) (actual time=1,188.156..1,188.156 rows=378,084 loops=1)

  • Buckets: 524,288 (originally 131072) Batches: 1 (originally 1) Memory Usage: 30,051kB
15. 506.394 1,088.320 ↓ 3.2 378,084 1

Index Scan using idx_subordertable_timestamp on subordertable (cost=231,898.65..376,391.21 rows=118,823 width=46) (actual time=582.108..1,088.320 rows=378,084 loops=1)

  • Index Cond: (((time_stamp + '05:30:00'::interval) >= '2020-01-20 16:40:00'::timestamp without time zone) AND ((time_stamp + '05:30:00'::interval) <= '2020-02-07 12:00:00'::timestamp without time zone))
  • Filter: ((parentstoreid IS NOT NULL) AND (NOT (hashed SubPlan 1)) AND (suborderstatusvalue <> ALL ('{5,6,7,8,9,10}'::bigint[])))
  • Rows Removed by Filter: 219,607
16.          

SubPlan (for Index Scan)

17. 2.533 581.926 ↓ 0.0 0 1

Gather (cost=1,000.00..231,814.51 rows=33,483 width=8) (actual time=581.847..581.926 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
18. 579.393 579.393 ↓ 0.0 0 3 / 3

Parallel Seq Scan on usersegmentsexcluded (cost=0.00..227,466.21 rows=13,951 width=8) (actual time=579.393..579.393 rows=0 loops=3)

  • Filter: (offerid = 883)
  • Rows Removed by Filter: 6,548,921
19. 208.966 331.267 ↑ 1.0 912,261 1

Hash (cost=19,545.61..19,545.61 rows=912,261 width=16) (actual time=331.267..331.267 rows=912,261 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 50,955kB
20. 122.301 122.301 ↑ 1.0 912,261 1

Seq Scan on paymenttable (cost=0.00..19,545.61 rows=912,261 width=16) (actual time=0.014..122.301 rows=912,261 loops=1)

21. 141.522 141.522 ↑ 1.0 1 70,761

Index Scan using storetable_pkey on storetable (cost=0.43..8.43 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=70,761)

  • Index Cond: (storeid = orderdata.storeid)
22. 141.522 141.522 ↑ 1.0 1 70,761

Index Scan using usertable_pkey on usertable (cost=0.43..2.48 rows=1 width=19) (actual time=0.002..0.002 rows=1 loops=70,761)

  • Index Cond: (userid = storetable.userid)
23. 3,679.572 3,679.572 ↓ 0.0 0 70,761

Index Scan using index_storeid on usersegments (cost=0.56..82.14 rows=1 width=8) (actual time=0.052..0.052 rows=0 loops=70,761)

  • Index Cond: (storetable.storeid = storeid)
  • Filter: (offerid = 883)
  • Rows Removed by Filter: 70
24. 0.093 0.093 ↓ 1.6 96 1

Index Scan using cohortactivatedusers_storeid_key on cohortactivatedusers (cost=0.42..124.87 rows=60 width=8) (actual time=0.012..0.093 rows=96 loops=1)

  • Index Cond: (storeid <= 5,729)
  • Filter: (date(activation_timestamp) < '2020-01-15'::date)
  • Rows Removed by Filter: 1