explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VImP

Settings
# exclusive inclusive rows x rows loops node
1. 1.395 20,717.873 ↑ 1.4 882 1

Sort (cost=1,571,234.42..1,571,237.44 rows=1,208 width=268) (actual time=20,717.690..20,717.873 rows=882 loops=1)

  • Sort Key: b.pick_session_id DESC
  • Sort Method: quicksort Memory: 259kB
2. 3.408 20,716.478 ↑ 1.4 882 1

Hash Left Join (cost=1,237,621.93..1,571,172.58 rows=1,208 width=268) (actual time=15,469.580..20,716.478 rows=882 loops=1)

  • Hash Cond: (s.user_id = u.id)
3. 1.829 20,709.693 ↑ 1.4 882 1

Nested Loop Left Join (cost=1,237,519.25..1,568,069.53 rows=1,208 width=175) (actual time=15,468.736..20,709.693 rows=882 loops=1)

  • Join Filter: (pl.location_id = l.id)
  • Rows Removed by Join Filter: 3,556
4. 1.151 20,690.224 ↑ 1.4 882 1

Hash Left Join (cost=1,237,518.83..1,567,477.35 rows=1,208 width=179) (actual time=15,468.680..20,690.224 rows=882 loops=1)

  • Hash Cond: (pi.packer_user_id = pack.id)
5. 0.764 20,688.129 ↑ 1.4 882 1

Nested Loop Left Join (cost=1,237,416.15..1,567,358.06 rows=1,208 width=170) (actual time=15,467.671..20,688.129 rows=882 loops=1)

6. 0.877 20,682.217 ↑ 1.4 858 1

Hash Left Join (cost=1,237,415.72..1,566,780.34 rows=1,208 width=127) (actual time=15,467.610..20,682.217 rows=858 loops=1)

  • Hash Cond: (co.shipping_method_id = sm.id)
7. 141.993 20,681.299 ↑ 1.4 858 1

Hash Semi Join (cost=1,237,412.24..1,566,760.40 rows=1,208 width=112) (actual time=15,467.527..20,681.299 rows=858 loops=1)

  • Hash Cond: (r.batch_id = "ANY_subquery".id)
8. 799.019 20,538.407 ↓ 1.0 1,897,740 1

Hash Left Join (cost=1,237,205.40..1,561,705.54 rows=1,841,747 width=112) (actual time=10,384.820..20,538.407 rows=1,897,740 loops=1)

  • Hash Cond: (r.batch_id = b.id)
9. 847.491 19,059.903 ↓ 1.0 1,897,740 1

Hash Left Join (cost=1,173,792.04..1,472,968.15 rows=1,841,747 width=92) (actual time=9,699.667..19,059.903 rows=1,897,740 loops=1)

  • Hash Cond: (pk.product_id = p.id)
10. 1,971.416 17,779.215 ↓ 1.0 1,897,740 1

Merge Right Join (cost=1,123,498.47..1,397,350.57 rows=1,841,747 width=58) (actual time=9,263.001..17,779.215 rows=1,897,740 loops=1)

  • Merge Cond: (co.id = pk.invoice_id)
11. 11,832.649 11,832.649 ↓ 1.0 16,732,938 1

Index Scan using customer_orders_pkey on customer_orders co (cost=0.43..934,799.09 rows=16,593,977 width=16) (actual time=0.024..11,832.649 rows=16,732,938 loops=1)

12. 149.350 3,975.150 ↓ 1.0 1,897,740 1

Materialize (cost=388,835.97..398,044.70 rows=1,841,747 width=46) (actual time=3,274.382..3,975.150 rows=1,897,740 loops=1)

13. 1,390.838 3,825.800 ↓ 1.0 1,897,740 1

Sort (cost=388,835.97..393,440.33 rows=1,841,747 width=46) (actual time=3,274.380..3,825.800 rows=1,897,740 loops=1)

  • Sort Key: pk.invoice_id
  • Sort Method: external merge Disk: 122,400kB
14. 841.182 2,434.962 ↓ 1.0 1,897,740 1

Merge Left Join (cost=0.85..164,801.85 rows=1,841,747 width=46) (actual time=0.025..2,434.962 rows=1,897,740 loops=1)

  • Merge Cond: (r.pick_id = pk.id)
15. 651.948 651.948 ↓ 1.0 1,897,740 1

Index Scan using dc_routes_pick_id_idx on dc_routes r (cost=0.43..53,973.63 rows=1,841,747 width=16) (actual time=0.010..651.948 rows=1,897,740 loops=1)

16. 941.832 941.832 ↓ 1.0 1,916,590 1

Index Scan using dc_picks_pkey on dc_picks pk (cost=0.43..83,627.24 rows=1,891,854 width=30) (actual time=0.011..941.832 rows=1,916,590 loops=1)

17. 80.537 433.197 ↓ 1.0 302,070 1

