explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AmME

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 5,175.085 ↓ 1.2 5 1

Limit (cost=251,137.98..251,137.99 rows=4 width=1,969) (actual time=5,175.084..5,175.085 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) * product_co8_.em_phieco_de
  • Buffers: shared hit=181797 read=57 dirtied=2
2. 0.064 5,175.081 ↓ 1.2 5 1

Sort (cost=251,137.98..251,137.99 rows=4 width=1,969) (actual time=5,175.081..5,175.081 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) * product_co8_.em_phi
  • Sort Key: product1_.name, product1_.m_product_id
  • Sort Method: quicksort Memory: 30kB
  • Buffers: shared hit=181797 read=57 dirtied=2
3. 0.155 5,175.017 ↓ 1.2 5 1

Nested Loop (cost=1.80..251,137.94 rows=4 width=1,969) (actual time=5,165.993..5,175.017 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) * product_co8_.em_
  • Join Filter: ((product1_.c_uom_id)::text = (uom18_.c_uom_id)::text)
  • Rows Removed by Join Filter: 70
  • Buffers: shared hit=181794 read=57 dirtied=2
4. 0.007 0.007 ↓ 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.007 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, uom18_.stdprecision, uom
  • Buffers: shared hit=1
5. 0.052 5,165.490 ↓ 1.2 5 15

Materialize (cost=1.80..250,011.13 rows=4 width=1,961) (actual time=10.369..344.366 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, obretco_pr0_.isactive, obretco_
  • Buffers: shared hit=181028 read=34
6. 2.939 5,165.438 ↓ 1.2 5 1

Nested Loop Left Join (cost=1.80..250,011.11 rows=4 width=1,961) (actual time=155.515..5,165.438 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, obretco_pr0_.isactive, ob
  • Join Filter: ((product1_.ad_image_id)::text = (adimage2_.ad_image_id)::text)
  • Rows Removed by Join Filter: 20870
  • Buffers: shared hit=181028 read=34
7. 0.044 5,152.714 ↓ 1.2 5 1

Nested Loop (cost=1.80..249,288.02 rows=4 width=1,514) (actual time=147.180..5,152.714 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, obretco_pr0_.isacti
  • Join Filter: ((obretco_pr0_.m_product_id)::text = (pricingpro4_.m_product_id)::text)
  • Buffers: shared hit=180601 read=34
8. 0.020 5,150.135 ↑ 1.0 5 1

Nested Loop (cost=1.12..249,273.79 rows=5 width=1,520) (actual time=147.049..5,150.135 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, obretco_pr0_.
  • Buffers: shared hit=180588 read=17
9. 0.049 5,149.935 ↑ 1.0 5 1

Nested Loop (cost=0.56..249,239.66 rows=5 width=1,411) (actual time=146.997..5,149.935 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, obretco
  • Buffers: shared hit=180563 read=17
10. 5,149.410 5,149.410 ↑ 74.1 7 1

Seq Scan on public.m_product product1_ (cost=0.00..244,780.15 rows=519 width=1,301) (actual time=146.868..5,149.410 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, product1_.em_phinum_amountmin,
  • Filter: ((product1_.isactive = 'Y'::bpchar) AND ((upper((product1_.value)::text) ~~ '%%MALONE%TA%%%'::text) OR (upper((product1_.upc)::text) ~~ '%%MALONE%TA%%%'::text) OR (upper((product1_.name)::text) ~~ '%%MAL
  • Rows Removed by Filter: 2403357
  • Buffers: shared hit=180547
11. 0.476 0.476 ↑ 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.068..0.068 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, obretco_pr0_.updatedby,
  • 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=16 read=17
12. 0.180 0.180 ↑ 1.0 1 5

Index Scan using m_product_key on public.m_product product_co8_ (cost=0.56..6.82 rows=1 width=109) (actual time=0.036..0.036 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. 2.535 2.535 ↑ 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.506..0.507 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, pricingpro4_.createdby,
  • Index Cond: ((pricingpro4_.isactive = 'Y'::bpchar) AND ((pricingpro4_.m_pricelist_version_id)::text = '3A0A9F505FD34C2593140E326D1BF8E2'::text) AND ((pricingpro4_.m_product_id)::text = (product_co8_.m_product_id)::text))
  • Buffers: shared hit=13 read=17
14. 5.405 9.785 ↓ 1.0 4,174 5

Materialize (cost=0.00..488.26 rows=4,084 width=529) (actual time=0.005..1.957 rows=4,174 loops=5)

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

Seq Scan on public.ad_image adimage2_ (cost=0.00..467.84 rows=4,084 width=529) (actual time=0.014..4.380 rows=4,174 loops=1)

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

SubPlan (forNested Loop)

17. 0.035 8.675 ↑ 1.0 1 5

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

  • Output: sum(butismd_mi5_.available_stock), butismd_mi5_.nosica_store
  • Group Key: butismd_mi5_.nosica_store
  • Buffers: shared hit=621 read=12 dirtied=2
18. 1.095 8.640 ↑ 1.0 1 5

Hash Join (cost=16.89..214.71 rows=1 width=8) (actual time=1.234..1.728 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=621 read=12 dirtied=2
19. 5.565 5.565 ↑ 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.007..1.113 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, warehouse6_.value, warehouse6_.n
  • Filter: ((warehouse6_.em_butimwh_type_code = '2'::numeric) OR (warehouse6_.em_butimwh_type_code = '12'::numeric))
  • Rows Removed by Filter: 1111
  • Buffers: shared hit=589
20. 0.020 1.980 ↓ 2.0 2 5

Hash (cost=16.88..16.88 rows=1 width=22) (actual time=0.396..0.396 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=32 read=12 dirtied=2
21. 0.020 1.960 ↓ 2.0 2 5

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

  • Output: butismd_mi5_.nosica_store, butismd_mi5_.available_stock, butismd_mi5_.nosica_warehouse
  • Buffers: shared hit=32 read=12 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, obretco_pr7_.updatedby, obre
  • Index Cond: ((obretco_pr0_.obretco_productlist_id)::text = (obretco_pr7_.obretco_productlist_id)::text)
  • Buffers: shared hit=15
23. 1.895 1.895 ↓ 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=0.339..0.379 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, butismd_mi5_.updatedby, butism
  • 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=17 read=12 dirtied=2
24. 0.125 0.125 ↑ 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.025..0.025 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.035 0.035 ↑ 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.007..0.007 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.040 0.040 ↓ 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.008..0.008 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.025 0.025 ↑ 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.005..0.005 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.014 0.405 ↓ 0.0 0 5

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

  • Output: characteri16_.name
  • Buffers: shared hit=34 read=11
32. 0.010 0.315 ↓ 0.0 0 5

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

  • Output: productcha15_.m_ch_value_id
  • Buffers: shared hit=26 read=11
33. 0.050 0.050 ↑ 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.010..0.010 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, characteri17_.updatedby, characteri1
  • Index Cond: ((characteri17_.name)::text = 'Produit.DispoPce'::text)
  • Buffers: shared hit=15
34. 0.255 0.255 ↓ 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.051..0.051 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, productcha15_.updatedby, 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=11 read=11
35. 0.076 0.076 ↑ 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.037..0.038 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, characteri16_.m_charact
  • Index Cond: ((characteri16_.m_ch_value_id)::text = (productcha15_.m_ch_value_id)::text)
  • Buffers: shared hit=8
Planning time : 15.380 ms
Execution time : 5,177.436 ms