explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MDv

Settings
# exclusive inclusive rows x rows loops node
1. 0.395 303.294 ↑ 1.1 3,105 1

Subquery Scan on T1 (cost=18,506.43..18,523.73 rows=3,461 width=270) (actual time=301.342..303.294 rows=3,105 loops=1)

2. 1.378 302.899 ↑ 1.1 3,105 1

Group (cost=18,506.43..18,513.35 rows=3,461 width=354) (actual time=301.340..302.899 rows=3,105 loops=1)

  • Group Key: "T1_1".id, "T1_6".id, "T1_7".id
3. 4.092 301.521 ↑ 1.0 3,379 1

Sort (cost=18,506.43..18,508.16 rows=3,461 width=354) (actual time=301.337..301.521 rows=3,379 loops=1)

  • Sort Key: "T1_1".id, "T1_6".id, "T1_7".id
  • Sort Method: quicksort Memory: 1,697kB
4. 2.653 297.429 ↑ 1.0 3,379 1

Nested Loop (cost=5,493.89..18,465.74 rows=3,461 width=354) (actual time=246.311..297.429 rows=3,379 loops=1)

5. 2.870 291.397 ↑ 1.0 3,379 1

Nested Loop (cost=5,493.84..18,253.42 rows=3,461 width=244) (actual time=246.301..291.397 rows=3,379 loops=1)

6. 2.515 278.339 ↑ 1.0 3,396 1

Nested Loop Left Join (cost=5,493.75..7,640.42 rows=3,461 width=17) (actual time=246.287..278.339 rows=3,396 loops=1)

7. 5.638 272.428 ↑ 1.0 3,396 1

Hash Left Join (cost=5,493.69..7,384.91 rows=3,461 width=25) (actual time=246.269..272.428 rows=3,396 loops=1)

  • Hash Cond: (("T1_1".id)::bigint = ("T1_5".result0_31)::bigint)
  • Filter: (CASE WHEN (CASE WHEN ((false) IS NULL) THEN NULL::boolean ELSE "T1_5".result2_31 END IS NULL) THEN true ELSE CASE WHEN ((false) IS NULL) THEN NULL::boolean ELSE "T1_5".result2_31 END END OR (CASE WHEN ((false) IS NULL) THEN NULL::timestamp with time zone ELSE "T1_5".result1_31 END IS NULL))
  • Rows Removed by Filter: 23,152
8. 9.756 117.523 ↓ 3.9 26,548 1

Hash Right Join (cost=1,698.12..3,585.72 rows=6,887 width=25) (actual time=83.348..117.523 rows=26,548 loops=1)

  • Hash Cond: (("T1_2".purchase_order_id)::bigint = ("T1_1".id)::bigint)
9. 20.817 107.140 ↓ 1.1 86,722 1

Hash Left Join (cost=1,647.39..3,493.15 rows=79,649 width=33) (actual time=73.027..107.140 rows=86,722 loops=1)

  • Hash Cond: (("T1_2".id)::bigint = ("T1_3".result0_10)::bigint)
10. 13.398 13.398 ↓ 1.1 86,722 1

Seq Scan on purchase_order_part "T1_2" (cost=0.00..1,803.95 rows=79,649 width=32) (actual time=0.006..13.398 rows=86,722 loops=1)

11. 6.865 72.925 ↓ 1.2 49,687 1

