explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OJ3H

Settings
# exclusive inclusive rows x rows loops node
1. 2.897 296.822 ↑ 2.4 2,142 1

Unique (cost=32,166.49..33,647.64 rows=5,197 width=1,103) (actual time=293.794..296.822 rows=2,142 loops=1)

  • Buffers: shared hit=9133 read=3070
2. 2.982 293.925 ↑ 2.4 2,142 1

Sort (cost=32,166.49..32,179.49 rows=5,197 width=1,103) (actual time=293.792..293.925 rows=2,142 loops=1)

  • Sort Key: ingredient0_.id, codes1_.id, ingredient2_.id, codes3_.id, clientcode4_.id, ingredient5_.id, plant6_.id, ingredient7_.id, groupentri8_.id, ingredient9_.id, ingredient10_.id, nutrientpr11_.id, ingredient0_.created, ingredient0_.modified, ingredient0_.rowversion, ingredient0_.availabilityid, ingredient0_.currentdefinitionid, ingredient0_.currentpricehistoryid, ingredient0_.defaultnutrientprofileid, ingredient0_.name, ingredient0_.parent, ingredient0_.plantid, ingredient0_.syntheticnutrientprofileid, codes1_.code, codes1_.customerid, codes1_.description, codes1_.ingredientid, codes1_.integrationid, codes1_.integrationdeleted, codes1_.rowversion, ingredient2_.created, ingredient2_.deleted, ingredient2_.modified, ingredient2_.rowversion, ingredient2_.customerid, ingredient2_.availabilityid, ingredient2_.currentdefinitionid, ingredient2_.currentpricehistoryid, ingredient2_.defaultnutrientprofileid, ingredient2_.name, ingredient2_.parent, ingredient2_.plantid, ingredient2_.syntheticnutrientprofileid, codes3_.code, codes3_.customerid, codes3_.description, codes3_.ingredientid, codes3_.integrationid, codes3_.integrationdeleted, codes3_.rowversion, clientcode4_.created, clientcode4_.deleted, clientcode4_.modified, clientcode4_.rowversion, clientcode4_.customerid, clientcode4_.clientid, clientcode4_.code, clientcode4_.description, clientcode4_."primary", clientcode4_.ingredientid, ingredient5_.created, ingredient5_.customerid, ingredient5_.description, ingredient5_.ingredientid, ingredient5_.ingredientpricehistoryid, ingredient5_.logdate, ingredient5_.loguser, ingredient5_.modified, ingredient5_.plantid, ingredient5_.productionminimum, ingredient5_.rowversion, ingredient5_.version, plant6_.created, plant6_.deleted, plant6_.modified, plant6_.rowversion, plant6_.customerid, plant6_.name, plant6_.parent, ingredient7_.basepriceperunit, ingredient7_.created, ingredient7_.customerid, ingredient7_.ingredientid, ingredient7_.pricingunitid, ingredient7_.rowversion, groupentri8_.created, groupentri8_.customerid, groupentri8_.grouppriceperunit, groupentri8_.ingredientpricegroupdefinitionid, groupentri8_.ingredientpricehistoryid, groupentri8_.rowversion, ingredient9_.basepricemultiplier, ingredient9_.computed, ingredient9_.created, ingredient9_.fixedmarkup, ingredient9_.ingredientpricegroupid, ingredient9_.rowversion, ingredient10_.created, ingredient10_.deleted, ingredient10_.modified, ingredient10_.rowversion, ingredient10_.customerid, ingredient10_.currentdefinitionid, ingredient10_.name, nutrientpr11_.created, nutrientpr11_.currenthistoryid, nutrientpr11_.customerid, nutrientpr11_.ingredientid, nutrientpr11_.ingredientdefinitionid, nutrientpr11_.modified, nutrientpr11_.rowversion, nutrientpr11_.sourceintegration, nutrientpr11_.sourcecalculation
  • Sort Method: quicksort Memory: 1325kB
  • Buffers: shared hit=9133 read=3070
3. 1.139 290.943 ↑ 2.4 2,142 1

Hash Join (cost=9,483.15..29,339.75 rows=5,197 width=1,103) (actual time=287.051..290.943 rows=2,142 loops=1)

  • Buffers: shared hit=9133 read=3070
4. 1.090 289.784 ↑ 2.4 2,142 1

