explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8Xwt : Optimization for: plan #HNCc

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 25,814.913 25,814.913 ↓ 2.0 2,852,659 1

CTE Scan on union_agg (cost=1,565,047.87..1,593,575.63 rows=1,426,388 width=56) (actual time=20,487.051..25,814.913 rows=2,852,659 loops=1)

2.          

CTE data_naps

3. 114.607 8,528.688 ↓ 125.3 25,062 1

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

  • Group Key: subordertable.storeid
4. 1,319.526 8,414.081 ↑ 6.4 162,691 1

GroupAggregate (cost=643,349.13..719,638.56 rows=1,048,776 width=28) (actual time=6,291.144..8,414.081 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)::numeric END) = ANY ('{1,2,2.5,3}'::numeric[]))
  • Rows Removed by Filter: 2165940
5. 3,292.947 7,094.555 ↑ 1.0 2,689,415 1

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

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

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

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

Hash Left Join (cost=15,454.57..230,112.57 rows=2,691,457 width=32) (actual time=165.513..1,783.678 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. 522.654 522.654 ↓ 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.010..522.654 rows=3,064,856 loops=1)

9. 98.990 164.898 ↑ 1.0 450,603 1

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

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

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

11. 389.759 798.506 ↑ 1.0 1,789,299 1

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

  • Buckets: 2097152 Batches: 1 Memory Usage: 99760kB
12. 408.747 408.747 ↑ 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.006..408.747 rows=1,789,299 loops=1)

13.          

CTE data_payment_close

14. 922.067 12,019.557 ↓ 911.4 182,270 1

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

  • Group Key: subordertable_1.storeid
15. 3,648.509 11,097.490 ↓ 2.0 2,090,259 1

HashAggregate (cost=647,243.08..664,285.69 rows=1,048,776 width=47) (actual time=9,766.326..11,097.490 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,352.253 7,448.981 ↑ 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,346.022..7,448.981 rows=2,689,415 loops=1)

  • Hash Cond: (subordertable_1.subordershipmentid = shipmenttable_1.shipmentid)
17. 2,273.764 5,213.463 ↑ 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,460.447..5,213.463 rows=2,689,415 loops=1)

  • Hash Cond: ((payout.suborderid = subordertable_1.suborderid) AND (payout.storeid = subordertable_1.storeid))
18. 484.051 484.051 ↑ 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.006..484.051 rows=2,808,081 loops=1)

19. 713.435 2,455.648 ↑ 1.0 2,689,415 1

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

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

Hash Left Join (cost=15,454.57..230,112.57 rows=2,691,457 width=46) (actual time=161.946..1,742.213 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. 497.684 497.684 ↓ 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.010..497.684 rows=3,064,856 loops=1)

22. 96.885 161.319 ↑ 1.0 450,603 1

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

  • Buckets: 524288 Batches: 1 Memory Usage: 25219kB
23. 64.434 64.434 ↑ 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.006..64.434 rows=450,603 loops=1)

24. 440.222 883.265 ↑ 1.0 1,789,299 1

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

  • Buckets: 2097152 Batches: 1 Memory Usage: 117582kB
25. 443.043 443.043 ↑ 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.006..443.043 rows=1,789,299 loops=1)

26.          

CTE combined_stores

27. 44.653 20,651.400 ↓ 911.4 182,270 1

Hash Left Join (cost=6.50..18.00 rows=200 width=56) (actual time=20,487.045..20,651.400 rows=182,270 loops=1)

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

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

29. 3.548 8,540.797 ↓ 125.3 25,062 1

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

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

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

31.          

CTE rest_stores

32. 1,427.503 1,454.928 ↓ 1.9 2,670,389 1

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

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

SubPlan (forSeq Scan)

34. 27.425 27.425 ↓ 911.4 182,270 1

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

35.          

CTE union_agg

36. 206.344 24,144.575 ↓ 2.0 2,852,659 1

Append (cost=0.00..42,791.64 rows=1,426,388 width=32) (actual time=20,487.048..24,144.575 rows=2,852,659 loops=1)

37. 20,702.488 20,702.488 ↓ 911.4 182,270 1

CTE Scan on combined_stores combined_stores_1 (cost=0.00..4.00 rows=200 width=56) (actual time=20,487.047..20,702.488 rows=182,270 loops=1)

38. 793.607 3,235.743 ↓ 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=68.939..3,235.743 rows=2,670,389 loops=1)

39. 2,442.136 2,442.136 ↓ 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=68.931..2,442.136 rows=2,670,389 loops=1)

Planning time : 1.366 ms
Execution time : 26,036.524 ms