Hash (cost=1,501.45..1,501.45 rows=41,697 width=8) (actual time=72.925..72.925 rows=49,687 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,453kB
12. 5.024 66.060 ↓ 1.2 49,687 1

Subquery Scan on T1_3 (cost=1,251.27..1,501.45 rows=41,697 width=8) (actual time=54.236..66.060 rows=49,687 loops=1)

13. 20.884 61.036 ↓ 1.2 49,687 1

HashAggregate (cost=1,251.27..1,376.36 rows=41,697 width=56) (actual time=54.236..61.036 rows=49,687 loops=1)

  • Group Key: "T1_8".purchase_order_part_id
14. 8.294 40.152 ↓ 1.1 54,516 1

Hash Left Join (cost=839.28..1,226.77 rows=48,994 width=8) (actual time=15.462..40.152 rows=54,516 loops=1)

  • Hash Cond: (("T1_8".id)::bigint = ("T1_10".purchase_order_part_received_to_id)::bigint)
15. 10.436 31.193 ↓ 1.1 54,516 1

Hash Right Join (cost=799.46..1,141.06 rows=48,994 width=16) (actual time=14.788..31.193 rows=54,516 loops=1)

  • Hash Cond: (("T1_9".purchase_order_part_received_to_id)::bigint = ("T1_8".id)::bigint)
16. 6.067 6.067 ↓ 1.0 29,896 1

Seq Scan on purchase_order_part_received_to_pallet "T1_9" (cost=0.00..326.02 rows=29,672 width=17) (actual time=0.006..6.067 rows=29,896 loops=1)

17. 7.810 14.690 ↓ 1.1 54,516 1

Hash (cost=627.98..627.98 rows=48,994 width=16) (actual time=14.689..14.690 rows=54,516 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,068kB
18. 6.880 6.880 ↓ 1.1 54,516 1

Seq Scan on purchase_order_part_received_to "T1_8" (cost=0.00..627.98 rows=48,994 width=16) (actual time=0.004..6.880 rows=54,516 loops=1)

19. 0.321 0.665 ↑ 1.1 2,677 1

Hash (cost=29.15..29.15 rows=3,049 width=8) (actual time=0.665..0.665 rows=2,677 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 137kB
20. 0.344 0.344 ↑ 1.1 2,677 1

Seq Scan on purchase_order_part_received_to_lot "T1_10" (cost=0.00..29.15 rows=3,049 width=8) (actual time=0.004..0.344 rows=2,677 loops=1)

21. 0.158 0.627 ↓ 1.0 1,152 1

Hash (cost=46.86..46.86 rows=1,107 width=8) (actual time=0.627..0.627 rows=1,152 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 61kB
22. 0.469 0.469 ↓ 1.0 1,152 1

Index Scan using purchase_order_manufacturer_id_idx on purchase_order "T1_1" (cost=0.06..46.86 rows=1,107 width=8) (actual time=0.017..0.469 rows=1,152 loops=1)

  • Index Cond: ('8063'::bigint = (manufacturer_id)::bigint)
  • Filter: (canceled_at IS NULL)
  • Rows Removed by Filter: 158
23. 1.793 149.267 ↓ 1.5 8,754 1

Hash (cost=3,775.57..3,775.57 rows=5,716 width=18) (actual time=149.267..149.267 rows=8,754 loops=1)

  • Buckets: 16,384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 539kB
24. 0.819 147.474 ↓ 1.5 8,754 1

Subquery Scan on T1_5 (cost=3,741.27..3,775.57 rows=5,716 width=18) (actual time=145.274..147.474 rows=8,754 loops=1)

25. 21.846 146.655 ↓ 1.5 8,754 1

HashAggregate (cost=3,741.27..3,758.42 rows=5,716 width=17) (actual time=145.274..146.655 rows=8,754 loops=1)

  • Group Key: "T1_11".purchase_order_id
26. 21.104 124.809 ↓ 1.1 86,722 1

Hash Left Join (cost=1,696.38..3,542.15 rows=79,649 width=29) (actual time=87.175..124.809 rows=86,722 loops=1)

  • Hash Cond: (("T1_11".id)::bigint = ("T1_12".result0_29)::bigint)
27. 16.630 16.630 ↓ 1.1 86,722 1

Seq Scan on purchase_order_part "T1_11" (cost=0.00..1,803.95 rows=79,649 width=20) (actual time=0.005..16.630 rows=86,722 loops=1)

28. 10.509 87.075 ↓ 1.2 49,687 1

Hash (cost=1,550.44..1,550.44 rows=41,697 width=25) (actual time=87.075..87.075 rows=49,687 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,278kB
29. 4.728 76.566 ↓ 1.2 49,687 1

Subquery Scan on T1_12 (cost=1,300.26..1,550.44 rows=41,697 width=25) (actual time=63.515..76.566 rows=49,687 loops=1)

30. 25.811 71.838 ↓ 1.2 49,687 1

HashAggregate (cost=1,300.26..1,425.35 rows=41,697 width=56) (actual time=63.514..71.838 rows=49,687 loops=1)

  • Group Key: "T1_13".purchase_order_part_id
31. 8.882 46.027 ↓ 1.1 54,516 1

Hash Left Join (cost=839.28..1,226.77 rows=48,994 width=20) (actual time=19.314..46.027 rows=54,516 loops=1)

  • Hash Cond: (("T1_13".id)::bigint = ("T1_15".purchase_order_part_received_to_id)::bigint)
32. 12.392 36.277 ↓ 1.1 54,516 1

Hash Right Join (cost=799.46..1,141.06 rows=48,994 width=28) (actual time=18.421..36.277 rows=54,516 loops=1)

  • Hash Cond: (("T1_14".purchase_order_part_received_to_id)::bigint = ("T1_13".id)::bigint)
33. 5.750 5.750 ↓ 1.0 29,896 1

Seq Scan on purchase_order_part_received_to_pallet "T1_14" (cost=0.00..326.02 rows=29,672 width=17) (actual time=0.007..5.750 rows=29,896 loops=1)

34. 10.489 18.135 ↓ 1.1 54,516 1

Hash (cost=627.98..627.98 rows=48,994 width=28) (actual time=18.135..18.135 rows=54,516 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,920kB
35. 7.646 7.646 ↓ 1.1 54,516 1

Seq Scan on purchase_order_part_received_to "T1_13" (cost=0.00..627.98 rows=48,994 width=28) (actual time=0.004..7.646 rows=54,516 loops=1)

36. 0.474 0.868 ↑ 1.1 2,677 1

Hash (cost=29.15..29.15 rows=3,049 width=8) (actual time=0.868..0.868 rows=2,677 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 137kB
37. 0.394 0.394 ↑ 1.1 2,677 1

Seq Scan on purchase_order_part_received_to_lot "T1_15" (cost=0.00..29.15 rows=3,049 width=8) (actual time=0.005..0.394 rows=2,677 loops=1)

38. 3.396 3.396 ↑ 1.0 1 3,396

Index Only Scan using po_part_incoming_shipment_po_part_id_incoming_shipment_id_idx on purchase_order_part_incoming_shipment "T1_4" (cost=0.06..0.07 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=3,396)

  • Index Cond: (purchase_order_part_id = CASE WHEN ((false) IS NULL) THEN NULL::bigint ELSE ("T1_2".id)::bigint END)
  • Heap Fetches: 1,677
39. 10.188 10.188 ↑ 1.0 1 3,396

Index Scan using part_pkey on part "T1_6" (cost=0.08..3.07 rows=1 width=236) (actual time=0.003..0.003 rows=1 loops=3,396)

  • Index Cond: ((id)::bigint = CASE WHEN ((false) IS NULL) THEN NULL::bigint ELSE ("T1_2".part_id)::bigint END)
40. 3.379 3.379 ↑ 1.0 1 3,379

Index Scan using inst_pkey on inst "T1_7" (cost=0.06..0.06 rows=1 width=110) (actual time=0.001..0.001 rows=1 loops=3,379)

  • Index Cond: ((id)::bigint = ("T1_6"."mfrId")::bigint)
Planning time : 34.160 ms
Execution time : 304.237 ms