explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4Jg8

Settings
# exclusive inclusive rows x rows loops node
1. 0.601 21,270.730 ↓ 8.2 878 1

Sort (cost=1,311,317.94..1,311,318.21 rows=107 width=275) (actual time=21,270.643..21,270.730 rows=878 loops=1)

  • Sort Key: b.pick_session_id DESC
  • Sort Method: quicksort Memory: 258kB
2. 0.810 21,270.129 ↓ 8.2 878 1

Nested Loop Left Join (cost=1,229,102.48..1,311,314.33 rows=107 width=275) (actual time=20,627.439..21,270.129 rows=878 loops=1)

3. 0.341 21,266.685 ↓ 8.2 878 1

Nested Loop Left Join (cost=1,229,102.20..1,311,017.13 rows=107 width=182) (actual time=20,627.398..21,266.685 rows=878 loops=1)

  • Join Filter: (pl.location_id = l.id)
  • Rows Removed by Join Filter: 3,553
4. 0.382 21,257.564 ↓ 8.2 878 1

Nested Loop Left Join (cost=1,229,101.78..1,310,964.68 rows=107 width=186) (actual time=20,627.240..21,257.564 rows=878 loops=1)

5. 0.356 21,256.304 ↓ 8.2 878 1

Nested Loop Left Join (cost=1,229,101.50..1,310,931.78 rows=107 width=177) (actual time=20,627.217..21,256.304 rows=878 loops=1)

6. 0.238 21,252.532 ↓ 8.0 854 1

Hash Left Join (cost=1,229,101.07..1,310,880.60 rows=107 width=134) (actual time=20,627.186..21,252.532 rows=854 loops=1)

  • Hash Cond: (co.shipping_method_id = sm.id)
7. 71.505 21,252.258 ↓ 8.0 854 1

Hash Right Join (cost=1,229,097.59..1,310,875.66 rows=107 width=119) (actual time=20,627.108..21,252.258 rows=854 loops=1)

  • Hash Cond: ((seqnum.batch_id)::integer = r.batch_id)
8. 140.118 11,287.896 ↓ 50.0 628,557 1

Subquery Scan on seqnum (cost=386,968.58..468,668.05 rows=12,569 width=12) (actual time=9,099.597..11,287.896 rows=628,557 loops=1)

  • Filter: (seqnum.row_num = 1)
  • Rows Removed by Filter: 1,660,244
9. 625.645 11,147.778 ↑ 1.1 2,288,801 1

WindowAgg (cost=386,968.58..437,245.18 rows=2,513,830 width=45) (actual time=9,099.587..11,147.778 rows=2,288,801 loops=1)

10. 9,782.442 10,522.133 ↑ 1.1 2,288,801 1

Sort (cost=386,968.58..393,253.15 rows=2,513,830 width=37) (actual time=9,099.575..10,522.133 rows=2,288,801 loops=1)

  • Sort Key: cs.container_id, cs.received DESC
  • Sort Method: external merge Disk: 108,760kB
11. 739.691 739.691 ↑ 1.1 2,288,801 1

Seq Scan on container_status cs (cost=0.00..80,450.15 rows=2,513,830 width=37) (actual time=0.803..739.691 rows=2,288,801 loops=1)

  • Filter: ((received >= '2020-02-14 00:00:00'::timestamp without time zone) AND (batch_id <> 'BatchId'::text))
  • Rows Removed by Filter: 243,176
12. 0.813 9,892.857 ↓ 8.0 854 1

