explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PPnq

Settings
# exclusive inclusive rows x rows loops node
1. 0.366 273.026 ↓ 96.0 96 1

Sort (cost=2,218.93..2,218.93 rows=1 width=336) (actual time=273.020..273.026 rows=96 loops=1)

  • Sort Key: (CASE WHEN ((ordli.order_lineitem_status_code)::text = 'open'::text) THEN 1 WHEN ((ordli.order_lineitem_status_code)::text = 'audited'::text) THEN 2 ELSE NULL::integer END)
  • Sort Method: quicksort Memory: 398kB
2.          

CTE ordlico

3. 0.011 269.461 ↑ 1.0 100 1

Limit (cost=0.06..1,447.00 rows=100 width=141) (actual time=269.336..269.461 rows=100 loops=1)

4. 1.448 269.450 ↑ 6.7 100 1

WindowAgg (cost=0.06..9,622.21 rows=665 width=141) (actual time=269.335..269.450 rows=100 loops=1)

5. 3.407 268.002 ↑ 1.8 366 1

GroupAggregate (cost=0.06..9,618.55 rows=665 width=133) (actual time=0.155..268.002 rows=366 loops=1)

  • Group Key: olc.order_lineitem_id
6. 0.529 264.595 ↓ 2.9 1,919 1

Nested Loop (cost=0.06..9,611.90 rows=665 width=209) (actual time=0.064..264.595 rows=1,919 loops=1)

7. 80.033 260.406 ↑ 1.5 366 1

Nested Loop (cost=0.06..9,543.73 rows=558 width=4) (actual time=0.029..260.406 rows=366 loops=1)

8. 59.040 59.040 ↑ 1.0 121,333 1

Index Scan using order_lineitem_pk on order_lineitem ol (cost=0.06..4,342.88 rows=121,378 width=8) (actual time=0.009..59.040 rows=121,333 loops=1)

  • Filter: ((order_lineitem_status_code)::text = ANY ('{open,audited}'::text[]))
  • Rows Removed by Filter: 296
9. 121.333 121.333 ↓ 0.0 0 121,333

Index Scan using order_id_hash_idx on "order" o (cost=0.00..0.04 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=121,333)

  • Index Cond: (id = ol.order_id)
  • Rows Removed by Index Recheck: 0
  • Filter: ((store_id)::text = '1056'::text)
  • Rows Removed by Filter: 1
10. 3.660 3.660 ↓ 2.5 5 366

Index Scan using orderlineitemcomponent_orderlineitemid_hash_idx on order_lineitem_component olc (cost=0.00..0.12 rows=2 width=209) (actual time=0.007..0.010 rows=5 loops=366)

  • Index Cond: (order_lineitem_id = ol.id)
11.          

CTE osh

12. 0.121 1.350 ↑ 1.1 96 1

GroupAggregate (cost=387.16..387.73 rows=104 width=43) (actual time=1.238..1.350 rows=96 loops=1)

  • Group Key: olish.order_lineitem_number
13. 0.210 1.229 ↑ 1.0 101 1

Sort (cost=387.16..387.21 rows=104 width=593) (actual time=1.222..1.229 rows=101 loops=1)

  • Sort Key: olish.order_lineitem_number
  • Sort Method: quicksort Memory: 32kB
14. 0.051 1.019 ↑ 1.0 101 1

Hash Join (cost=1.14..386.46 rows=104 width=593) (actual time=0.077..1.019 rows=101 loops=1)

  • Hash Cond: (olish.order_lineitem_status_id = olis.id)
15. 0.024 0.945 ↑ 1.0 101 1

Nested Loop (cost=0.06..385.30 rows=104 width=23) (actual time=0.032..0.945 rows=101 loops=1)

16. 0.015 0.421 ↑ 1.0 100 1

Nested Loop (cost=0.06..380.90 rows=100 width=11) (actual time=0.007..0.421 rows=100 loops=1)

17. 0.206 0.206 ↑ 1.0 100 1

