explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7Eo : Cust Inv Lines

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 173,404.341 ↑ 1.0 10 1

Limit (cost=16,611,522.68..16,611,522.71 rows=10 width=680) (actual time=173,404.334..173,404.341 rows=10 loops=1)

2.          

Initplan (forLimit)

3. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)

4. 6.491 173,404.331 ↑ 24.9 20 1

Sort (cost=16,611,522.65..16,611,523.89 rows=499 width=680) (actual time=173,404.328..173,404.331 rows=20 loops=1)

  • Sort Key: il.id
  • Sort Method: top-N heapsort Memory: 40kB
5. 9.919 173,397.840 ↓ 2.3 1,124 1

Result (cost=1,644.66..16,611,509.37 rows=499 width=680) (actual time=149.871..173,397.840 rows=1,124 loops=1)

  • One-Time Filter: $2
6. 3.196 79.485 ↓ 2.3 1,124 1

Nested Loop Left Join (cost=1,644.66..15,418.07 rows=499 width=680) (actual time=5.370..79.485 rows=1,124 loops=1)

7. 3.289 65.049 ↓ 2.3 1,124 1

Nested Loop Left Join (cost=1,644.37..15,249.51 rows=499 width=652) (actual time=5.363..65.049 rows=1,124 loops=1)

8. 4.202 43.776 ↓ 2.3 1,124 1

Nested Loop (cost=1,643.95..11,262.55 rows=499 width=630) (actual time=5.343..43.776 rows=1,124 loops=1)

9. 4.719 21.598 ↓ 1.5 1,284 1

Hash Join (cost=1,643.53..4,905.48 rows=832 width=534) (actual time=5.303..21.598 rows=1,284 loops=1)

  • Hash Cond: (o.customer_id = cu.id)
10. 11.738 13.608 ↓ 1.5 1,284 1

Bitmap Heap Scan on tms_order o (cost=1,446.85..4,694.96 rows=882 width=515) (actual time=2.021..13.608 rows=1,284 loops=1)

  • Recheck Cond: (account_id = 25)
  • Filter: (consolidated_order_id IS NULL)
  • Heap Blocks: exact=1244
11. 1.870 1.870 ↓ 1.6 1,401 1

Bitmap Index Scan on tms_order_account_id_create_date_search_index_idx (cost=0.00..1,446.63 rows=883 width=0) (actual time=1.870..1.870 rows=1,401 loops=1)

  • Index Cond: (account_id = 25)
12. 1.135 3.271 ↓ 1.0 3,062 1

Hash (cost=158.43..158.43 rows=3,060 width=23) (actual time=3.271..3.271 rows=3,062 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 172kB
13. 2.136 2.136 ↓ 1.0 3,062 1

Seq Scan on customer cu (cost=0.00..158.43 rows=3,060 width=23) (actual time=0.007..2.136 rows=3,062 loops=1)

  • Filter: (NOT is_deleted)
  • Rows Removed by Filter: 183
14. 17.976 17.976 ↑ 1.0 1 1,284

Index Scan using cust_invoice_line_order_id on tms_customer_invoice_line il (cost=0.42..7.63 rows=1 width=96) (actual time=0.013..0.014 rows=1 loops=1,284)

  • Index Cond: (tms_order_id = o.id)
15. 17.984 17.984 ↑ 1.0 1 1,124

Index Scan using tms_load_pkey on tms_load l (cost=0.42..7.98 rows=1 width=30) (actual time=0.014..0.016 rows=1 loops=1,124)

  • Index Cond: (o.load_id = id)
16. 11.240 11.240 ↑ 1.0 1 1,124

Index Scan using core_carrier on carrier ca (cost=0.29..0.33 rows=1 width=32) (actual time=0.008..0.010 rows=1 loops=1,124)

  • Index Cond: (id = l.carrier_id)
17.          

SubPlan (forResult)

18. 13.488 173,308.436 ↑ 1.0 1 1,124

Aggregate (cost=33,258.68..33,258.69 rows=1 width=0) (actual time=154.189..154.189 rows=1 loops=1,124)

19. 6.744 173,294.948 ↑ 144,952.0 4 1,124

Result (cost=0.00..31,809.16 rows=579,808 width=0) (actual time=152.822..154.177 rows=4 loops=1,124)

  • One-Time Filter: (il.tms_order_id = o.id)
20. 173,288.204 173,288.204 ↑ 144,952.0 4 1,124

Seq Scan on tms_order_internal_note (cost=0.00..31,809.16 rows=579,808 width=0) (actual time=152.820..154.171 rows=4 loops=1,124)

  • Filter: on_cust_invoice_line
  • Rows Removed by Filter: 1144767