explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2dMC

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 23,682.624 ↑ 4.6 5 1

Limit (cost=250,630.41..250,630.47 rows=23 width=1,969) (actual time=23,682.622..23,682.624 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, ((COALE
  • Buffers: shared hit=171463 read=83954
2. 0.052 23,682.623 ↑ 4.6 5 1

Sort (cost=250,630.41..250,630.47 rows=23 width=1,969) (actual time=23,682.622..23,682.623 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, (
  • Sort Key: product1_.name, product1_.m_product_id
  • Sort Method: quicksort Memory: 30kB
  • Buffers: shared hit=171463 read=83954
3. 0.181 23,682.571 ↑ 4.6 5 1

Nested Loop (cost=2.23..250,629.89 rows=23 width=1,969) (actual time=7,306.939..23,682.571 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_amountmi
  • Join Filter: ((product1_.c_uom_id)::text = (uom18_.c_uom_id)::text)
  • Rows Removed by Join Filter: 70
  • Buffers: shared hit=171460 read=83954
4. 2.459 23,660.775 ↑ 4.6 5 1

Nested Loop Left Join (cost=2.23..244,729.03 rows=23 width=1,961) (actual time=7,301.364..23,660.775 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, obretc
  • Join Filter: ((product1_.ad_image_id)::text = (adimage2_.ad_image_id)::text)
  • Rows Removed by Join Filter: 20420
  • Buffers: shared hit=170819 read=83906
5. 0.038 23,641.246 ↑ 4.6 5 1

Nested Loop (cost=2.23..242,875.00 rows=23 width=1,514) (actual time=7,285.504..23,641.246 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,
  • Join Filter: ((obretco_pr0_.m_product_id)::text = (pricingpro4_.m_product_id)::text)
  • Buffers: shared hit=170816 read=83515
6. 0.023 23,633.858 ↑ 7.0 5 1

Nested Loop (cost=1.67..242,802.09 rows=35 width=1,520) (actual time=7,282.772..23,633.858 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_.bestse
  • Buffers: shared hit=170808 read=83497
7. 15,443.518 23,633.810 ↑ 7.0 5 1

Nested Loop (cost=1.12..242,568.19 rows=35 width=1,411) (actual time=7,282.759..23,633.810 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_.
  • Buffers: shared hit=170783 read=83497
  • -> Index Scan using obretco_prol_product_act_pl on public.obretco_prol_product obretco_pr0_ (cost=0.56..80859.79 rows=24134 width=110) (actual time=3.436..15422.88
  • 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_.crea
  • Index Cond: ((obretco_pr0_.isactive = 'Y'::bpchar) AND ((obretco_pr0_.obretco_productlist_id)::text = '1365D84C84BC4BE6812B1CB40A810B97'::text))
  • Buffers: shared hit=2961 read=39656
8. 8,190.292 8,190.292 ↓ 0.0 0 42,218

Index Scan using m_product_key on public.m_product product1_ (cost=0.56..6.69 rows=1 width=1,301) (actual time=0.194..0.194 rows=0 loops=42,218)

  • Output: product1_.m_product_id, product1_.ad_client_id, product1_.ad_org_id, product1_.isactive, product1_.created, product1_.createdby, product1_.updated, pro
  • Index Cond: ((product1_.m_product_id)::text = (obretco_pr0_.m_product_id)::text)
  • Filter: ((product1_.isactive = 'Y'::bpchar) AND ((upper((product1_.value)::text) ~~ '%%MALONE%TA%%%'::text) OR (upper((product1_.upc)::text) ~~ '%%MALONE%TA%%%
  • Rows Removed by Filter: 1
  • Buffers: shared hit=167822 read=43841
9. 0.025 0.025 ↑ 1.0 1 5

Index Scan using m_product_key on public.m_product product_co8_ (cost=0.56..6.67 rows=1 width=109) (actual time=0.004..0.005 rows=1 loops=5)

  • Output: product_co8_.m_product_id, product_co8_.ad_client_id, product_co8_.ad_org_id, product_co8_.isactive, product_co8_.created, product_co8_.createdby, product_co
  • Index Cond: ((product_co8_.m_product_id)::text = (obretco_pr0_.m_product_id)::text)
  • Buffers: shared hit=25
10. 7.350 7.350 ↑ 1.0 1 5

Index Scan using m_productprice_act_plv_pro on public.m_productprice pricingpro4_ (cost=0.56..2.07 rows=1 width=60) (actual time=1.469..1.470 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_.isa
  • Index Cond: ((pricingpro4_.isactive = 'Y'::bpchar) AND ((pricingpro4_.m_pricelist_version_id)::text = '3A0A9F505FD34C2593140E326D1BF8E2'::text) AND ((pricingpro4_.m_produc
  • Buffers: shared hit=8 read=18
11. 3.617 17.070 ↑ 1.0 4,084 5

Materialize (cost=0.00..455.26 rows=4,084 width=529) (actual time=0.003..3.414 rows=4,084 loops=5)

  • Output: adimage2_.binarydata, adimage2_.ad_image_id
  • Buffers: shared hit=3 read=391
12. 13.453 13.453 ↑ 1.0 4,084 1

Seq Scan on public.ad_image adimage2_ (cost=0.00..434.84 rows=4,084 width=529) (actual time=0.008..13.453 rows=4,084 loops=1)

  • Output: adimage2_.binarydata, adimage2_.ad_image_id
  • Buffers: shared hit=3 read=391
13. 0.013 0.020 ↑ 1.0 15 5

Materialize (cost=0.00..1.22 rows=15 width=23) (actual time=0.002..0.004 rows=15 loops=5)

  • Output: uom18_.uomsymbol, uom18_.stdprecision, uom18_.c_uom_id
  • Buffers: shared hit=1
14. 0.007 0.007 ↑ 1.0 15 1

Seq Scan on public.c_uom uom18_ (cost=0.00..1.15 rows=15 width=23) (actual time=0.004..0.007 rows=15 loops=1)

  • Output: uom18_.uomsymbol, uom18_.stdprecision, uom18_.c_uom_id
  • Buffers: shared hit=1
15.          

SubPlan (forNested Loop)

16. 0.050 9.855 ↑ 1.0 1 5

HashAggregate (cost=189.71..189.72 rows=1 width=8) (actual time=1.971..1.971 rows=1 loops=5)

  • Output: sum(butismd_mi5_.available_stock), butismd_mi5_.nosica_store
  • Group Key: butismd_mi5_.nosica_store
  • Buffers: shared hit=518 read=15
17. 0.660 9.805 ↑ 1.0 1 5

Hash Join (cost=16.89..189.70 rows=1 width=8) (actual time=1.592..1.961 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=518 read=15
18. 3.935 3.935 ↑ 1.2 1,303 5

Seq Scan on public.m_warehouse warehouse6_ (cost=0.00..167.18 rows=1,500 width=14) (actual time=0.008..0.787 rows=1,303 loops=5)

  • Output: warehouse6_.m_warehouse_id, warehouse6_.ad_client_id, warehouse6_.ad_org_id, warehouse6_.isactive, warehouse6_.created, warehouse6_.createdby, warehouse6_.update
  • Filter: ((warehouse6_.em_butimwh_type_code = '2'::numeric) OR (warehouse6_.em_butimwh_type_code = '12'::numeric))
  • Rows Removed by Filter: 1107
  • Buffers: shared hit=489
19. 0.010 5.210 ↓ 2.0 2 5

Hash (cost=16.88..16.88 rows=1 width=22) (actual time=1.042..1.042 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=29 read=15
20. 0.035 5.200 ↓ 2.0 2 5

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

  • Output: butismd_mi5_.nosica_store, butismd_mi5_.available_stock, butismd_mi5_.nosica_warehouse
  • Buffers: shared hit=29 read=15
21. 0.750 0.750 ↑ 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.149..0.150 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_.cre
  • Index Cond: ((obretco_pr0_.obretco_productlist_id)::text = (obretco_pr7_.obretco_productlist_id)::text)
  • Buffers: shared hit=12 read=3
22. 4.415 4.415 ↓ 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.515..0.883 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_.creat
  • Index Cond: (((butismd_mi5_.nosica_store)::text = (obretco_pr7_.em_butintp_nosica_storeid)::text) AND ((butismd_mi5_.nosica_product)::text = (product1_.em_bu
  • Buffers: shared hit=17 read=12
23. 2.470 2.470 ↑ 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.494..0.494 rows=1 loops=5)

  • Output: eco.taxamt
  • Index Cond: ((eco.phieco_taxcategory_id)::text = (product_co8_.em_phieco_dea)::text)
  • Buffers: shared hit=14 read=6
24. 0.040 0.040 ↑ 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.008..0.008 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
25. 0.045 0.045 ↓ 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.009..0.009 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
26. 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
27. 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
28. 0.015 0.015 ↑ 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.003..0.003 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
29. 0.015 0.015 ↑ 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.003..0.003 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
30. 0.014 9.115 ↓ 0.0 0 5

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

  • Output: characteri16_.name
  • Buffers: shared hit=18 read=27
31. 0.015 7.245 ↓ 0.0 0 5

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

  • Output: productcha15_.m_ch_value_id
  • Buffers: shared hit=16 read=21
32. 0.805 0.805 ↑ 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.161..0.161 rows=1 loops=5)

  • Output: characteri17_.m_characteristic_id, characteri17_.ad_client_id, characteri17_.ad_org_id, characteri17_.isactive, characteri17_.created, characteri17_.createdby, c
  • Index Cond: ((characteri17_.name)::text = 'Produit.DispoPce'::text)
  • Buffers: shared hit=12 read=3
33. 6.425 6.425 ↓ 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=1.285..1.285 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,
  • Index Cond: (((productcha15_.m_product_id)::text = (product1_.m_product_id)::text) AND ((productcha15_.m_characteristic_id)::text = (characteri17_.m_characteristic_id)::
  • Buffers: shared hit=4 read=18
34. 1.856 1.856 ↑ 1.0 1 2

Index Scan using m_ch_value_key on public.m_ch_value characteri16_ (cost=0.42..8.44 rows=1 width=46) (actual time=0.928..0.928 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_
  • Index Cond: ((characteri16_.m_ch_value_id)::text = (productcha15_.m_ch_value_id)::text)
  • Buffers: shared hit=2 read=6