Hash (cost=46,518.55..46,518.55 rows=302,001 width=38) (actual time=433.197..433.197 rows=302,070 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 22,079kB
18. 52.357 352.660 ↓ 1.0 302,070 1

Hash Left Join (cost=16,075.82..46,518.55 rows=302,001 width=38) (actual time=149.340..352.660 rows=302,070 loops=1)

  • Hash Cond: (p.manufacturer_id = m.id)
19. 90.324 299.335 ↓ 1.0 302,070 1

Hash Left Join (cost=15,965.05..42,258.28 rows=302,001 width=32) (actual time=148.279..299.335 rows=302,070 loops=1)

  • Hash Cond: (p.location_id = l.id)
20. 62.539 62.539 ↓ 1.0 302,070 1

Seq Scan on products p (cost=0.00..24,508.01 rows=302,001 width=22) (actual time=0.020..62.539 rows=302,070 loops=1)

21. 83.948 146.472 ↓ 1.0 425,785 1

Hash (cost=10,870.02..10,870.02 rows=407,602 width=14) (actual time=146.472..146.472 rows=425,785 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 24,239kB
22. 62.524 62.524 ↓ 1.0 425,785 1

Seq Scan on locations l (cost=0.00..10,870.02 rows=407,602 width=14) (actual time=0.015..62.524 rows=425,785 loops=1)

23. 0.566 0.968 ↑ 1.0 3,663 1

Hash (cost=64.79..64.79 rows=3,679 width=14) (actual time=0.968..0.968 rows=3,663 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 205kB
24. 0.402 0.402 ↑ 1.0 3,663 1

Seq Scan on manufacturers m (cost=0.00..64.79 rows=3,679 width=14) (actual time=0.008..0.402 rows=3,663 loops=1)

25. 156.826 679.485 ↓ 1.0 616,645 1

Hash (cost=55,788.07..55,788.07 rows=610,023 width=24) (actual time=679.484..679.485 rows=616,645 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 41,379kB
26. 197.606 522.659 ↓ 1.0 616,645 1

Hash Left Join (cost=31,385.43..55,788.07 rows=610,023 width=24) (actual time=256.592..522.659 rows=616,645 loops=1)

  • Hash Cond: (b.pick_session_id = s.id)
27. 72.017 72.017 ↓ 1.0 616,645 1

Seq Scan on dc_batches b (cost=0.00..16,124.23 rows=610,023 width=20) (actual time=0.012..72.017 rows=616,645 loops=1)

28. 120.742 253.036 ↓ 1.0 753,676 1

Hash (cost=22,060.19..22,060.19 rows=746,019 width=8) (actual time=253.036..253.036 rows=753,676 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 37,633kB
29. 132.294 132.294 ↓ 1.0 753,676 1

Seq Scan on dc_pick_sessions s (cost=0.00..22,060.19 rows=746,019 width=8) (actual time=0.008..132.294 rows=753,676 loops=1)

30. 0.049 0.899 ↓ 1.6 325 1

Hash (cost=204.28..204.28 rows=205 width=4) (actual time=0.899..0.899 rows=325 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
31. 0.047 0.850 ↓ 1.6 325 1

Subquery Scan on ANY_subquery (cost=201.71..204.28 rows=205 width=4) (actual time=0.773..0.850 rows=325 loops=1)

32. 0.067 0.803 ↓ 1.6 325 1

Sort (cost=201.71..202.23 rows=205 width=12) (actual time=0.772..0.803 rows=325 loops=1)

  • Sort Key: db.created_at DESC
  • Sort Method: quicksort Memory: 40kB
33.          

Initplan (for Sort)

34. 0.000 0.039 ↑ 1.0 1 1

Limit (cost=0.42..2.44 rows=1 width=4) (actual time=0.039..0.039 rows=1 loops=1)

35. 0.039 0.039 ↑ 1.0 1 1

Index Scan using locations_name_idx on locations (cost=0.42..2.44 rows=1 width=4) (actual time=0.039..0.039 rows=1 loops=1)

  • Index Cond: (name = 'RG100000123'::text)
36. 0.697 0.697 ↓ 1.6 325 1

Index Scan using dc_batches_container_location_id_idx on dc_batches db (cost=0.42..191.40 rows=205 width=12) (actual time=0.081..0.697 rows=325 loops=1)

  • Index Cond: (container_location_id = $1)
37. 0.017 0.041 ↑ 1.0 66 1

Hash (cost=2.66..2.66 rows=66 width=23) (actual time=0.041..0.041 rows=66 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
38. 0.024 0.024 ↑ 1.0 66 1

Seq Scan on shipping_methods sm (cost=0.00..2.66 rows=66 width=23) (actual time=0.016..0.024 rows=66 loops=1)

39. 5.148 5.148 ↑ 1.0 1 858

Index Scan using dc_packed_items_pick_id_idx on dc_packed_items pi (cost=0.43..0.47 rows=1 width=47) (actual time=0.005..0.006 rows=1 loops=858)

  • Index Cond: (pick_id = pk.id)
40. 0.468 0.944 ↑ 1.0 3,008 1

Hash (cost=65.08..65.08 rows=3,008 width=17) (actual time=0.944..0.944 rows=3,008 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 183kB
41. 0.476 0.476 ↑ 1.0 3,008 1

Seq Scan on dc_users pack (cost=0.00..65.08 rows=3,008 width=17) (actual time=0.016..0.476 rows=3,008 loops=1)

42. 17.640 17.640 ↓ 2.5 5 882

Index Scan using products_locations_product_id_idx on products_locations pl (cost=0.42..0.47 rows=2 width=12) (actual time=0.008..0.020 rows=5 loops=882)

  • Index Cond: (p.id = product_id)
43. 0.418 0.731 ↑ 1.0 3,008 1

Hash (cost=65.08..65.08 rows=3,008 width=17) (actual time=0.731..0.731 rows=3,008 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 183kB
44. 0.313 0.313 ↑ 1.0 3,008 1

Seq Scan on dc_users u (cost=0.00..65.08 rows=3,008 width=17) (actual time=0.004..0.313 rows=3,008 loops=1)

45.          

SubPlan (for Hash Left Join)

46. 0.882 2.646 ↑ 1.0 1 882

Aggregate (cost=2.45..2.46 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=882)

47. 1.764 1.764 ↑ 1.0 1 882

Index Scan using dc_packed_items_pick_id_idx on dc_packed_items (cost=0.43..2.45 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=882)

  • Index Cond: (pick_id = pk.id)
Planning time : 5.260 ms
Execution time : 20,744.762 ms