Hash Join (cost=9,481.92..29,321.03 rows=5,197 width=841) (actual time=287.026..289.784 rows=2,142 loops=1)

  • Buffers: shared hit=9132 read=3070
5. 97.416 288.652 ↑ 2.4 2,142 1

Hash Join (cost=9,480.59..29,302.2 rows=5,197 width=772) (actual time=286.971..288.652 rows=2,142 loops=1)

  • Buffers: shared hit=9131 read=3070
6. 85.153 85.153 ↓ 1.0 823,548 1

Seq Scan on ingredientpricehistorygroupentry groupentri8_ (cost=0..16,681.38 rows=823,538 width=50) (actual time=0.093..85.153 rows=823,548 loops=1)

  • Buffers: shared hit=5376 read=3070
7. 2.257 106.083 ↓ 1.0 1,866 1

Hash (cost=9,457.46..9,457.46 rows=1,850 width=722) (actual time=106.083..106.083 rows=1,866 loops=1)

  • Buffers: shared hit=3755
8. 1.160 103.826 ↓ 1.0 1,866 1

Hash Join (cost=1,978.01..9,457.46 rows=1,850 width=722) (actual time=98.909..103.826 rows=1,866 loops=1)

  • Buffers: shared hit=3755
9. 1.306 97.652 ↓ 1.0 1,866 1

Hash Join (cost=1,659.35..9,133.94 rows=1,850 width=646) (actual time=93.819..97.652 rows=1,866 loops=1)

  • Buffers: shared hit=3636
10. 1.022 96.297 ↓ 1.0 1,866 1

Hash Join (cost=1,656.66..9,126.18 rows=1,850 width=590) (actual time=93.764..96.297 rows=1,866 loops=1)

  • Buffers: shared hit=3635
11. 43.671 95.161 ↓ 1.0 1,866 1

Hash Join (cost=1,649.3..9,107.16 rows=1,850 width=504) (actual time=93.632..95.161 rows=1,866 loops=1)

  • Buffers: shared hit=3632
12. 29.218 29.218 ↓ 1.0 293,191 1

Seq Scan on ingredientpricehistory ingredient7_ (cost=0..5,983.82 rows=293,182 width=50) (actual time=0.014..29.218 rows=293,191 loops=1)

  • Buffers: shared hit=3052
13. 1.288 22.272 ↓ 1.0 1,866 1

Hash (cost=1,626.17..1,626.17 rows=1,850 width=454) (actual time=22.272..22.272 rows=1,866 loops=1)

  • Buffers: shared hit=580
14. 1.894 20.984 ↓ 1.0 1,866 1

Hash Join (cost=1,331.02..1,626.17 rows=1,850 width=454) (actual time=19.089..20.984 rows=1,866 loops=1)

  • Buffers: shared hit=580
15. 0.619 0.619 ↑ 1.0 8,807 1

Seq Scan on ingredientdefinition ingredient5_ (cost=0..242.24 rows=8,924 width=100) (actual time=0.015..0.619 rows=8,807 loops=1)

  • Buffers: shared hit=153
16. 1.038 18.471 ↓ 1.0 1,866 1

Hash (cost=1,307.9..1,307.9 rows=1,850 width=354) (actual time=18.471..18.471 rows=1,866 loops=1)

  • Buffers: shared hit=427
17. 1.332 17.433 ↓ 1.0 1,866 1

Hash Join (cost=1,180.08..1,307.9 rows=1,850 width=354) (actual time=15.144..17.433 rows=1,866 loops=1)

  • Buffers: shared hit=427
18. 0.996 0.996 ↑ 1.0 3,907 1

Seq Scan on ingredientintegrationcode codes3_ (cost=0..104.19 rows=3,907 width=70) (actual time=0.021..0.996 rows=3,907 loops=1)

  • Filter: (codes3_.integrationdeleted = 0)
  • Buffers: shared hit=51
19. 1.033 15.105 ↓ 1.0 1,866 1

Hash (cost=1,156.95..1,156.95 rows=1,850 width=284) (actual time=15.105..15.105 rows=1,866 loops=1)

  • Buffers: shared hit=376
20. 0.815 14.072 ↓ 1.0 1,866 1

Hash Join (cost=1,037.65..1,156.95 rows=1,850 width=284) (actual time=11.794..14.072 rows=1,866 loops=1)

  • Buffers: shared hit=376
21. 0.988 2.890 ↓ 1.0 1,866 1

