explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qeYM

Settings
# exclusive inclusive rows x rows loops node
1. 0.474 59,785.266 ↑ 29.5 76 1

Hash Semi Join (cost=1,195,246.15..2,795,384.22 rows=2,245 width=262) (actual time=29,224.666..59,785.266 rows=76 loops=1)

  • Hash Cond: (tt.id = t_1.id)
2. 0.999 59,784.682 ↑ 5.8 386 1

Hash Left Join (cost=1,195,209.33..2,795,313.44 rows=2,245 width=257) (actual time=24,433.515..59,784.682 rows=386 loops=1)

  • Hash Cond: (cti.id = rsi.customer_trans_item_id)
3. 1.149 59,774.867 ↑ 5.8 386 1

Nested Loop Left Join (cost=1,194,404.67..2,794,500.32 rows=2,245 width=245) (actual time=24,424.546..59,774.867 rows=386 loops=1)

4. 0.521 59,766.770 ↑ 5.8 386 1

Nested Loop Left Join (cost=1,194,404.24..2,792,859.26 rows=2,245 width=172) (actual time=24,424.531..59,766.770 rows=386 loops=1)

  • Join Filter: (tl.id = l.id)
  • Rows Removed by Join Filter: 212
5. 0.866 59,762.003 ↑ 5.8 386 1

Nested Loop Left Join (cost=1,194,403.82..2,791,827.23 rows=2,245 width=172) (actual time=24,424.501..59,762.003 rows=386 loops=1)

6. 468.058 59,758.435 ↑ 5.8 386 1

Hash Semi Join (cost=1,194,403.39..2,790,775.00 rows=2,245 width=164) (actual time=24,424.464..59,758.435 rows=386 loops=1)

  • Hash Cond: (ct.customer_id = se.customer_id)
7. 2,496.428 59,290.337 ↓ 3.3 4,669,402 1

Hash Join (cost=1,193,309.08..2,785,581.85 rows=1,405,488 width=164) (actual time=23,852.485..59,290.337 rows=4,669,402 loops=1)

  • Hash Cond: (si.title_id = tt.id)
8. 2,894.110 55,935.364 ↓ 3.3 4,669,402 1

Hash Join (cost=1,116,643.48..2,687,833.93 rows=1,405,488 width=143) (actual time=22,977.559..55,935.364 rows=4,669,402 loops=1)

  • Hash Cond: (ii.locator_id = l.id)
9. 6,952.164 52,480.868 ↓ 3.3 4,669,402 1

Hash Join (cost=1,071,166.42..2,623,031.41 rows=1,405,488 width=143) (actual time=22,379.929..52,480.868 rows=4,669,402 loops=1)

  • Hash Cond: (ti.id = cti.id)
10. 5,223.491 41,391.220 ↑ 1.4 4,669,402 1

Hash Join (cost=761,531.96..1,953,282.25 rows=6,631,006 width=103) (actual time=17,898.872..41,391.220 rows=4,669,402 loops=1)

  • Hash Cond: (ti.inventory_item_id = ii.id)
11. 10,091.168 30,370.809 ↑ 1.5 7,673,257 1

Hash Join (cost=493,453.70..1,549,006.23 rows=11,214,214 width=75) (actual time=11,888.727..30,370.809 rows=7,673,257 loops=1)

  • Hash Cond: (ti.trans_id = t.id)
12. 8,570.280 8,570.280 ↓ 1.0 35,055,198 1

Seq Scan on trans_item ti (cost=0.00..684,778.51 rows=34,484,251 width=33) (actual time=0.019..8,570.280 rows=35,055,198 loops=1)

13. 1,657.012 11,709.361 ↓ 1.1 4,045,477 1

Hash (cost=447,704.22..447,704.22 rows=3,659,958 width=58) (actual time=11,709.361..11,709.361 rows=4,045,477 loops=1)

  • Buckets: 4194304 Batches: 1 Memory Usage: 390115kB
14. 6,673.060 10,052.349 ↓ 1.1 4,045,477 1

