explain.depesz.com

PostgreSQL's explain analyze made readable

Result: i1CH

Settings
# exclusive inclusive rows x rows loops node
1. 0.043 84,294.400 ↑ 1.1 29 1

Limit (cost=2.91..145,780.19 rows=31 width=1,542) (actual time=39,382.698..84,294.400 rows=29 loops=1)

  • Output: product1_.em_obretss_hassequence, product1_.em_obretss_hasownsequence, ((SubPlan 1)), product1_.em_phbutpg_giftcardsubtype, product1_.em_phinum_amountmax, product1_.em_phinum_amountmin, ((COALESCE((SubPlan 2), '0'::numeric) * product_co6_.em_phieco_de
  • Buffers: shared hit=13620357 read=219252
2. 1.886 84,294.357 ↑ 19.6 29 1

Nested Loop (cost=2.91..2,671,018.80 rows=568 width=1,542) (actual time=39,382.695..84,294.357 rows=29 loops=1)

  • Output: product1_.em_obretss_hassequence, product1_.em_obretss_hasownsequence, (SubPlan 1), product1_.em_phbutpg_giftcardsubtype, product1_.em_phinum_amountmax, product1_.em_phinum_amountmin, (COALESCE((SubPlan 2), '0'::numeric) * product_co6_.em_phieco
  • Join Filter: ((product1_.c_uom_id)::text = (uom16_.c_uom_id)::text)
  • Rows Removed by Join Filter: 406
  • Buffers: shared hit=13620357 read=219252
3. 0.260 84,087.441 ↑ 19.6 29 1

Nested Loop (cost=2.91..2,510,415.28 rows=568 width=1,534) (actual time=39,361.920..84,087.441 rows=29 loops=1)

  • Output: obretco_pr0_.obretco_productlist_id, obretco_pr0_.em_butimas_productfollowed, obretco_pr0_.em_obpr_ranking_id, obretco_pr0_.em_obrpr_relevance, obretco_pr0_.bestseller, obretco_pr0_.issalediscontinued, obretco_pr0_.isactive, obretco_pr0_.m
  • Join Filter: ((obretco_pr0_.m_product_id)::text = (product_co6_.m_product_id)::text)
  • Buffers: shared hit=13616744 read=219065
4. 0.336 84,086.572 ↑ 19.6 29 1

Nested Loop (cost=2.36..2,509,887.49 rows=568 width=1,524) (actual time=39,361.878..84,086.572 rows=29 loops=1)

  • Output: obretco_pr0_.obretco_productlist_id, obretco_pr0_.em_butimas_productfollowed, obretco_pr0_.em_obpr_ranking_id, obretco_pr0_.em_obrpr_relevance, obretco_pr0_.bestseller, obretco_pr0_.issalediscontinued, obretco_pr0_.isactive, obretco_
  • Join Filter: ((obretco_pr0_.m_product_id)::text = (pricingpro2_.m_product_id)::text)
  • Buffers: shared hit=13616598 read=219065
5. 25.065 84,022.336 ↑ 15.6 45 1

Nested Loop (cost=1.67..2,507,911.67 rows=703 width=1,464) (actual time=39,354.875..84,022.336 rows=45 loops=1)

  • Output: obretco_pr0_.obretco_productlist_id, obretco_pr0_.em_butimas_productfollowed, obretco_pr0_.em_obpr_ranking_id, obretco_pr0_.em_obrpr_relevance, obretco_pr0_.bestseller, obretco_pr0_.issalediscontinued, obretco_pr0_.isactive, ob
  • Join Filter: ((product1_.m_product_id)::text = (obretco_pr0_.m_product_id)::text)
  • Buffers: shared hit=13616442 read=218964
6. 1,240.131 83,032.391 ↑ 1.2 48,244 1

Nested Loop (cost=1.11..2,393,601.52 rows=58,133 width=1,354) (actual time=43.069..83,032.391 rows=48,244 loops=1)

  • Output: product1_.em_obretss_hassequence, product1_.em_obretss_hasownsequence, product1_.em_butintp_idproduit, product1_.em_phbutpg_giftcardsubtype, product1_.em_phinum_amountmax, product1_.em_phinum_amountmin, product1_.em_phiec
  • Buffers: shared hit=13424857 read=217453
7. 3,981.060 3,981.060 ↓ 1.0 2,431,600 1

Index Only Scan using m_product_name_id on public.m_product product18_ (cost=0.56..163,221.07 rows=2,374,710 width=53) (actual time=4.042..3,981.060 rows=2,431,600 loops=1)

  • Output: product18_.name, product18_.m_product_id
  • Heap Fetches: 29662
  • Buffers: shared hit=1409146 read=29442
8. 77,811.200 77,811.200 ↓ 0.0 0 2,431,600

Index Scan using m_product_key on public.m_product product1_ (cost=0.56..0.93 rows=1 width=1,301) (actual time=0.032..0.032 rows=0 loops=2,431,600)

  • Output: product1_.em_obretss_hassequence, product1_.em_obretss_hasownsequence, product1_.em_butintp_idproduit, product1_.em_phbutpg_giftcardsubtype, product1_.em_phinum_amountmax, product1_.em_phinum_amountmin, product1_.em
  • Index Cond: ((product1_.m_product_id)::text = (product18_.m_product_id)::text)
  • Filter: ((product1_.isactive = 'Y'::bpchar) AND ((upper((product1_.upc)::text) ~~ '%1001%'::text) OR (upper((product1_.name)::text) ~~ '%1001%'::text)))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=12015711 read=188011
9. 964.880 964.880 ↓ 0.0 0 48,244

Index Scan using obretco_prol_product_un on public.obretco_prol_product obretco_pr0_ (cost=0.56..1.95 rows=1 width=110) (actual time=0.020..0.020 rows=0 loops=48,244)

  • Output: obretco_pr0_.obretco_prol_product_id, obretco_pr0_.ad_client_id, obretco_pr0_.ad_org_id, obretco_pr0_.isactive, obretco_pr0_.created, obretco_pr0_.createdby, obretco_pr0_.updated, obretco_pr0_.updatedby, obretco_pr0_.obre
  • Index Cond: (((obretco_pr0_.obretco_productlist_id)::text = 'BC2BF31849F145FD888B6B69D0DA4CF0'::text) AND ((obretco_pr0_.m_product_id)::text = (product18_.m_product_id)::text))
  • Filter: (obretco_pr0_.isactive = 'Y'::bpchar)
  • Buffers: shared hit=191585 read=1511
10. 63.900 63.900 ↑ 1.0 1 45

Index Scan using m_productprice_act_plv_pro on public.m_productprice pricingpro2_ (cost=0.69..2.80 rows=1 width=60) (actual time=1.420..1.420 rows=1 loops=45)

  • Output: pricingpro2_.m_productprice_id, pricingpro2_.m_pricelist_version_id, pricingpro2_.m_product_id, pricingpro2_.ad_client_id, pricingpro2_.ad_org_id, pricingpro2_.isactive, pricingpro2_.created, pricingpro2_.createdby, pricingpro2
  • Index Cond: ((pricingpro2_.isactive = 'Y'::bpchar) AND ((pricingpro2_.m_pricelist_version_id)::text = '53720EAC40B841B5BFCE359A49B0C364'::text) AND ((pricingpro2_.m_product_id)::text = (product18_.m_product_id)::text))
  • Buffers: shared hit=156 read=101
11. 0.609 0.609 ↑ 1.0 1 29

Index Scan using m_product_key on public.m_product product_co6_ (cost=0.56..0.92 rows=1 width=109) (actual time=0.020..0.021 rows=1 loops=29)

  • Output: product_co6_.em_phieco_dea, product_co6_.em_phieco_dea_qty, product_co6_.em_phieco_deee, product_co6_.em_phieco_deee_qty, product_co6_.m_product_id
  • Index Cond: ((product_co6_.m_product_id)::text = (product18_.m_product_id)::text)
  • Buffers: shared hit=146
12. 0.155 0.174 ↑ 1.0 15 29

Materialize (cost=0.00..1.22 rows=15 width=22) (actual time=0.002..0.006 rows=15 loops=29)

  • Output: uom16_.uomsymbol, uom16_.stdprecision, uom16_.c_uom_id
  • Buffers: shared hit=1
13. 0.019 0.019 ↑ 1.0 15 1

Seq Scan on public.c_uom uom16_ (cost=0.00..1.15 rows=15 width=22) (actual time=0.010..0.019 rows=15 loops=1)

  • Output: uom16_.uomsymbol, uom16_.stdprecision, uom16_.c_uom_id
  • Buffers: shared hit=1
14.          

SubPlan (forNested Loop)

15. 0.464 54.636 ↑ 1.0 1 29

HashAggregate (cost=216.03..216.04 rows=1 width=8) (actual time=1.884..1.884 rows=1 loops=29)

  • Output: sum(butismd_mi3_.available_stock), butismd_mi3_.nosica_store
  • Group Key: butismd_mi3_.nosica_store
  • Buffers: shared hit=2973 read=61
16. 5.543 54.172 ↑ 1.0 1 29

Hash Join (cost=16.89..216.02 rows=1 width=8) (actual time=0.945..1.868 rows=1 loops=29)

  • Output: butismd_mi3_.nosica_store, butismd_mi3_.available_stock
  • Hash Cond: ((warehouse4_.em_butimwh_noswarehouse_id)::text = (butismd_mi3_.nosica_warehouse)::text)
  • Buffers: shared hit=2973 read=61
17. 42.104 42.104 ↓ 1.1 1,681 19

Seq Scan on public.m_warehouse warehouse4_ (cost=0.00..193.32 rows=1,548 width=14) (actual time=0.028..2.216 rows=1,681 loops=19)

  • Output: warehouse4_.m_warehouse_id, warehouse4_.ad_client_id, warehouse4_.ad_org_id, warehouse4_.isactive, warehouse4_.created, warehouse4_.createdby, warehouse4_.updated, warehouse4_.updatedby, warehouse4_.value, warehouse4_.name, w
  • Filter: ((warehouse4_.em_butimwh_type_code = '2'::numeric) OR (warehouse4_.em_butimwh_type_code = '12'::numeric))
  • Rows Removed by Filter: 1420
  • Buffers: shared hit=2784 read=9
18. 0.174 6.525 ↑ 1.0 1 29

Hash (cost=16.88..16.88 rows=1 width=22) (actual time=0.225..0.225 rows=1 loops=29)

  • Output: butismd_mi3_.nosica_store, butismd_mi3_.available_stock, butismd_mi3_.nosica_warehouse
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=189 read=52
19. 0.290 6.351 ↑ 1.0 1 29

Nested Loop (cost=0.83..16.88 rows=1 width=22) (actual time=0.207..0.219 rows=1 loops=29)

  • Output: butismd_mi3_.nosica_store, butismd_mi3_.available_stock, butismd_mi3_.nosica_warehouse
  • Buffers: shared hit=189 read=52
20. 2.320 2.320 ↑ 1.0 1 29

Index Scan using obretco_pl_key on public.obretco_productlist obretco_pr5_ (cost=0.27..8.29 rows=1 width=4) (actual time=0.079..0.080 rows=1 loops=29)

  • Output: obretco_pr5_.obretco_productlist_id, obretco_pr5_.ad_client_id, obretco_pr5_.ad_org_id, obretco_pr5_.isactive, obretco_pr5_.created, obretco_pr5_.createdby, obretco_pr5_.updated, obretco_pr5_.updatedby, obretco_pr
  • Index Cond: ((obretco_pr0_.obretco_productlist_id)::text = (obretco_pr5_.obretco_productlist_id)::text)
  • Buffers: shared hit=85 read=2
21. 3.741 3.741 ↑ 1.0 1 29

Index Scan using butismd_ministock_unique on public.butismd_ministock butismd_mi3_ (cost=0.56..8.57 rows=1 width=22) (actual time=0.118..0.129 rows=1 loops=29)

  • Output: butismd_mi3_.butismd_ministock_id, butismd_mi3_.ad_client_id, butismd_mi3_.ad_org_id, butismd_mi3_.isactive, butismd_mi3_.created, butismd_mi3_.createdby, butismd_mi3_.updated, butismd_mi3_.updatedby, butismd_mi3_
  • Index Cond: (((butismd_mi3_.nosica_store)::text = (obretco_pr5_.em_butintp_nosica_storeid)::text) AND ((butismd_mi3_.nosica_product)::text = (product1_.em_butintp_idproduit)::text))
  • Buffers: shared hit=104 read=50
22. 7.801 7.801 ↓ 0.0 0 29

Index Scan using phieco_taxcategory_key on public.phieco_taxcategory eco (cost=0.41..8.43 rows=1 width=6) (actual time=0.268..0.269 rows=0 loops=29)

  • Output: eco.taxamt
  • Index Cond: ((eco.phieco_taxcategory_id)::text = (product_co6_.em_phieco_dea)::text)
  • Buffers: shared hit=24 read=8
23. 17.980 17.980 ↑ 1.0 1 29

Index Scan using phieco_taxcategory_key on public.phieco_taxcategory eco_1 (cost=0.41..8.43 rows=1 width=6) (actual time=0.570..0.620 rows=1 loops=29)

  • Output: eco_1.taxamt
  • Index Cond: ((eco_1.phieco_taxcategory_id)::text = (product_co6_.em_phieco_deee)::text)
  • Buffers: shared hit=102 read=15
24. 0.377 0.377 ↓ 0.0 0 29

Seq Scan on public.obpos_print_template obpos_prin8_ (cost=0.00..2.50 rows=1 width=42) (actual time=0.013..0.013 rows=0 loops=29)

  • Output: obpos_prin8_.template_path
  • Filter: ((product1_.em_obpgc_printtemplate_id)::text = (obpos_prin8_.obpos_print_template_id)::text)
  • Rows Removed by Filter: 40
  • Buffers: shared hit=56 read=2
25. 0.203 0.203 ↓ 0.0 0 29

Seq Scan on public.obpos_print_template obpos_prin9_ (cost=0.00..2.50 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=29)

  • Output: obpos_prin9_.print_type
  • Filter: ((product1_.em_obpgc_printtemplate_id)::text = (obpos_prin9_.obpos_print_template_id)::text)
  • Rows Removed by Filter: 40
  • Buffers: shared hit=58
26. 0.203 0.203 ↓ 0.0 0 29

Seq Scan on public.obpos_print_template obpos_prin10_ (cost=0.00..2.50 rows=1 width=5) (actual time=0.007..0.007 rows=0 loops=29)

  • Output: obpos_prin10_.printer
  • Filter: ((product1_.em_obpgc_printtemplate_id)::text = (obpos_prin10_.obpos_print_template_id)::text)
  • Rows Removed by Filter: 40
  • Buffers: shared hit=58
27. 0.087 0.087 ↓ 0.0 0 29

Index Scan using phieco_taxcategory_key on public.phieco_taxcategory phieco_tax11_ (cost=0.41..8.43 rows=1 width=6) (actual time=0.003..0.003 rows=0 loops=29)

  • Output: phieco_tax11_.taxamt
  • Index Cond: ((product1_.em_phieco_dea)::text = (phieco_tax11_.phieco_taxcategory_id)::text)
  • Buffers: shared hit=32
28. 0.261 0.261 ↑ 1.0 1 29

Index Scan using phieco_taxcategory_key on public.phieco_taxcategory phieco_tax12_ (cost=0.41..8.43 rows=1 width=6) (actual time=0.009..0.009 rows=1 loops=29)

  • Output: phieco_tax12_.taxamt
  • Index Cond: ((product1_.em_phieco_deee)::text = (phieco_tax12_.phieco_taxcategory_id)::text)
  • Buffers: shared hit=117
29. 0.223 123.308 ↑ 1.0 1 29

Nested Loop (cost=1.26..25.26 rows=1 width=13) (actual time=4.251..4.252 rows=1 loops=29)

  • Output: characteri14_.name
  • Buffers: shared hit=192 read=101
30. 0.174 108.721 ↑ 1.0 1 29

Nested Loop (cost=0.83..16.88 rows=1 width=33) (actual time=3.748..3.749 rows=1 loops=29)

  • Output: productcha13_.m_ch_value_id
  • Buffers: shared hit=131 read=90
31. 2.407 2.407 ↑ 1.0 1 29

Index Scan using em_butintp_ch_name_client on public.m_characteristic characteri15_ (cost=0.28..8.29 rows=1 width=33) (actual time=0.083..0.083 rows=1 loops=29)

  • Output: characteri15_.m_characteristic_id, characteri15_.ad_client_id, characteri15_.ad_org_id, characteri15_.isactive, characteri15_.created, characteri15_.createdby, characteri15_.updated, characteri15_.updatedby, characteri15_.nam
  • Index Cond: ((characteri15_.name)::text = 'Produit.DispoPce'::text)
  • Buffers: shared hit=85 read=2
32. 106.140 106.140 ↑ 1.0 1 29

Index Scan using em_repdf_product_ch_rep on public.m_product_ch_value productcha13_ (cost=0.56..8.58 rows=1 width=66) (actual time=3.660..3.660 rows=1 loops=29)

  • Output: productcha13_.m_product_ch_value_id, productcha13_.ad_client_id, productcha13_.ad_org_id, productcha13_.isactive, productcha13_.created, productcha13_.createdby, productcha13_.updated, productcha13_.updatedby, productcha13_.m
  • Index Cond: (((productcha13_.m_product_id)::text = (product1_.m_product_id)::text) AND ((productcha13_.m_characteristic_id)::text = (characteri15_.m_characteristic_id)::text))
  • Buffers: shared hit=46 read=88
33. 14.364 14.364 ↑ 1.0 1 18

Index Scan using m_ch_value_key on public.m_ch_value characteri14_ (cost=0.42..8.37 rows=1 width=46) (actual time=0.798..0.798 rows=1 loops=18)

  • Output: characteri14_.m_ch_value_id, characteri14_.ad_client_id, characteri14_.ad_org_id, characteri14_.isactive, characteri14_.created, characteri14_.createdby, characteri14_.updated, characteri14_.updatedby, characteri14_.m_characteristi
  • Index Cond: ((characteri14_.m_ch_value_id)::text = (productcha13_.m_ch_value_id)::text)
  • Buffers: shared hit=61 read=11