CTE Scan on ordlico ordlico_1 (cost=0.00..0.60 rows=100 width=4) (actual time=0.001..0.206 rows=100 loops=1)

18. 0.200 0.200 ↑ 1.0 1 100

Index Scan using order_lineitem_pk on order_lineitem olitem (cost=0.06..3.80 rows=1 width=15) (actual time=0.002..0.002 rows=1 loops=100)

  • Index Cond: (id = ordlico_1.order_lineitem_id)
19. 0.500 0.500 ↑ 1.0 1 100

Index Scan using order_lineitem_status_history_orderlineitemstatusnumber_hash_id on order_lineitem_status_history olish (cost=0.00..0.04 rows=1 width=23) (actual time=0.003..0.005 rows=1 loops=100)

  • Index Cond: ((order_lineitem_number)::text = (olitem.order_lineitem_number)::text)
20. 0.009 0.023 ↑ 1.0 12 1

Hash (cost=1.04..1.04 rows=12 width=578) (actual time=0.023..0.023 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.014 0.014 ↑ 1.0 12 1

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

22. 0.025 272.660 ↓ 96.0 96 1

Nested Loop (cost=1.13..384.20 rows=1 width=336) (actual time=270.843..272.660 rows=96 loops=1)

23. 0.573 272.347 ↓ 96.0 96 1

Nested Loop (cost=1.05..383.40 rows=1 width=319) (actual time=270.824..272.347 rows=96 loops=1)

  • Join Filter: ((ord.framer_username)::text = (frm.username)::text)
  • Rows Removed by Join Filter: 5441
24. 0.088 271.294 ↓ 96.0 96 1

Nested Loop (cost=1.05..382.01 rows=1 width=303) (actual time=270.799..271.294 rows=96 loops=1)

25. 0.073 271.110 ↓ 96.0 96 1

Hash Join (cost=1.05..381.97 rows=1 width=286) (actual time=270.793..271.110 rows=96 loops=1)

  • Hash Cond: ((ordli.order_lineitem_number)::text = (osh.order_lineitem_number)::text)
26. 0.043 269.622 ↑ 1.0 100 1

Nested Loop (cost=0.06..380.90 rows=100 width=254) (actual time=269.358..269.622 rows=100 loops=1)

27. 269.379 269.379 ↑ 1.0 100 1

CTE Scan on ordlico (cost=0.00..0.60 rows=100 width=141) (actual time=269.341..269.379 rows=100 loops=1)

28. 0.200 0.200 ↑ 1.0 1 100

Index Scan using order_lineitem_pk on order_lineitem ordli (cost=0.06..3.80 rows=1 width=117) (actual time=0.002..0.002 rows=1 loops=100)

  • Index Cond: (id = ordlico.order_lineitem_id)
29. 0.026 1.415 ↑ 1.1 96 1

Hash (cost=0.62..0.62 rows=104 width=150) (actual time=1.415..1.415 rows=96 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
30. 1.389 1.389 ↑ 1.1 96 1

CTE Scan on osh (cost=0.00..0.62 rows=104 width=150) (actual time=1.241..1.389 rows=96 loops=1)

31. 0.096 0.096 ↑ 1.0 1 96

Index Scan using order_id_hash_idx on "order" ord (cost=0.00..0.04 rows=1 width=25) (actual time=0.001..0.001 rows=1 loops=96)

  • Index Cond: (id = ordli.order_id)
32. 0.480 0.480 ↑ 1.1 58 96

Seq Scan on framer frm (cost=0.00..1.18 rows=61 width=27) (actual time=0.002..0.005 rows=58 loops=96)

33. 0.288 0.288 ↑ 1.0 1 96

Index Scan using customer_pk on customer cust (cost=0.09..0.79 rows=1 width=17) (actual time=0.003..0.003 rows=1 loops=96)

  • Index Cond: (id = ord.customer_id)
Planning time : 4.245 ms
Execution time : 273.626 ms