explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zi4K : Optimization for: plan #yYBF

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.013 121.400 ↑ 1.0 50 1

Limit (cost=7,307.77..7,307.79 rows=50 width=893) (actual time=121.386..121.400 rows=50 loops=1)

2.          

CTE osh

3. 13.149 15.292 ↑ 1.0 2,794 1

HashAggregate (cost=89.34..103.48 rows=2,828 width=43) (actual time=6.707..15.292 rows=2,794 loops=1)

  • Group Key: olish.order_lineitem_number
4. 1.586 2.143 ↑ 1.0 5,363 1

Hash Join (cost=1.08..81.05 rows=5,527 width=88) (actual time=0.032..2.143 rows=5,363 loops=1)

  • Hash Cond: (olish.order_lineitem_status_id = olis.id)
5. 0.541 0.541 ↑ 1.0 5,363 1

Seq Scan on order_lineitem_status_history olish (cost=0.00..75.58 rows=5,527 width=34) (actual time=0.009..0.541 rows=5,363 loops=1)

6. 0.004 0.016 ↑ 1.0 12 1

Hash (cost=1.04..1.04 rows=12 width=62) (actual time=0.016..0.016 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
7. 0.012 0.012 ↑ 1.0 12 1

Seq Scan on order_lineitem_status olis (cost=0.00..1.04 rows=12 width=62) (actual time=0.010..0.012 rows=12 loops=1)

8.          

CTE ordlico

9. 54.743 68.942 ↑ 1.6 2,010 1

GroupAggregate (cost=641.87..2,080.66 rows=3,184 width=133) (actual time=2.979..68.942 rows=2,010 loops=1)

  • Group Key: olc.order_lineitem_id
10. 5.953 14.199 ↓ 1.1 12,849 1

Merge Join (cost=641.87..1,992.77 rows=12,165 width=434) (actual time=2.925..14.199 rows=12,849 loops=1)

  • Merge Cond: (olc.order_lineitem_id = ol.id)
11. 7.080 7.080 ↓ 1.0 19,559 1

Index Scan using order_lineitem_comp_orderlineitemid_idx on order_lineitem_component olc (cost=0.06..1,718.45 rows=19,504 width=434) (actual time=0.010..7.080 rows=19,559 loops=1)

12. 1.166 1.166 ↓ 1.0 2,010 1

Index Only Scan using order_lineitem_pk on order_lineitem ol (cost=0.06..264.51 rows=1,986 width=4) (actual time=0.010..1.166 rows=2,010 loops=1)

  • Heap Fetches: 985
13. 1.113 121.387 ↑ 53.0 50 1

Sort (cost=5,123.62..5,124.95 rows=2,649 width=893) (actual time=121.384..121.387 rows=50 loops=1)

  • Sort Key: (CASE WHEN ((ordli.order_lineitem_status_code)::text = 'open'::text) THEN 1 ELSE NULL::integer END)
  • Sort Method: top-N heapsort Memory: 352kB
14. 6.144 120.274 ↑ 2.4 1,102 1

WindowAgg (cost=867.67..5,106.02 rows=2,649 width=893) (actual time=119.346..120.274 rows=1,102 loops=1)

15. 1.389 114.130 ↑ 2.4 1,102 1

Hash Join (cost=867.67..5,095.43 rows=2,649 width=881) (actual time=109.134..114.130 rows=1,102 loops=1)

  • Hash Cond: (ord.id = ordli.order_id)
  • Join Filter: (((ord.store_id)::text = '1056'::text) OR (((ord.source_app)::text = 'ab'::text) AND ((ordli.custom_attributes ->> 'physical_store_id'::text) = '1056'::text)))
  • Rows Removed by Join Filter: 29
16. 0.424 3.691 ↓ 1.0 1,109 1

Hash Join (cost=1.44..4,212.17 rows=1,072 width=57) (actual time=0.062..3.691 rows=1,109 loops=1)

  • Hash Cond: ((ord.framer_username)::text = (frm.username)::text)
17. 0.542 3.236 ↓ 1.0 1,109 1

Nested Loop (cost=0.09..4,210.19 rows=1,072 width=41) (actual time=0.024..3.236 rows=1,109 loops=1)

18. 0.476 0.476 ↓ 1.0 1,109 1

Seq Scan on "order" ord (cost=0.00..35.18 rows=1,072 width=28) (actual time=0.012..0.476 rows=1,109 loops=1)

  • Filter: (((store_id)::text = '1056'::text) OR ((source_app)::text = 'ab'::text))
  • Rows Removed by Filter: 695
19. 2.218 2.218 ↑ 1.0 1 1,109

Index Scan using customer_pk on customer cust (cost=0.09..3.89 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=1,109)

  • Index Cond: (id = ord.customer_id)
20. 0.015 0.031 ↑ 1.0 55 1

Hash (cost=1.17..1.17 rows=55 width=27) (actual time=0.030..0.031 rows=55 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
21. 0.016 0.016 ↑ 1.0 55 1

Seq Scan on framer frm (cost=0.00..1.17 rows=55 width=27) (actual time=0.007..0.016 rows=55 loops=1)

22. 7.868 109.050 ↑ 2.5 1,833 1

Hash (cost=850.36..850.36 rows=4,534 width=839) (actual time=109.050..109.050 rows=1,833 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 7149kB
23. 2.178 101.182 ↑ 2.5 1,833 1

Hash Join (cost=815.26..850.36 rows=4,534 width=839) (actual time=25.994..101.182 rows=1,833 loops=1)

  • Hash Cond: (ordlico.order_lineitem_id = ordli.id)
24. 76.125 76.125 ↑ 1.6 2,010 1

CTE Scan on ordlico (cost=0.00..19.10 rows=3,184 width=133) (actual time=2.983..76.125 rows=2,010 loops=1)

25. 1.801 22.879 ↑ 1.5 1,833 1

Hash (cost=805.36..805.36 rows=2,828 width=710) (actual time=22.879..22.879 rows=1,833 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 1429kB
26. 1.219 21.078 ↑ 1.5 1,833 1

Hash Join (cost=786.91..805.36 rows=2,828 width=710) (actual time=10.085..21.078 rows=1,833 loops=1)

  • Hash Cond: ((osh.order_lineitem_number)::text = (ordli.order_lineitem_number)::text)
27. 16.495 16.495 ↑ 1.0 2,794 1

CTE Scan on osh (cost=0.00..16.97 rows=2,828 width=150) (actual time=6.709..16.495 rows=2,794 loops=1)

28. 1.615 3.364 ↓ 1.0 2,010 1

Hash (cost=779.96..779.96 rows=1,986 width=678) (actual time=3.364..3.364 rows=2,010 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 1402kB
29. 1.749 1.749 ↓ 1.0 2,010 1

Seq Scan on order_lineitem ordli (cost=0.00..779.96 rows=1,986 width=678) (actual time=0.007..1.749 rows=2,010 loops=1)