explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GTCZ

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

Limit (cost=251,328.52..251,328.53 rows=4 width=1,969) (actual time=21,353.976..21,353.977 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=8898 read=172956
2. 0.130 21,353.974 ↓ 1.2 5 1

Sort (cost=251,328.52..251,328.53 rows=4 width=1,969) (actual time=21,353.973..21,353.974 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=8898 read=172956
3. 0.351 21,353.844 ↓ 1.2 5 1

Nested Loop (cost=1.80..251,328.48 rows=4 width=1,969) (actual time=21,282.351..21,353.844 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=8895 read=172956
4. 0.008 0.008 ↓ 1.1 15 1

Seq Scan on public.c_uom uom18_ (cost=0.00..1.14 rows=14 width=22) (actual time=0.004..0.008 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.080 21,258.555 ↓ 1.2 5 15

Materialize (cost=1.80..250,201.67 rows=4 width=1,961) (actual time=35.252..1,417.237 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=8160 read=172902
6. 5.458 21,258.475 ↓ 1.2 5 1

Nested Loop Left Join (cost=1.80..250,201.65 rows=4 width=1,961) (actual time=528.757..21,258.475 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: 20420
  • Buffers: shared hit=8160 read=172902
7. 0.078 21,199.792 ↓ 1.2 5 1

Nested Loop (cost=1.80..249,478.56 rows=4 width=1,514) (actual time=477.935..21,199.792 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=8160 read=172475
8. 0.057 21,167.459 ↑ 1.0 5 1

Nested Loop (cost=1.12..249,464.36 rows=5 width=1,520) (actual time=465.330..21,167.459 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=8149 read=172456
9. 0.119 21,158.232 ↑ 1.0 5 1

Nested Loop (cost=0.56..249,430.27 rows=5 width=1,411) (actual time=462.647..21,158.232 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=8134 read=172446
10. 21,131.989 21,131.989 ↑ 74.3 7 1

Seq Scan on public.m_product product1_ (cost=0.00..244,962.17 rows=520 width=1,301) (actual time=455.196..21,131.989 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: 2402201
  • Buffers: shared hit=8121 read=172426
11. 26.124 26.124 ↑ 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=3.732..3.732 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=13 read=20
12. 9.170 9.170 ↑ 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=1.833..1.834 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=15 read=10
13. 32.255 32.255 ↑ 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=6.448..6.451 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=11 read=19
14. 8.825 53.225 ↑ 1.0 4,084 5

Materialize (cost=0.00..488.26 rows=4,084 width=529) (actual time=0.766..10.645 rows=4,084 loops=5)

  • Output: adimage2_.binarydata, adimage2_.ad_image_id
  • Buffers: shared read=427
15. 44.400 44.400 ↑ 1.0 4,084 1

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

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

SubPlan (forNested Loop)

17. 0.075 22.570 ↑ 1.0 1 5

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

  • Output: sum(butismd_mi5_.available_stock), butismd_mi5_.nosica_store
  • Group Key: butismd_mi5_.nosica_store
  • Buffers: shared hit=614 read=19
18. 1.730 22.495 ↑ 1.0 1 5

Hash Join (cost=16.89..214.71 rows=1 width=8) (actual time=3.230..4.499 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=614 read=19
19. 14.400 14.400 ↑ 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.014..2.880 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: 1110
  • Buffers: shared hit=579 read=10
20. 0.035 6.365 ↓ 2.0 2 5

Hash (cost=16.88..16.88 rows=1 width=22) (actual time=1.273..1.273 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=35 read=9
21. 0.055 6.330 ↓ 2.0 2 5

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

  • Output: butismd_mi5_.nosica_store, butismd_mi5_.available_stock, butismd_mi5_.nosica_warehouse
  • Buffers: shared hit=35 read=9
22. 1.835 1.835 ↑ 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.367..0.367 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=13 read=2
23. 4.440 4.440 ↓ 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.610..0.888 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=22 read=7
24. 8.635 8.635 ↑ 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=1.727..1.727 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
25. 1.240 1.240 ↑ 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.248..0.248 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=19 read=1
26. 1.240 1.240 ↓ 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.248..0.248 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=8 read=2
27. 0.040 0.040 ↓ 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.008..0.008 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.035 0.035 ↓ 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.007..0.007 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.050 0.050 ↑ 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.009..0.010 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.050 0.050 ↑ 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.009..0.010 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.039 61.070 ↓ 0.0 0 5

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

  • Output: characteri16_.name
  • Buffers: shared hit=19 read=26
32. 0.025 49.275 ↓ 0.0 0 5

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

  • Output: productcha15_.m_ch_value_id
  • Buffers: shared hit=17 read=20
33. 0.705 0.705 ↑ 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.140..0.141 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=13 read=2
34. 48.545 48.545 ↓ 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=9.709..9.709 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=4 read=18
35. 11.756 11.756 ↑ 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=5.876..5.878 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=2 read=6