Hash Join (cost=121,065.06..447,704.22 rows=3,659,958 width=58) (actual time=2,356.683..10,052.349 rows=4,045,477 loops=1)

  • Hash Cond: (t.id = ct.id)
15. 1,190.629 1,190.629 ↓ 1.1 11,839,362 1

Seq Scan on trans t (cost=0.00..205,630.48 rows=11,254,548 width=24) (actual time=0.024..1,190.629 rows=11,839,362 loops=1)

16. 1,338.023 2,188.660 ↓ 1.1 4,045,477 1

Hash (cost=75,315.58..75,315.58 rows=3,659,958 width=34) (actual time=2,188.660..2,188.660 rows=4,045,477 loops=1)

  • Buckets: 4194304 Batches: 1 Memory Usage: 295299kB
17. 850.637 850.637 ↓ 1.1 4,045,477 1

Seq Scan on customer_trans ct (cost=0.00..75,315.58 rows=3,659,958 width=34) (actual time=0.025..850.637 rows=4,045,477 loops=1)

18. 960.904 5,796.920 ↓ 1.0 2,582,466 1

Hash (cost=235,843.85..235,843.85 rows=2,578,753 width=44) (actual time=5,796.920..5,796.920 rows=2,582,466 loops=1)

  • Buckets: 4194304 Batches: 1 Memory Usage: 234524kB
19. 2,421.308 4,836.016 ↓ 1.0 2,582,466 1

Hash Join (cost=88,859.94..235,843.85 rows=2,578,753 width=44) (actual time=1,628.921..4,836.016 rows=2,582,466 loops=1)

  • Hash Cond: (ii.id = si.id)
20. 965.806 965.806 ↓ 1.0 4,379,292 1

Seq Scan on inventory_item ii (cost=0.00..93,939.31 rows=4,361,131 width=16) (actual time=0.025..965.806 rows=4,379,292 loops=1)

21. 838.095 1,448.902 ↓ 1.0 2,582,466 1

Hash (cost=56,625.53..56,625.53 rows=2,578,753 width=28) (actual time=1,448.902..1,448.902 rows=2,582,466 loops=1)

  • Buckets: 4194304 Batches: 1 Memory Usage: 194173kB
22. 610.807 610.807 ↓ 1.0 2,582,466 1

Seq Scan on serial_item si (cost=0.00..56,625.53 rows=2,578,753 width=28) (actual time=0.028..610.807 rows=2,582,466 loops=1)

23. 2,398.282 4,137.484 ↓ 1.0 7,673,257 1

Hash (cost=154,028.76..154,028.76 rows=7,309,176 width=48) (actual time=4,137.484..4,137.484 rows=7,673,257 loops=1)

  • Buckets: 8388608 Batches: 2 Memory Usage: 306754kB
24. 1,739.202 1,739.202 ↓ 1.0 7,673,257 1

Seq Scan on customer_trans_item cti (cost=0.00..154,028.76 rows=7,309,176 width=48) (actual time=0.037..1,739.202 rows=7,673,257 loops=1)

25. 356.040 560.386 ↓ 1.0 1,535,799 1

Hash (cost=27,162.58..27,162.58 rows=1,465,158 width=8) (actual time=560.386..560.386 rows=1,535,799 loops=1)

  • Buckets: 2097152 Batches: 1 Memory Usage: 76377kB
26. 204.346 204.346 ↓ 1.0 1,535,799 1

Seq Scan on locator l (cost=0.00..27,162.58 rows=1,465,158 width=8) (actual time=0.028..204.346 rows=1,535,799 loops=1)

27. 485.977 858.545 ↑ 1.0 1,651,954 1

Hash (cost=55,848.60..55,848.60 rows=1,665,360 width=21) (actual time=858.545..858.545 rows=1,651,954 loops=1)

  • Buckets: 2097152 Batches: 1 Memory Usage: 106722kB
28. 372.568 372.568 ↑ 1.0 1,651,954 1

