explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HNCc

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 200.857 22,951.254 ↓ 2.0 2,852,659 1

Append (cost=1,522,256.23..1,565,047.87 rows=1,426,388 width=32) (actual time=19,592.233..22,951.254 rows=2,852,659 loops=1)

2.          

CTE data_naps

3. 111.221 8,192.399 ↓ 125.3 25,062 1

HashAggregate (cost=753,723.78..753,725.78 rows=200 width=40) (actual time=8,184.708..8,192.399 rows=25,062 loops=1)

  • Group Key: subordertable.storeid
4. 1,342.511 8,081.178 ↑ 6.4 162,691 1

GroupAggregate (cost=643,349.13..719,638.56 rows=1,048,776 width=28) (actual time=5,956.644..8,081.178 rows=162,691 loops=1)

  • Group Key: subordertable.orderid, subordertable.storeid
  • Filter: (min(CASE WHEN ((shipmenttable.pickupdate IS NOT NULL) AND (subordertable.suborderstatusvalue = 2)) THEN 2.5 ELSE (subordertable.suborderstatusvalue)::nu
  • Rows Removed by Filter: 2165940
5. 3,128.888 6,738.667 ↑ 1.0 2,689,415 1

Sort (cost=643,349.13..650,077.78 rows=2,691,457 width=28) (actual time=5,956.618..6,738.667 rows=2,689,415 loops=1)

  • Sort Key: subordertable.orderid, subordertable.storeid
  • Sort Method: external merge Disk: 104096kB
6. 1,136.567 3,609.779 ↑ 1.0 2,689,415 1

Hash Left Join (cost=110,382.08..355,902.12 rows=2,691,457 width=28) (actual time=950.743..3,609.779 rows=2,689,415 loops=1)

  • Hash Cond: (subordertable.subordershipmentid = shipmenttable.shipmentid)
7. 1,039.308 1,694.375 ↑ 1.0 2,689,415 1

Hash Left Join (cost=15,454.57..230,112.57 rows=2,691,457 width=32) (actual time=163.131..1,694.375 rows=2,689,415 loops=1)

  • Hash Cond: (subordertable.suborderpaymentid = paymenttable.paymentid)
  • Filter: ((subordertable.subordermodeofpayment <> 'onlinepayment'::text) OR (paymenttable.paymentgatewaystatus = 'success'::text))
  • Rows Removed by Filter: 375441
8. 494.278 494.278 ↓ 1.0 3,064,856 1

Seq Scan on subordertable (cost=0.00..194,482.65 rows=3,057,565 width=46) (actual time=0.009..494.278 rows=3,064,856 loops=1)

9. 99.592 160.789 ↑ 1.0 450,603 1

Hash (cost=9,822.03..9,822.03 rows=450,603 width=16) (actual time=160.789..160.789 rows=450,603 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 25219kB
10. 61.197 61.197 ↑ 1.0 450,603 1

Seq Scan on paymenttable (cost=0.00..9,822.03 rows=450,603 width=16) (actual time=0.006..61.197 rows=450,603 loops=1)

11. 408.605 778.837 ↑ 1.0 1,789,299 1

Hash (cost=72,556.67..72,556.67 rows=1,789,667 width=12) (actual time=778.837..778.837 rows=1,789,299 loops=1)

  • Buckets: 2097152 Batches: 1 Memory Usage: 99760kB
12. 370.232 370.232 ↑ 1.0 1,789,299 1

Seq Scan on shipmenttable (cost=0.00..72,556.67 rows=1,789,667 width=12) (actual time=0.007..370.232 rows=1,789,299 loops=1)

13.          

CTE data_payment_close

14. 859.341 11,455.587 ↓ 911.4 182,270 1

HashAggregate (cost=682,639.27..682,641.27 rows=200 width=9) (actual time=11,388.001..11,455.587 rows=182,270 loops=1)

  • Group Key: subordertable_1.storeid
15. 3,396.305 10,596.246 ↓ 2.0 2,090,259 1

HashAggregate (cost=647,243.08..664,285.69 rows=1,048,776 width=47) (actual time=9,396.580..10,596.246 rows=2,090,259 loops=1)

  • Group Key: subordertable_1.orderid, subordertable_1.storeid
  • Filter: (min(CASE WHEN ((shipmenttable_1.pickupdate IS NOT NULL) AND (subordertable_1.suborderstatusvalue = 2)) THEN 2.5 ELSE (subordertable_1.suborderstatusvalue)::numeric END) = ANY ('{4,5,6,7,8}'::numeric[]))
  • Rows Removed by Filter: 238372
16. 1,273.019 7,199.941 ↑ 1.0 2,689,415 1

Hash Left Join (cost=389,067.93..579,956.66 rows=2,691,457 width=47) (actual time=3,306.879..7,199.941 rows=2,689,415 loops=1)

  • Hash Cond: (subordertable_1.subordershipmentid = shipmenttable_1.shipmentid)
17. 2,161.219 5,023.469 ↑ 1.0 2,689,415 1

Hash Right Join (cost=294,140.42..454,167.10 rows=2,691,457 width=39) (actual time=2,394.315..5,023.469 rows=2,689,415 loops=1)

  • Hash Cond: ((payout.suborderid = subordertable_1.suborderid) AND (payout.storeid = subordertable_1.storeid))
18. 472.771 472.771 ↑ 1.0 2,808,081 1

Seq Scan on payoutdetailstable payout (cost=0.00..68,336.81 rows=2,808,081 width=17) (actual time=0.005..472.771 rows=2,808,081 loops=1)

19. 720.240 2,389.479 ↑ 1.0 2,689,415 1

Hash (cost=230,112.57..230,112.57 rows=2,691,457 width=46) (actual time=2,389.479..2,389.479 rows=2,689,415 loops=1)

  • Buckets: 4194304 Batches: 2 Memory Usage: 137072kB
20. 1,036.960 1,669.239 ↑ 1.0 2,689,415 1

Hash Left Join (cost=15,454.57..230,112.57 rows=2,691,457 width=46) (actual time=159.471..1,669.239 rows=2,689,415 loops=1)

  • Hash Cond: (subordertable_1.suborderpaymentid = paymenttable_1.paymentid)
  • Filter: ((subordertable_1.subordermodeofpayment <> 'onlinepayment'::text) OR (paymenttable_1.paymentgatewaystatus = 'success'::text))
  • Rows Removed by Filter: 375441
21. 473.420 473.420 ↓ 1.0 3,064,856 1

Seq Scan on subordertable subordertable_1 (cost=0.00..194,482.65 rows=3,057,565 width=54) (actual time=0.009..473.420 rows=3,064,856 loops=1)

22. 93.452 158.859 ↑ 1.0 450,603 1

Hash (cost=9,822.03..9,822.03 rows=450,603 width=16) (actual time=158.859..158.859 rows=450,603 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 25219kB
23. 65.407 65.407 ↑ 1.0 450,603 1

Seq Scan on paymenttable paymenttable_1 (cost=0.00..9,822.03 rows=450,603 width=16) (actual time=0.005..65.407 rows=450,603 loops=1)

24. 475.704 903.453 ↑ 1.0 1,789,299 1

Hash (cost=72,556.67..72,556.67 rows=1,789,667 width=24) (actual time=903.453..903.453 rows=1,789,299 loops=1)

  • Buckets: 2097152 Batches: 1 Memory Usage: 117582kB
25. 427.749 427.749 ↑ 1.0 1,789,299 1

Seq Scan on shipmenttable shipmenttable_1 (cost=0.00..72,556.67 rows=1,789,667 width=24) (actual time=0.007..427.749 rows=1,789,299 loops=1)

26.          

CTE combined_stores

27. 41.609 19,746.113 ↓ 911.4 182,270 1

Hash Left Join (cost=6.50..18.00 rows=200 width=56) (actual time=19,592.230..19,746.113 rows=182,270 loops=1)

  • Hash Cond: (data_payment_close.storeid = data_naps.storeid)
28. 11,500.305 11,500.305 ↓ 911.4 182,270 1

CTE Scan on data_payment_close (cost=0.00..4.00 rows=200 width=24) (actual time=11,388.003..11,500.305 rows=182,270 loops=1)

29. 3.553 8,204.199 ↓ 125.3 25,062 1

Hash (cost=4.00..4.00 rows=200 width=40) (actual time=8,204.199..8,204.199 rows=25,062 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2019kB
30. 8,200.646 8,200.646 ↓ 125.3 25,062 1

CTE Scan on data_naps (cost=0.00..4.00 rows=200 width=40) (actual time=8,184.711..8,200.646 rows=25,062 loops=1)

31.          

CTE rest_stores

32. 1,283.964 1,304.626 ↓ 1.9 2,670,389 1

Seq Scan on storetable (cost=4.50..85,871.19 rows=1,426,188 width=8) (actual time=61.192..1,304.626 rows=2,670,389 loops=1)

  • Filter: (NOT (hashed SubPlan 4))
  • Rows Removed by Filter: 182270
33.          

SubPlan (forSeq Scan)

34. 20.662 20.662 ↓ 911.4 182,270 1

CTE Scan on combined_stores combined_stores_1 (cost=0.00..4.00 rows=200 width=8) (actual time=0.002..20.662 rows=182,270 loops=1)

35. 19,795.796 19,795.796 ↓ 911.4 182,270 1

CTE Scan on combined_stores (cost=0.00..4.00 rows=200 width=56) (actual time=19,592.233..19,795.796 rows=182,270 loops=1)

36. 732.732 2,954.601 ↓ 1.9 2,670,389 1

Subquery Scan on *SELECT* 2 (cost=0.00..42,785.64 rows=1,426,188 width=32) (actual time=61.202..2,954.601 rows=2,670,389 loops=1)

37. 2,221.869 2,221.869 ↓ 1.9 2,670,389 1

CTE Scan on rest_stores (cost=0.00..28,523.76 rows=1,426,188 width=32) (actual time=61.195..2,221.869 rows=2,670,389 loops=1)

Planning time : 1.422 ms
Execution time : 23,125.633 ms