explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jlHD

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 5,373.677 ↓ 1.2 5 1

Limit (cost=251,328.53..251,328.54 rows=4 width=1,969) (actual time=5,373.677..5,373.677 rows=5 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) *
  • Buffers: shared hit=181815 read=39 dirtied=2
2. 0.102 5,373.674 ↓ 1.2 5 1

Sort (cost=251,328.53..251,328.54 rows=4 width=1,969) (actual time=5,373.674..5,373.674 rows=5 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'::numer
  • Sort Key: product1_.name, product1_.m_product_id
  • Sort Method: quicksort Memory: 30kB
  • Buffers: shared hit=181815 read=39 dirtied=2
3. 0.197 5,373.572 ↓ 1.2 5 1

Nested Loop (cost=1.80..251,328.49 rows=4 width=1,969) (actual time=5,309.573..5,373.572 rows=5 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'::nu
  • Join Filter: ((product1_.c_uom_id)::text = (uom18_.c_uom_id)::text)
  • Rows Removed by Join Filter: 70
  • Buffers: shared hit=181812 read=39 dirtied=2
4. 0.005 0.005 ↓ 1.1 15 1

Seq Scan on public.c_uom uom18_ (cost=0.00..1.14 rows=14 width=22) (actual time=0.003..0.005 rows=15 loops=1)

  • Output: uom18_.c_uom_id, uom18_.ad_client_id, uom18_.ad_org_id, uom18_.isactive, uom18_.created, uom18_.updated, uom18_.createdby, uom18_.updatedby, uom18_.x12de355, uom18_.uomsymbol, uom18_.name, uom18_.description,
  • Buffers: shared hit=1
5. 0.046 5,309.130 ↓ 1.2 5 15

Materialize (cost=1.80..250,201.68 rows=4 width=1,961) (actual time=13.194..353.942 rows=5 loops=15)

  • 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, obretc
  • Buffers: shared hit=181038 read=24
6. 2.846 5,309.084 ↓ 1.2 5 1

Nested Loop Left Join (cost=1.80..250,201.66 rows=4 width=1,961) (actual time=197.898..5,309.084 rows=5 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,
  • Join Filter: ((product1_.ad_image_id)::text = (adimage2_.ad_image_id)::text)
  • Rows Removed by Join Filter: 20570
  • Buffers: shared hit=181038 read=24
7. 0.037 5,296.878 ↓ 1.2 5 1

Nested Loop (cost=1.80..249,478.57 rows=4 width=1,514) (actual time=190.252..5,296.878 rows=5 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_.issaledisconti
  • Join Filter: ((obretco_pr0_.m_product_id)::text = (pricingpro4_.m_product_id)::text)
  • Buffers: shared hit=180611 read=24
8. 0.019 5,295.411 ↑ 1.0 5 1

Nested Loop (cost=1.12..249,464.37 rows=5 width=1,520) (actual time=189.650..5,295.411 rows=5 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_.issaledi
  • Buffers: shared hit=180595 read=10
9. 0.054 5,295.242 ↑ 1.0 5 1

Nested Loop (cost=0.56..249,430.27 rows=5 width=1,411) (actual time=189.612..5,295.242 rows=5 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_.is
  • Buffers: shared hit=180570 read=10
10. 5,294.768 5,294.768 ↑ 74.3 7 1

Seq Scan on public.m_product product1_ (cost=0.00..244,962.17 rows=520 width=1,301) (actual time=189.504..5,294.768 rows=7 loops=1)

  • Output: product1_.em_obretss_hassequence, product1_.em_obretss_hasownsequence, product1_.em_butintp_idproduit, product1_.em_phbutpg_giftcardsubtype, product1_.em_phinum_amountmax, produc
  • Filter: ((product1_.isactive = 'Y'::bpchar) AND ((upper((product1_.value)::text) ~~ '%%MALONE%TA%%%'::text) OR (upper((product1_.upc)::text) ~~ '%%MALONE%TA%%%'::text) OR (upper((product
  • Rows Removed by Filter: 2402478
  • Buffers: shared hit=180547
11. 0.420 0.420 ↑ 1.0 1 7

Index Scan using obretco_prol_product_un on public.obretco_prol_product obretco_pr0_ (cost=0.56..8.58 rows=1 width=110) (actual time=0.060..0.060 rows=1 loops=7)

  • 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
  • Index Cond: (((obretco_pr0_.obretco_productlist_id)::text = '1365D84C84BC4BE6812B1CB40A810B97'::text) AND ((obretco_pr0_.m_product_id)::text = (product1_.m_product_id)::text))
  • Filter: (obretco_pr0_.isactive = 'Y'::bpchar)
  • Buffers: shared hit=23 read=10
12. 0.150 0.150 ↑ 1.0 1 5

Index Scan using m_product_key on public.m_product product_co8_ (cost=0.56..6.81 rows=1 width=109) (actual time=0.029..0.030 rows=1 loops=5)

  • Output: product_co8_.em_phieco_dea, product_co8_.em_phieco_dea_qty, product_co8_.em_phieco_deee, product_co8_.em_phieco_deee_qty, product_co8_.m_product_id
  • Index Cond: ((product_co8_.m_product_id)::text = (obretco_pr0_.m_product_id)::text)
  • Buffers: shared hit=25
13. 1.430 1.430 ↑ 1.0 1 5

Index Scan using m_productprice_act_plv_pro on public.m_productprice pricingpro4_ (cost=0.69..2.83 rows=1 width=60) (actual time=0.285..0.286 rows=1 loops=5)

  • Output: pricingpro4_.m_productprice_id, pricingpro4_.m_pricelist_version_id, pricingpro4_.m_product_id, pricingpro4_.ad_client_id, pricingpro4_.ad_org_id, pricingpro4_.isactive, pricingpro4_.created
  • Index Cond: ((pricingpro4_.isactive = 'Y'::bpchar) AND ((pricingpro4_.m_pricelist_version_id)::text = '3A0A9F505FD34C2593140E326D1BF8E2'::text) AND ((pricingpro4_.m_product_id)::text = (product_co8_
  • Buffers: shared hit=16 read=14
14. 5.628 9.360 ↓ 1.0 4,114 5

Materialize (cost=0.00..488.26 rows=4,084 width=529) (actual time=0.006..1.872 rows=4,114 loops=5)

  • Output: adimage2_.binarydata, adimage2_.ad_image_id
  • Buffers: shared hit=427
15. 3.732 3.732 ↓ 1.0 4,114 1

Seq Scan on public.ad_image adimage2_ (cost=0.00..467.84 rows=4,084 width=529) (actual time=0.019..3.732 rows=4,114 loops=1)

  • Output: adimage2_.binarydata, adimage2_.ad_image_id
  • Buffers: shared hit=427
16.          

SubPlan (forNested Loop)

17. 0.050 63.595 ↑ 1.0 1 5

HashAggregate (cost=214.71..214.72 rows=1 width=8) (actual time=12.719..12.719 rows=1 loops=5)

  • Output: sum(butismd_mi5_.available_stock), butismd_mi5_.nosica_store
  • Group Key: butismd_mi5_.nosica_store
  • Buffers: shared hit=622 read=11 dirtied=2
18. 0.845 63.545 ↑ 1.0 1 5

Hash Join (cost=16.89..214.71 rows=1 width=8) (actual time=12.070..12.709 rows=1 loops=5)

  • Output: butismd_mi5_.nosica_store, butismd_mi5_.available_stock
  • Hash Cond: ((warehouse6_.em_butimwh_noswarehouse_id)::text = (butismd_mi5_.nosica_warehouse)::text)
  • Buffers: shared hit=622 read=11 dirtied=2
19. 5.860 5.860 ↑ 1.1 1,327 5

Seq Scan on public.m_warehouse warehouse6_ (cost=0.00..192.18 rows=1,501 width=14) (actual time=0.006..1.172 rows=1,327 loops=5)

  • Output: warehouse6_.m_warehouse_id, warehouse6_.ad_client_id, warehouse6_.ad_org_id, warehouse6_.isactive, warehouse6_.created, warehouse6_.createdby, warehouse6_.updated, warehouse6_.updatedby, warehou
  • Filter: ((warehouse6_.em_butimwh_type_code = '2'::numeric) OR (warehouse6_.em_butimwh_type_code = '12'::numeric))
  • Rows Removed by Filter: 1110
  • Buffers: shared hit=589
20. 0.025 56.840 ↓ 2.0 2 5

Hash (cost=16.88..16.88 rows=1 width=22) (actual time=11.368..11.368 rows=2 loops=5)

  • Output: butismd_mi5_.nosica_store, butismd_mi5_.available_stock, butismd_mi5_.nosica_warehouse
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=33 read=11 dirtied=2
21. 0.025 56.815 ↓ 2.0 2 5

Nested Loop (cost=0.83..16.88 rows=1 width=22) (actual time=3.210..11.363 rows=2 loops=5)

  • Output: butismd_mi5_.nosica_store, butismd_mi5_.available_stock, butismd_mi5_.nosica_warehouse
  • Buffers: shared hit=33 read=11 dirtied=2
22. 0.045 0.045 ↑ 1.0 1 5

Index Scan using obretco_pl_key on public.obretco_productlist obretco_pr7_ (cost=0.27..8.29 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=5)

  • Output: obretco_pr7_.obretco_productlist_id, obretco_pr7_.ad_client_id, obretco_pr7_.ad_org_id, obretco_pr7_.isactive, obretco_pr7_.created, obretco_pr7_.createdby, obretco_pr7_.updated, obr
  • Index Cond: ((obretco_pr0_.obretco_productlist_id)::text = (obretco_pr7_.obretco_productlist_id)::text)
  • Buffers: shared hit=15
23. 56.745 56.745 ↓ 2.0 2 5

Index Scan using butismd_ministock_unique on public.butismd_ministock butismd_mi5_ (cost=0.56..8.57 rows=1 width=22) (actual time=3.197..11.349 rows=2 loops=5)

  • Output: butismd_mi5_.butismd_ministock_id, butismd_mi5_.ad_client_id, butismd_mi5_.ad_org_id, butismd_mi5_.isactive, butismd_mi5_.created, butismd_mi5_.createdby, butismd_mi5_.updated, butis
  • Index Cond: (((butismd_mi5_.nosica_store)::text = (obretco_pr7_.em_butintp_nosica_storeid)::text) AND ((butismd_mi5_.nosica_product)::text = (product1_.em_butintp_idproduit)::text))
  • Buffers: shared hit=18 read=11 dirtied=2
24. 0.120 0.120 ↑ 1.0 1 5

Index Scan using phieco_taxcategory_key on public.phieco_taxcategory eco (cost=0.41..8.43 rows=1 width=6) (actual time=0.024..0.024 rows=1 loops=5)

  • Output: eco.taxamt
  • Index Cond: ((eco.phieco_taxcategory_id)::text = (product_co8_.em_phieco_dea)::text)
  • Buffers: shared hit=20
25. 0.045 0.045 ↑ 1.0 1 5

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

  • Output: eco_1.taxamt
  • Index Cond: ((eco_1.phieco_taxcategory_id)::text = (product_co8_.em_phieco_deee)::text)
  • Buffers: shared hit=20
26. 0.050 0.050 ↓ 0.0 0 5

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

  • Output: obpos_prin10_.template_path
  • Filter: ((product1_.em_obpgc_printtemplate_id)::text = (obpos_prin10_.obpos_print_template_id)::text)
  • Rows Removed by Filter: 40
  • Buffers: shared hit=10
27. 0.020 0.020 ↓ 0.0 0 5

Seq Scan on public.obpos_print_template obpos_prin11_ (cost=0.00..2.50 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=5)

  • Output: obpos_prin11_.print_type
  • Filter: ((product1_.em_obpgc_printtemplate_id)::text = (obpos_prin11_.obpos_print_template_id)::text)
  • Rows Removed by Filter: 40
  • Buffers: shared hit=10
28. 0.020 0.020 ↓ 0.0 0 5

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

  • Output: obpos_prin12_.printer
  • Filter: ((product1_.em_obpgc_printtemplate_id)::text = (obpos_prin12_.obpos_print_template_id)::text)
  • Rows Removed by Filter: 40
  • Buffers: shared hit=10
29. 0.020 0.020 ↑ 1.0 1 5

Index Scan using phieco_taxcategory_key on public.phieco_taxcategory phieco_tax13_ (cost=0.41..8.43 rows=1 width=6) (actual time=0.004..0.004 rows=1 loops=5)

  • Output: phieco_tax13_.taxamt
  • Index Cond: ((product1_.em_phieco_dea)::text = (phieco_tax13_.phieco_taxcategory_id)::text)
  • Buffers: shared hit=20
30. 0.020 0.020 ↑ 1.0 1 5

Index Scan using phieco_taxcategory_key on public.phieco_taxcategory phieco_tax14_ (cost=0.41..8.43 rows=1 width=6) (actual time=0.004..0.004 rows=1 loops=5)

  • Output: phieco_tax14_.taxamt
  • Index Cond: ((product1_.em_phieco_deee)::text = (phieco_tax14_.phieco_taxcategory_id)::text)
  • Buffers: shared hit=20
31. 0.012 0.350 ↓ 0.0 0 5

Nested Loop (cost=1.26..25.26 rows=1 width=13) (actual time=0.070..0.070 rows=0 loops=5)

  • Output: characteri16_.name
  • Buffers: shared hit=41 read=4
32. 0.020 0.270 ↓ 0.0 0 5

Nested Loop (cost=0.83..16.88 rows=1 width=33) (actual time=0.054..0.054 rows=0 loops=5)

  • Output: productcha15_.m_ch_value_id
  • Buffers: shared hit=33 read=4
33. 0.040 0.040 ↑ 1.0 1 5

Index Scan using em_butintp_ch_name_client on public.m_characteristic characteri17_ (cost=0.28..8.29 rows=1 width=33) (actual time=0.008..0.008 rows=1 loops=5)

  • Output: characteri17_.m_characteristic_id, characteri17_.ad_client_id, characteri17_.ad_org_id, characteri17_.isactive, characteri17_.created, characteri17_.createdby, characteri17_.updated, characteri1
  • Index Cond: ((characteri17_.name)::text = 'Produit.DispoPce'::text)
  • Buffers: shared hit=15
34. 0.210 0.210 ↓ 0.0 0 5

Index Scan using em_repdf_product_ch_rep on public.m_product_ch_value productcha15_ (cost=0.56..8.58 rows=1 width=66) (actual time=0.042..0.042 rows=0 loops=5)

  • Output: productcha15_.m_product_ch_value_id, productcha15_.ad_client_id, productcha15_.ad_org_id, productcha15_.isactive, productcha15_.created, productcha15_.createdby, productcha15_.updated, productch
  • Index Cond: (((productcha15_.m_product_id)::text = (product1_.m_product_id)::text) AND ((productcha15_.m_characteristic_id)::text = (characteri17_.m_characteristic_id)::text))
  • Buffers: shared hit=18 read=4
35. 0.068 0.068 ↑ 1.0 1 2

Index Scan using m_ch_value_key on public.m_ch_value characteri16_ (cost=0.42..8.37 rows=1 width=46) (actual time=0.034..0.034 rows=1 loops=2)

  • Output: characteri16_.m_ch_value_id, characteri16_.ad_client_id, characteri16_.ad_org_id, characteri16_.isactive, characteri16_.created, characteri16_.createdby, characteri16_.updated, characteri16_.updatedby
  • Index Cond: ((characteri16_.m_ch_value_id)::text = (productcha15_.m_ch_value_id)::text)
  • Buffers: shared hit=8