Seq Scan on title tt (cost=0.00..55,848.60 rows=1,665,360 width=21) (actual time=0.028..372.568 rows=1,651,954 loops=1)

29. 0.003 0.040 ↑ 8.1 17 1

Hash (cost=1,092.59..1,092.59 rows=138 width=8) (actual time=0.040..0.040 rows=17 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 0.003 0.037 ↑ 8.1 17 1

Nested Loop (cost=0.86..1,092.59 rows=138 width=8) (actual time=0.017..0.037 rows=17 loops=1)

31. 0.004 0.004 ↑ 1.0 1 1

Index Only Scan using section_pkey on section s (cost=0.42..8.44 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=1)

  • Index Cond: (id = '1000002421002'::bigint)
  • Heap Fetches: 1
32. 0.030 0.030 ↑ 8.1 17 1

Index Scan using section_enrollment_section_id_index on section_enrollment se (cost=0.43..1,082.77 rows=138 width=16) (actual time=0.014..0.030 rows=17 loops=1)

  • Index Cond: (section_id = '1000002421002'::bigint)
  • Filter: ((expired_at IS NULL) OR ((expired_at)::date > (start_date + '21 days'::interval)))
  • Rows Removed by Filter: 1
33. 2.702 2.702 ↓ 0.0 0 386

Index Only Scan using customer_locator_pkey on customer_locator cl (cost=0.42..0.46 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=386)

  • Index Cond: (id = l.id)
  • Heap Fetches: 6
34. 4.246 4.246 ↑ 1.0 1 386

Index Scan using transit_locator_destination_id_index on transit_locator tl (cost=0.42..0.45 rows=1 width=16) (actual time=0.010..0.011 rows=1 loops=386)

  • Index Cond: (destination_id = ct.customer_id)
35. 6.948 6.948 ↑ 3.0 1 386

Index Scan using mls_order_order_item_id on mls_order mo (cost=0.43..0.70 rows=3 width=89) (actual time=0.017..0.018 rows=1 loops=386)

  • Index Cond: ((order_item_id)::text = (cti.order_item_id)::text)
  • Filter: (child_id IS NULL)
  • Rows Removed by Filter: 0
36. 3.309 8.816 ↑ 1.0 16,650 1

Hash (cost=595.96..595.96 rows=16,696 width=28) (actual time=8.816..8.816 rows=16,650 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1263kB
37. 5.507 5.507 ↑ 1.0 16,650 1

Seq Scan on rs_invoice rsi (cost=0.00..595.96 rows=16,696 width=28) (actual time=0.012..5.507 rows=16,650 loops=1)

38. 0.004 0.110 ↓ 4.0 4 1

Hash (cost=36.81..36.81 rows=1 width=16) (actual time=0.110..0.110 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
39. 0.004 0.106 ↓ 4.0 4 1

Nested Loop (cost=1.28..36.81 rows=1 width=16) (actual time=0.057..0.106 rows=4 loops=1)

40. 0.007 0.082 ↓ 4.0 4 1

Nested Loop (cost=0.85..28.36 rows=1 width=24) (actual time=0.043..0.082 rows=4 loops=1)

41. 0.039 0.039 ↓ 4.0 4 1

Index Scan using catalog_listing_section_id_index on catalog_listing cl_1 (cost=0.42..19.90 rows=1 width=16) (actual time=0.029..0.039 rows=4 loops=1)

  • Index Cond: (section_id = '1000002421002'::bigint)
  • Filter: ((active IS TRUE) AND (lower((required)::text) = 'req'::text))
42. 0.036 0.036 ↑ 1.0 1 4

Index Only Scan using title_pkey on title t_1 (cost=0.43..8.45 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=4)

  • Index Cond: (id = cl_1.title_id)
  • Heap Fetches: 4
43. 0.020 0.020 ↑ 1.0 1 4

Index Only Scan using section_pkey on section s_1 (cost=0.42..8.44 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=4)

  • Index Cond: (id = '1000002421002'::bigint)
  • Heap Fetches: 4