Hash (cost=842,127.67..842,127.67 rows=107 width=112) (actual time=9,892.857..9,892.857 rows=854 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 140kB
13. 158.208 9,892.044 ↓ 8.0 854 1

Hash Semi Join (cost=785,963.00..842,127.67 rows=107 width=112) (actual time=4,679.668..9,892.044 rows=854 loops=1)

  • Hash Cond: (r.batch_id = "ANY_subquery".id)
14. 407.108 9,733.144 ↓ 11.5 1,881,781 1

Hash Left Join (cost=785,756.17..841,491.63 rows=163,053 width=112) (actual time=4,676.171..9,733.144 rows=1,881,781 loops=1)

  • Hash Cond: (p.manufacturer_id = m.id)
15. 665.015 9,325.203 ↓ 11.5 1,881,781 1

Hash Left Join (cost=785,645.39..839,140.50 rows=163,053 width=106) (actual time=4,675.305..9,325.203 rows=1,881,781 loops=1)

  • Hash Cond: (p.location_id = l.id)
16. 691.146 8,532.348 ↓ 11.5 1,881,781 1

Hash Left Join (cost=769,680.34..822,211.60 rows=163,053 width=96) (actual time=4,545.513..8,532.348 rows=1,881,781 loops=1)

  • Hash Cond: (pk.product_id = p.id)
17. 655.235 7,647.378 ↓ 11.5 1,881,781 1

Hash Left Join (cost=741,397.32..791,686.60 rows=163,053 width=78) (actual time=4,349.730..7,647.378 rows=1,881,781 loops=1)

  • Hash Cond: (b.pick_session_id = s.id)
18. 799.506 6,723.665 ↓ 11.5 1,881,781 1

Hash Left Join (cost=710,011.89..758,088.43 rows=163,053 width=74) (actual time=4,077.798..6,723.665 rows=1,881,781 loops=1)

  • Hash Cond: (r.batch_id = b.id)
19. 1,667.284 5,679.103 ↓ 11.5 1,881,781 1

Hash Join (cost=686,262.38..732,096.94 rows=163,053 width=58) (actual time=3,828.213..5,679.103 rows=1,881,781 loops=1)

  • Hash Cond: (r.pick_id = pk.id)
20. 184.317 184.317 ↓ 1.0 1,897,772 1

Seq Scan on dc_routes r (cost=0.00..37,297.47 rows=1,841,747 width=16) (actual time=0.006..184.317 rows=1,897,772 loops=1)

21. 491.876 3,827.502 ↓ 11.3 1,890,288 1

Hash (cost=684,168.75..684,168.75 rows=167,490 width=42) (actual time=3,827.501..3,827.502 rows=1,890,288 loops=1)

  • Buckets: 1,048,576 (originally 262144) Batches: 4 (originally 1) Memory Usage: 46,953kB
22. 1,075.315 3,335.626 ↓ 11.3 1,890,288 1

Hash Join (cost=574,930.86..684,168.75 rows=167,490 width=42) (actual time=1,807.756..3,335.626 rows=1,890,288 loops=1)

  • Hash Cond: (pk.invoice_id = co.id)
23. 456.378 456.378 ↓ 1.0 1,906,507 1

Seq Scan on dc_picks pk (cost=0.00..67,428.54 rows=1,891,854 width=30) (actual time=0.006..456.378 rows=1,906,507 loops=1)

24. 299.969 1,803.933 ↓ 1.1 1,560,904 1

Hash (cost=549,393.15..549,393.15 rows=1,469,097 width=16) (actual time=1,803.933..1,803.933 rows=1,560,904 loops=1)

  • Buckets: 1,048,576 Batches: 2 Memory Usage: 44,535kB
25. 1,503.964 1,503.964 ↓ 1.1 1,560,904 1

Index Scan using customer_orders_created_at_idx on customer_orders co (cost=0.43..549,393.15 rows=1,469,097 width=16) (actual time=0.011..1,503.964 rows=1,560,904 loops=1)

  • Index Cond: (created_at >= '2020-02-14 00:00:00'::timestamp without time zone)
26. 125.904 245.056 ↓ 1.0 616,661 1

Hash (cost=16,124.23..16,124.23 rows=610,023 width=20) (actual time=245.056..245.056 rows=616,661 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 39,064kB
27. 119.152 119.152 ↓ 1.0 616,661 1

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

28. 131.486 268.478 ↓ 1.0 753,682 1

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

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

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

30. 64.057 193.824 ↓ 1.0 302,071 1

Hash (cost=24,508.01..24,508.01 rows=302,001 width=22) (actual time=193.824..193.824 rows=302,071 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 19,661kB
31. 129.767 129.767 ↓ 1.0 302,071 1

Seq Scan on products p (cost=0.00..24,508.01 rows=302,001 width=22) (actual time=0.011..129.767 rows=302,071 loops=1)

32. 68.797 127.840 ↓ 1.0 425,787 1

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 24,239kB
33. 59.043 59.043 ↓ 1.0 425,787 1

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

34. 0.437 0.833 ↑ 1.0 3,663 1

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

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

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

36. 0.035 0.692 ↓ 1.6 325 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
37. 0.026 0.657 ↓ 1.6 325 1

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

38. 0.048 0.631 ↓ 1.6 325 1

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

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

Initplan (for Sort)

40. 0.001 0.017 ↑ 1.0 1 1

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

41. 0.016 0.016 ↑ 1.0 1 1

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

  • Index Cond: (name = 'RG100000123'::text)
42. 0.566 0.566 ↓ 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.032..0.566 rows=325 loops=1)

  • Index Cond: (container_location_id = $1)
43. 0.016 0.036 ↑ 1.0 66 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
44. 0.020 0.020 ↑ 1.0 66 1

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

45. 3.416 3.416 ↑ 1.0 1 854

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.004..0.004 rows=1 loops=854)

  • Index Cond: (pick_id = pk.id)
46. 0.878 0.878 ↑ 1.0 1 878

Index Scan using dc_users_pkey on dc_users pack (cost=0.28..0.30 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=878)

  • Index Cond: (pi.packer_user_id = id)
47. 8.780 8.780 ↓ 2.5 5 878

Index Scan using products_locations_product_id_idx on products_locations pl (cost=0.42..0.47 rows=2 width=12) (actual time=0.004..0.010 rows=5 loops=878)

  • Index Cond: (p.id = product_id)
48. 0.878 0.878 ↑ 1.0 1 878

Index Scan using dc_users_pkey on dc_users u (cost=0.28..0.30 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=878)

  • Index Cond: (s.user_id = id)
49.          

SubPlan (for Nested Loop Left Join)

50. 0.878 1.756 ↑ 1.0 1 878

Aggregate (cost=2.45..2.46 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=878)

51. 0.878 0.878 ↑ 1.0 1 878

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.001..0.001 rows=1 loops=878)

  • Index Cond: (pick_id = pk.id)
Planning time : 4.554 ms
Execution time : 21,296.881 ms