explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lUq3

Settings
# exclusive inclusive rows x rows loops node
1. 0.109 144.191 ↓ 147.0 147 1

Unique (cost=1,766.39..1,766.41 rows=1 width=58) (actual time=144.045..144.191 rows=147 loops=1)

2. 0.377 144.082 ↓ 194.0 194 1

Sort (cost=1,766.39..1,766.40 rows=1 width=58) (actual time=144.043..144.082 rows=194 loops=1)

  • Sort Key: p.id, p.companyconceptproduct_id, p.name, pt.product_type, pt.accounting_code, p.inventory, (CASE WHEN (p.inventory AND (NOT (hashed SubPlan 1))) THEN true ELSE false END)
  • Sort Method: quicksort Memory: 47kB
3. 19.119 143.705 ↓ 194.0 194 1

Nested Loop Left Join (cost=1,585.84..1,766.38 rows=1 width=58) (actual time=19.158..143.705 rows=194 loops=1)

  • Join Filter: (puc.productunit_id = pu.id)
  • Filter: (((NOT (hashed SubPlan 2)) AND (vp.id IS NOT NULL) AND (v.id IS NOT NULL) AND ((puc.ratio IS NULL) OR (puc.ratio = '0'::numeric) OR (puc.id IS NULL))) OR (alternatives: SubPlan 3 or has
  • Rows Removed by Filter: 4198
4. 8.704 69.174 ↓ 4,392.0 4,392 1

Nested Loop Left Join (cost=132.69..304.76 rows=1 width=89) (actual time=2.362..69.174 rows=4,392 loops=1)

5. 5.090 47.393 ↓ 4,359.0 4,359 1

Nested Loop Left Join (cost=132.40..304.29 rows=1 width=81) (actual time=2.351..47.393 rows=4,359 loops=1)

6. 5.256 33.585 ↓ 4,359.0 4,359 1

Nested Loop Left Join (cost=132.26..304.12 rows=1 width=81) (actual time=2.344..33.585 rows=4,359 loops=1)

7. 3.116 17.517 ↓ 2,703.0 2,703 1

Nested Loop Left Join (cost=131.97..303.64 rows=1 width=65) (actual time=2.339..17.517 rows=2,703 loops=1)

8. 3.868 8.995 ↓ 2,703.0 2,703 1

Hash Join (cost=131.84..303.47 rows=1 width=52) (actual time=2.330..8.995 rows=2,703 loops=1)

  • Hash Cond: ((pu.product_id = p.id) AND (pu.id = p.reportbyunit_id))
9. 2.830 2.830 ↓ 1.0 3,078 1

Seq Scan on t_productunit pu (cost=0.00..125.68 rows=3,014 width=16) (actual time=0.010..2.830 rows=3,078 loops=1)

  • Filter: ((retired_date IS NULL) AND (ratio = '1'::numeric))
  • Rows Removed by Filter: 820
10. 1.146 2.297 ↑ 1.0 2,703 1

Hash (cost=90.90..90.90 rows=2,729 width=52) (actual time=2.297..2.297 rows=2,703 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 266kB
11. 1.151 1.151 ↑ 1.0 2,710 1

Seq Scan on t_product p (cost=0.00..90.90 rows=2,729 width=52) (actual time=0.004..1.151 rows=2,710 loops=1)

  • Filter: (retired_date IS NULL)
  • Rows Removed by Filter: 158
12. 5.406 5.406 ↑ 1.0 1 2,703

Index Scan using pk_t_producttype on t_producttype pt (cost=0.14..0.16 rows=1 width=29) (actual time=0.002..0.002 rows=1 loops=2,703)

  • Index Cond: (id = p.producttype_id)
13. 10.812 10.812 ↑ 1.0 2 2,703

Index Scan using idx_vendorproduct_product_id on t_vendorproduct vp (cost=0.28..0.47 rows=2 width=24) (actual time=0.003..0.004 rows=2 loops=2,703)

  • Index Cond: (product_id = p.id)
  • Filter: (retired_date IS NULL)
  • Rows Removed by Filter: 0
14. 8.718 8.718 ↑ 1.0 1 4,359

Index Scan using pk_t_vendor on t_vendor v (cost=0.14..0.17 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=4,359)

  • Index Cond: (id = vp.vendor_id)
  • Filter: (retired_date IS NULL)
15. 13.077 13.077 ↑ 1.0 1 4,359

Index Scan using uq_central_vendor_product_unit on t_vendorproductunit vpu (cost=0.28..0.46 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=4,359)

  • Index Cond: (vendorproduct_id = vp.id)
  • Filter: (ratio = '1'::numeric)
  • Rows Removed by Filter: 0
16. 13.176 13.176 ↑ 1.0 1 4,392

Index Scan using idx_puc_vendorproductunit_id on t_productunit_conversion puc (cost=0.28..0.40 rows=1 width=29) (actual time=0.003..0.003 rows=1 loops=4,392)

  • Index Cond: (vendorproductunit_id = vpu.id)
  • Filter: (retired_date IS NULL)
  • Rows Removed by Filter: 0
17.          

SubPlan (forNested Loop Left Join)

18. 3.544 14.698 ↓ 1.6 4,240 1

Hash Join (cost=414.22..719.98 rows=2,585 width=32) (actual time=8.595..14.698 rows=4,240 loops=1)

  • Hash Cond: (evpu_1.id = epuc_1.vendorproductunit_id)
19. 2.598 2.598 ↓ 1.0 4,528 1

Seq Scan on t_vendorproductunit evpu_1 (cost=0.00..208.54 rows=4,346 width=16) (actual time=0.011..2.598 rows=4,528 loops=1)

  • Filter: ((retired_date IS NULL) AND (ratio = '1'::numeric))
  • Rows Removed by Filter: 956
20. 1.456 8.556 ↓ 1.3 4,240 1

Hash (cost=373.46..373.46 rows=3,261 width=16) (actual time=8.556..8.556 rows=4,240 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 263kB
21. 2.417 7.100 ↓ 1.3 4,240 1

Hash Join (cost=163.35..373.46 rows=3,261 width=16) (actual time=2.792..7.100 rows=4,240 loops=1)

  • Hash Cond: (epuc_1.productunit_id = epu_1.id)
22. 1.918 1.918 ↓ 1.0 4,241 1

Seq Scan on t_productunit_conversion epuc_1 (cost=0.00..156.93 rows=4,213 width=16) (actual time=0.007..1.918 rows=4,241 loops=1)

  • Filter: ((retired_date IS NULL) AND (ratio <> '0'::numeric))
  • Rows Removed by Filter: 1688
23. 1.057 2.765 ↓ 1.0 3,078 1

Hash (cost=125.68..125.68 rows=3,014 width=16) (actual time=2.765..2.765 rows=3,078 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 177kB
24. 1.708 1.708 ↓ 1.0 3,078 1

Seq Scan on t_productunit epu_1 (cost=0.00..125.68 rows=3,014 width=16) (actual time=0.005..1.708 rows=3,078 loops=1)

  • Filter: ((retired_date IS NULL) AND (ratio = '1'::numeric))
  • Rows Removed by Filter: 820
25. 13.149 13.149 ↓ 0.0 0 4,383

Index Scan using idx_productunit_product_id on t_productunit (cost=0.28..8.30 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=4,383)

  • Index Cond: (product_id = p.id)
  • Filter: ((parentunit_id IS NOT NULL) AND (retired_date IS NULL) AND (conversion_quantity = '0'::numeric))
  • Rows Removed by Filter: 1
26. 0.000 0.000 ↓ 0.0 0

Seq Scan on t_productunit t_productunit_1 (cost=0.00..125.68 rows=817 width=8) (never executed)

  • Filter: ((parentunit_id IS NOT NULL) AND (retired_date IS NULL) AND (conversion_quantity = '0'::numeric))
27. 3.418 14.389 ↓ 1.6 4,240 1

Hash Join (cost=414.22..719.98 rows=2,585 width=32) (actual time=8.581..14.389 rows=4,240 loops=1)

  • Hash Cond: (evpu.id = epuc.vendorproductunit_id)
28. 2.408 2.408 ↓ 1.0 4,528 1

Seq Scan on t_vendorproductunit evpu (cost=0.00..208.54 rows=4,346 width=16) (actual time=0.007..2.408 rows=4,528 loops=1)

  • Filter: ((retired_date IS NULL) AND (ratio = '1'::numeric))
  • Rows Removed by Filter: 956
29. 1.495 8.563 ↓ 1.3 4,240 1

Hash (cost=373.46..373.46 rows=3,261 width=16) (actual time=8.563..8.563 rows=4,240 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 263kB
30. 2.449 7.068 ↓ 1.3 4,240 1

Hash Join (cost=163.35..373.46 rows=3,261 width=16) (actual time=2.730..7.068 rows=4,240 loops=1)

  • Hash Cond: (epuc.productunit_id = epu.id)
31. 1.918 1.918 ↓ 1.0 4,241 1

Seq Scan on t_productunit_conversion epuc (cost=0.00..156.93 rows=4,213 width=16) (actual time=0.006..1.918 rows=4,241 loops=1)

  • Filter: ((retired_date IS NULL) AND (ratio <> '0'::numeric))
  • Rows Removed by Filter: 1688
32. 1.030 2.701 ↓ 1.0 3,078 1

Hash (cost=125.68..125.68 rows=3,014 width=16) (actual time=2.701..2.701 rows=3,078 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 177kB
33. 1.671 1.671 ↓ 1.0 3,078 1

Seq Scan on t_productunit epu (cost=0.00..125.68 rows=3,014 width=16) (actual time=0.006..1.671 rows=3,078 loops=1)

  • Filter: ((retired_date IS NULL) AND (ratio = '1'::numeric))
  • Rows Removed by Filter: 820