explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dqfA

Settings
# exclusive inclusive rows x rows loops node
1. 294.794 294.794 ↑ 2.0 1 1

TE Scan on data_naps (cost=314,527.80..314,527.84 rows=2 width=168) (actual time=294.793..294.794 rows=1 loops=1)

2.          

CTE ordersdata

3. 29.962 293.283 ↑ 1.6 29,066 1

Gather (cost=17,913.77..309,996.86 rows=46,337 width=56) (actual time=142.869..293.283 rows=29,066 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 45.184 263.321 ↑ 2.0 9,689 3

Nested Loop Left Join (cost=16,913.77..304,363.16 rows=19,307 width=56) (actual time=139.901..263.321 rows=9,689 loops=3)

5. 8.475 218.133 ↑ 2.0 9,689 3

Parallel Hash Left Join (cost=16,913.33..211,660.79 rows=19,307 width=40) (actual time=139.868..218.133 rows=9,689 loops=3)

  • Hash Cond: (subordertable.suborderpaymentid = paymenttable.paymentid)
  • Filter: ((subordertable.subordermodeofpayment <> 'onlinepayment'::text) OR (paymenttable.paymentgatewaystatus = 'success'::text))
  • Rows Removed by Filter: 623
6. 83.373 98.072 ↑ 2.1 10,311 3

Parallel Bitmap Heap Scan on subordertable (cost=1,446.16..196,137.39 rows=21,420 width=54) (actual time=25.937..98.072 rows=10,311 loops=3)

  • Recheck Cond: ((storeid = 2826859) OR (parentstoreid = 2826859))
  • Filter: (suborderstatusvalue = ANY ('{1,2,3,4,11,12}'::bigint[]))
  • Rows Removed by Filter: 13178
  • Heap Blocks: exact=22910
7. 0.003 14.699 ↓ 0.0 0 1

BitmapOr (cost=1,446.16..1,446.16 rows=76,746 width=0) (actual time=14.699..14.699 rows=0 loops=1)

8. 14.664 14.664 ↑ 1.1 70,469 1

Bitmap Index Scan on idx_storeid_subordertable (cost=0.00..1,401.25 rows=75,843 width=0) (actual time=14.664..14.664 rows=70,469 loops=1)

  • Index Cond: (storeid = 2826859)
9. 0.032 0.032 ↓ 0.0 0 1

Bitmap Index Scan on idx_parentstoreid_subordertable (cost=0.00..19.21 rows=903 width=0) (actual time=0.032..0.032 rows=0 loops=1)

  • Index Cond: (parentstoreid = 2826859)
10. 70.196 111.586 ↑ 1.2 247,722 3

Parallel Hash (cost=11,596.52..11,596.52 rows=309,652 width=16) (actual time=111.585..111.586 rows=247,722 loops=3)

  • Buckets: 1048576 Batches: 1 Memory Usage: 43104kB
11. 41.390 41.390 ↑ 1.2 247,722 3

Parallel Seq Scan on paymenttable (cost=0.00..11,596.52 rows=309,652 width=16) (actual time=0.016..41.390 rows=247,722 loops=3)

12. 0.004 0.004 ↑ 1.0 1 29,066

Index Scan using shipmenttable_pkey on shipmenttable (cost=0.43..4.80 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=29,066)

  • Index Cond: (subordertable.subordershipmentid = shipmentid)
13.          

CTE retailorders

14. 14.699 279.668 ↑ 200.0 1 1

HashAggregate (cost=2,258.93..2,260.93 rows=200 width=48) (actual time=279.667..279.668 rows=1 loops=1)

  • Group Key: ordersdata.storeid
15. 264.969 264.969 ↑ 1.6 29,066 1

CTE Scan on ordersdata (cost=0.00..926.74 rows=46,337 width=40) (actual time=142.872..264.969 rows=29,066 loops=1)

16.          

CTE supplyorders

17. 12.853 15.086 ↑ 3.4 59 1

HashAggregate (cost=2,258.93..2,260.93 rows=200 width=48) (actual time=15.073..15.086 rows=59 loops=1)

  • Group Key: ordersdata_1.parentstoreid
18. 2.233 2.233 ↑ 1.6 29,066 1

CTE Scan on ordersdata ordersdata_1 (cost=0.00..926.74 rows=46,337 width=40) (actual time=0.001..2.233 rows=29,066 loops=1)

19.          

CTE data_naps

20. 0.008 294.791 ↑ 2.0 1 1

GroupAggregate (cost=0.00..9.09 rows=2 width=168) (actual time=294.791..294.791 rows=1 loops=1)

  • Group Key: retailorders.storeid
21. 0.003 294.783 ↑ 2.0 1 1

Append (cost=0.00..9.01 rows=2 width=48) (actual time=279.671..294.783 rows=1 loops=1)

22. 279.671 279.671 ↑ 1.0 1 1

CTE Scan on retailorders (cost=0.00..4.50 rows=1 width=48) (actual time=279.670..279.671 rows=1 loops=1)

  • Filter: (storeid = 2826859)
23. 15.109 15.109 ↓ 0.0 0 1

CTE Scan on supplyorders (cost=0.00..4.50 rows=1 width=48) (actual time=15.109..15.109 rows=0 loops=1)

  • Filter: (storeid = 2826859)
  • Rows Removed by Filter: 59