Hash Join (cost=372.13..486.57 rows=1,850 width=177) (actual time=1.306..2.89 rows=1,866 loops=1)

  • Buffers: shared hit=106
22. 0.641 0.641 ↑ 1.0 3,907 1

Seq Scan on ingredientintegrationcode codes1_ (cost=0..104.19 rows=3,907 width=70) (actual time=0.029..0.641 rows=3,907 loops=1)

  • Filter: (codes1_.integrationdeleted = 0)
  • Buffers: shared hit=51
23. 0.604 1.261 ↓ 1.0 1,866 1

Hash (cost=349..349 rows=1,850 width=107) (actual time=1.261..1.261 rows=1,866 loops=1)

  • Buffers: shared hit=55
24. 0.550 0.657 ↓ 1.0 1,866 1

Bitmap Heap Scan on ingredient ingredient0_ (cost=51.25..349 rows=1,850 width=107) (actual time=0.118..0.657 rows=1,866 loops=1)

  • Buffers: shared hit=55
25. 0.107 0.107 ↓ 1.0 1,866 1

Bitmap Index Scan on idx_customerdeleted (cost=0..50.79 rows=1,850 width=0) (actual time=0.107..0.107 rows=1,866 loops=1)

  • Index Cond: ((ingredient0_.deleted = 0) AND (ingredient0_.customerid = 1))
  • Buffers: shared hit=9
26. 6.145 10.367 ↑ 1.0 17,579 1

Hash (cost=445.79..445.79 rows=17,579 width=107) (actual time=10.367..10.367 rows=17,579 loops=1)

  • Buffers: shared hit=270
27. 4.222 4.222 ↑ 1.0 17,579 1

Seq Scan on ingredient ingredient2_ (cost=0..445.79 rows=17,579 width=107) (actual time=0.009..4.222 rows=17,579 loops=1)

  • Buffers: shared hit=270
28. 0.045 0.114 ↑ 1.7 100 1

Hash (cost=5.28..5.28 rows=167 width=86) (actual time=0.114..0.114 rows=100 loops=1)

  • Buffers: shared hit=3
29. 0.069 0.069 ↑ 1.0 167 1

Seq Scan on ingredientclientcode clientcode4_ (cost=0..5.28 rows=167 width=86) (actual time=0.021..0.069 rows=167 loops=1)

  • Filter: (clientcode4_.deleted = 0)
  • Buffers: shared hit=3
30. 0.028 0.049 ↑ 1.0 75 1

Hash (cost=1.75..1.75 rows=75 width=56) (actual time=0.049..0.049 rows=75 loops=1)

  • Buffers: shared hit=1
31. 0.021 0.021 ↑ 1.0 75 1

Seq Scan on plant plant6_ (cost=0..1.75 rows=75 width=56) (actual time=0.012..0.021 rows=75 loops=1)

  • Buffers: shared hit=1
32. 3.067 5.014 ↓ 1.0 8,877 1

Hash (cost=207.74..207.74 rows=8,874 width=76) (actual time=5.014..5.014 rows=8,877 loops=1)

  • Buffers: shared hit=119
33. 1.947 1.947 ↓ 1.0 8,877 1

Seq Scan on nutrientprofile nutrientpr11_ (cost=0..207.74 rows=8,874 width=76) (actual time=0.01..1.947 rows=8,877 loops=1)

  • Buffers: shared hit=119
34. 0.020 0.042 ↑ 1.0 15 1

Hash (cost=1.15..1.15 rows=15 width=69) (actual time=0.042..0.042 rows=15 loops=1)

  • Buffers: shared hit=1
35. 0.022 0.022 ↑ 1.0 15 1

Seq Scan on ingredientpricegroupdefinition ingredient9_ (cost=0..1.15 rows=15 width=69) (actual time=0.019..0.022 rows=15 loops=1)

  • Buffers: shared hit=1
36. 0.009 0.020 ↑ 1.0 10 1

Hash (cost=1.1..1.1 rows=10 width=198) (actual time=0.02..0.02 rows=10 loops=1)

  • Buffers: shared hit=1
37. 0.011 0.011 ↑ 1.0 10 1

Seq Scan on ingredientpricegroup ingredient10_ (cost=0..1.1 rows=10 width=198) (actual time=0.009..0.011 rows=10 loops=1)

  • Buffers: shared hit=1