explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LdM3G

Settings
# exclusive inclusive rows x rows loops node
1. 2.111 217.405 ↑ 1.0 1,000 1

Limit (cost=1,004.00..43,843.82 rows=1,000 width=75) (actual time=11.270..217.405 rows=1,000 loops=1)

  • Output: p.product_id, p.name, sr.amount, ps.selectionproduct_id, (COALESCE(mppt.ispackage, false)), ((((sp.removetime > now()) OR (sp.removetime IS NULL)) AND ((sp.disabledtime > now()) OR (sp.disabledtime IS NULL)))), s."timestamp
  • Buffers: shared hit=6,254
2. 34.043 215.294 ↑ 46.6 1,000 1

Nested Loop Left Join (cost=1,004.00..1,997,682.32 rows=46,608 width=75) (actual time=11.262..215.294 rows=1,000 loops=1)

  • Output: p.product_id, p.name, sr.amount, ps.selectionproduct_id, COALESCE(mppt.ispackage, false), (((sp.removetime > now()) OR (sp.removetime IS NULL)) AND ((sp.disabledtime > now()) OR (sp.disabledtime IS NULL))), s."timestamp
  • Inner Unique: true
  • Join Filter: (mppt.producttype_id = ppt.main_producttype_id)
  • Rows Removed by Join Filter: 27,924
  • Buffers: shared hit=6,254
3. 27.758 151.251 ↑ 46.6 1,000 1

Nested Loop Left Join (cost=1,004.00..1,978,290.60 rows=46,608 width=93) (actual time=11.131..151.251 rows=1,000 loops=1)

  • Output: p.product_id, p.name, ps.selectionproduct_id, sp.removetime, sp.disabledtime, sr.amount, s."timestamp", ppt.main_producttype_id
  • Inner Unique: true
  • Join Filter: (pp.producttype_id = ppt.producttype_id)
  • Rows Removed by Join Filter: 22,964
  • Buffers: shared hit=6,253
4. 3.797 98.493 ↑ 46.6 1,000 1

Nested Loop Left Join (cost=1,004.00..1,962,850.28 rows=46,608 width=105) (actual time=11.025..98.493 rows=1,000 loops=1)

  • Output: p.product_id, p.name, ps.selectionproduct_id, sp.removetime, sp.disabledtime, sr.amount, s."timestamp", pp.producttype_id
  • Inner Unique: true
  • Buffers: shared hit=6,252
5. 5.869 93.696 ↑ 46.6 1,000 1

Nested Loop Left Join (cost=1,003.57..1,793,020.46 rows=46,608 width=89) (actual time=11.016..93.696 rows=1,000 loops=1)

  • Output: p.product_id, p.name, ps.selectionproduct_id, sp.removetime, sp.disabledtime, sr.amount, s."timestamp
  • Join Filter: (cp.product_id = p.product_id)
  • Rows Removed by Join Filter: 2,000
  • Buffers: shared hit=6,236
6. 3.883 84.827 ↑ 46.6 1,000 1

Nested Loop Left Join (cost=1,003.57..1,791,621.19 rows=46,608 width=89) (actual time=10.992..84.827 rows=1,000 loops=1)

  • Output: p.product_id, p.name, ps.selectionproduct_id, sp.removetime, sp.disabledtime, sr.amount, s."timestamp
  • Inner Unique: true
  • Buffers: shared hit=6,235
7. 4.058 79.944 ↑ 46.6 1,000 1

Nested Loop Left Join (cost=1,003.15..1,621,791.38 rows=46,608 width=73) (actual time=10.982..79.944 rows=1,000 loops=1)

  • Output: p.product_id, p.name, ps.selectionproduct_id, sr.amount, s."timestamp
  • Buffers: shared hit=6,219
8. 24.752 71.886 ↑ 46.6 1,000 1

Nested Loop (cost=1,002.87..1,507,810.10 rows=46,608 width=57) (actual time=10.962..71.886 rows=1,000 loops=1)

  • Output: p.product_id, p.name, sr.amount, s."timestamp
  • Join Filter: (pt.producttype_id = p.producttype_id)
  • Rows Removed by Join Filter: 18,105
  • Buffers: shared hit=4,214
9. 0.000 26.008 ↑ 56.4 1,006 1

Nested Loop (cost=1,001.30..1,491,636.28 rows=56,740 width=73) (actual time=10.768..26.008 rows=1,006 loops=1)

  • Output: p.product_id, p.name, p.producttype_id, sr.amount, s."timestamp
  • Inner Unique: true
  • Buffers: shared hit=4,212
10. 0.000 25.694 ↑ 70.5 1,006 1

Gather Merge (cost=1,000.88..1,306,088.51 rows=70,938 width=29) (actual time=10.722..25.694 rows=1,006 loops=1)

  • Output: sr.amount, sr.product_id, s."timestamp
  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=3,385 read=18
11. 3.154 60.882 ↑ 55.0 759 2 / 2

Nested Loop (cost=0.86..1,297,107.97 rows=41,728 width=29) (actual time=0.105..60.882 rows=759 loops=2)

  • Output: sr.amount, sr.product_id, s."timestamp
  • Buffers: shared hit=3,385 read=18
  • Worker 0: actual time=0.143..120.857 rows=1,440 loops=1
  • Buffers: shared hit=3,197 read=18
12. 38.488 38.488 ↑ 50.6 296 2 / 2

Parallel Index Scan Backward using "IX_sale_branch_id_timestamp" on "company2de759bc-3fce-11e3-ac31-5b2986f46e71".sale s (cost=0.43..49,664.34 rows=14,964 width=24) (actual time=0.038..38.488 rows=296 loops=2)

  • Output: s.sale_id, s.cashier_id, s.user_id, s.branch_id, s.sum, s.topay, s.customer_id, s.note, s.log_id, s.onaccount, s.receiver_company_id, s."timestamp", s.onhold, s.moneystorage_id, s.saletype_id, s.receiptnumber, s.roundoff, s.returned, s.customername, s.errorsale_id, s.correctedsale_id, s.sellername, s.reference, s.cashiernumber, s.customerextra, s.discount, s.discountpercent, s.discountpercentprimary, s.closed, s.roundoffledgeraccount_id, s.paidinvoice_id, s.cutreceiptproductname, s.modifiessale_id, s.showcustomername, s.ismanualdiscountonly, s.contact2, s.contact3, s.contact4, s.contact5, s.contact1, s.showproductcode, s.invoiceactor_id, s.parentsale_id, s.receipttemplate_id, s.waybill_id, s.saleprogram_id, s.removedtime, s.checkoutreference, s.salestate_id, s.taxfreetype_id, s.checkoutinfo, s.vatstatus_id, s.loyaltycardnumber, s.loyaltysystem_id, s.offlinereceiptnumber, s.referraltemplate_id, s.showvat, s.language_id, s.extrainfo, s.customergroups, s.template_id, s.customerorder
  • Index Cond: (s.branch_id = '971aa24a-dc34-4477-b039-8dc8a20f07f4'::uuid)
  • Buffers: shared hit=578 read=8
  • Worker 0: actual time=0.044..76.868 rows=562 loops=1
  • Buffers: shared hit=558 read=8
13. 19.240 19.240 ↑ 21.7 3 592 / 2

Index Scan using salerow_sale_id_idx on "company2de759bc-3fce-11e3-ac31-5b2986f46e71".salerow sr (cost=0.43..82.71 rows=65 width=37) (actual time=0.055..0.065 rows=3 loops=592)

  • Output: sr."row", sr.sale_id, sr.old_product_id, sr.user_id, sr.amount, sr.sum, sr."timestamp", sr.stockevent_id, sr.producer_id, sr.unitprice, sr.vatpercent, sr.vat_id, sr.producerbranch_id, sr.dynamicname, sr.producttype_id, sr.owner_company_id, sr.salerow_id, sr.isadjustment, sr.iseditable, sr.saleinvoice_id, sr.discount, sr.discountpercent, sr.discountpercentprimary, sr.originalprice, sr.note, sr.salediscount, sr.vatledgeraccount_id, sr.producttypeledgeraccount_id, sr.averagepurchasetotal, sr.stock_id, sr.cancelstockevent_id, sr.primaryproductgroup_id, sr.total, sr.vat, sr.invoice_id, sr.saleofferprice_id, sr.offerprice_id, sr.offerdiscount, sr.saleofferdiscount, sr.ismanualdiscountonly, sr.mainsalerow_id, sr.taxfreeledgeraccount_id, sr.margintaxledgeraccount_id, sr.old_selectionproduct_id, sr.saleorderrow_id, sr.customer_id, sr.warrantyperiod, sr.accountproductstarttime, sr.accountproductendtime, sr.isdelivered, sr.doinventory, sr.productpackagerow_id, sr.deliverydate, sr.originalsalerow_id, sr.modifiedtime, sr.istaxfree, sr.vatstatus_id, sr.ispaymenttermcost, sr.isdeliverymethodcost, sr.productstock_id, sr.manufacturerproduct_id, sr.product_id, sr.selectionproduct_id, sr.isinvoicecost, sr.alertkey, sr.averagecosttotal, sr.printname, sr.customergroups, sr.productgroups
  • Index Cond: (sr.sale_id = s.sale_id)
  • Buffers: shared hit=2,807 read=10
  • Worker 0: actual time=0.057..0.067 rows=3 loops=562
  • Buffers: shared hit=2,639 read=10
14. 8.048 8.048 ↑ 1.0 1 1,006

Index Scan using product_pkey on "company2de759bc-3fce-11e3-ac31-5b2986f46e71".product p (cost=0.42..2.62 rows=1 width=60) (actual time=0.008..0.008 rows=1 loops=1,006)

  • Output: p.product_id, p.code, p.oldcode, p.name, p.ean, p.vat_id, p.producttype_id, p.description, p.removetime, p.disabledtime, p.availableonline, p.superproduct_id, p.measure_id, p.size, p.log_id, p.variantparent_id, p.valid, p.accountproduct_id, p.defaultlanguage_id, p.a24phone, p.resttime, p.colorcode, p.colorcodetext, p.deliverygroup_id, p.medicalproduct_id, p.lastpurchaseaccount_id, p.viewonly, p.visibleontehplay, p.warrantyperiod, p.billingaddressrequired, p.allowcreditpayment, p.productstate_id, p.un_id, p.unweight, p.firesafetyrequired, p.a24_reservation_advance, p.saleperiod_id, p.createtime, p.modifytime, p.creator_id, p.modifier_id, p.mergeproduct_id, p.presystem_id, p.allowgiftcardbillingrefund, p.visibleontehplayuntilend, p.a24_hide_price, p.immaterialdelivery, p.serialnumbertype_id, p.extrasize, p.webstorestarttime, p.webstoreendtime, p.sellwithedit, p.seasonalaccountproductstartdate, p.seasonalaccountproductenddate, p.isseasonalaccountproduct, p.system_id, p.onlinepayment
  • Index Cond: (p.product_id = sr.product_id)
  • Filter: (((p.disabledtime IS NULL) OR (p.disabledtime > now())) AND ((p.removetime IS NULL) OR (p.removetime > now())))
  • Buffers: shared hit=4,024
15. 20.932 21.126 ↑ 1.0 19 1,006

Materialize (cost=1.57..2.96 rows=19 width=16) (actual time=0.001..0.021 rows=19 loops=1,006)

  • Output: pt.producttype_id
  • Buffers: shared hit=2
16. 0.067 0.194 ↑ 1.0 19 1

Hash Join (cost=1.57..2.87 rows=19 width=16) (actual time=0.125..0.194 rows=19 loops=1)

  • Output: pt.producttype_id
  • Inner Unique: true
  • Hash Cond: (pt.main_producttype_id = mpt.producttype_id)
  • Buffers: shared hit=2
17. 0.032 0.032 ↑ 1.0 23 1

Seq Scan on "company2de759bc-3fce-11e3-ac31-5b2986f46e71".producttype pt (cost=0.00..1.23 rows=23 width=20) (actual time=0.009..0.032 rows=23 loops=1)

  • Output: pt.producttype_id, pt.key, pt.main_producttype_id, pt.log_id, pt.ledgeraccount_id
  • Buffers: shared hit=1
18. 0.058 0.095 ↑ 1.0 23 1

Hash (cost=1.28..1.28 rows=23 width=4) (actual time=0.093..0.095 rows=23 loops=1)

  • Output: mpt.producttype_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
19. 0.037 0.037 ↑ 1.0 23 1

Seq Scan on main.producttype mpt (cost=0.00..1.28 rows=23 width=4) (actual time=0.008..0.037 rows=23 loops=1)

  • Output: mpt.producttype_id
  • Filter: mpt.issaleable
  • Rows Removed by Filter: 5
  • Buffers: shared hit=1
20. 4.000 4.000 ↓ 0.0 0 1,000

Index Scan using "IX_product_productselection_productselection" on "company2de759bc-3fce-11e3-ac31-5b2986f46e71".productselection ps (cost=0.28..2.44 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=1,000)

  • Output: ps.selectionproduct_id, ps.product_id, ps.modifier_id, ps.modifiedtime, ps.isdefault, ps."order", ps.amount, ps.productselection_id
  • Index Cond: (p.product_id = ps.product_id)
  • Filter: ps.isdefault
  • Buffers: shared hit=2,005
21. 1.000 1.000 ↓ 0.0 0 1,000

Index Scan using product_pkey on "company2de759bc-3fce-11e3-ac31-5b2986f46e71".product sp (cost=0.42..3.64 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=1,000)

  • Output: sp.product_id, sp.code, sp.oldcode, sp.name, sp.ean, sp.vat_id, sp.producttype_id, sp.description, sp.removetime, sp.disabledtime, sp.availableonline, sp.superproduct_id, sp.measure_id, sp.size, sp.log_id, sp.variantparent_id, sp.valid, sp.accountproduct_id, sp.defaultlanguage_id, sp.a24phone, sp.resttime, sp.colorcode, sp.colorcodetext, sp.deliverygroup_id, sp.medicalproduct_id, sp.lastpurchaseaccount_id, sp.viewonly, sp.visibleontehplay, sp.warrantyperiod, sp.billingaddressrequired, sp.allowcreditpayment, sp.productstate_id, sp.un_id, sp.unweight, sp.firesafetyrequired, sp.a24_reservation_advance, sp.saleperiod_id, sp.createtime, sp.modifytime, sp.creator_id, sp.modifier_id, sp.mergeproduct_id, sp.presystem_id, sp.allowgiftcardbillingrefund, sp.visibleontehplayuntilend, sp.a24_hide_price, sp.immaterialdelivery, sp.serialnumbertype_id, sp.extrasize, sp.webstorestarttime, sp.webstoreendtime, sp.sellwithedit, sp.seasonalaccountproductstartdate, sp.seasonalaccountproductenddate, sp.isseasonalaccountproduct, sp.system_id, sp.onlinepayment
  • Index Cond: (sp.product_id = ps.selectionproduct_id)
  • Buffers: shared hit=16
22. 2.990 3.000 ↑ 1.0 2 1,000

Materialize (cost=0.00..1.03 rows=2 width=16) (actual time=0.001..0.003 rows=2 loops=1,000)

  • Output: cp.product_id
  • Buffers: shared hit=1
23. 0.010 0.010 ↑ 1.0 2 1

Seq Scan on "company2de759bc-3fce-11e3-ac31-5b2986f46e71".company_product cp (cost=0.00..1.02 rows=2 width=16) (actual time=0.007..0.010 rows=2 loops=1)

  • Output: cp.product_id
  • Buffers: shared hit=1
24. 1.000 1.000 ↓ 0.0 0 1,000

Index Scan using product_pkey on "company2de759bc-3fce-11e3-ac31-5b2986f46e71".product pp (cost=0.42..3.64 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=1,000)

  • Output: pp.product_id, pp.code, pp.oldcode, pp.name, pp.ean, pp.vat_id, pp.producttype_id, pp.description, pp.removetime, pp.disabledtime, pp.availableonline, pp.superproduct_id, pp.measure_id, pp.size, pp.log_id, pp.variantparent_id, pp.valid, pp.accountproduct_id, pp.defaultlanguage_id, pp.a24phone, pp.resttime, pp.colorcode, pp.colorcodetext, pp.deliverygroup_id, pp.medicalproduct_id, pp.lastpurchaseaccount_id, pp.viewonly, pp.visibleontehplay, pp.warrantyperiod, pp.billingaddressrequired, pp.allowcreditpayment, pp.productstate_id, pp.un_id, pp.unweight, pp.firesafetyrequired, pp.a24_reservation_advance, pp.saleperiod_id, pp.createtime, pp.modifytime, pp.creator_id, pp.modifier_id, pp.mergeproduct_id, pp.presystem_id, pp.allowgiftcardbillingrefund, pp.visibleontehplayuntilend, pp.a24_hide_price, pp.immaterialdelivery, pp.serialnumbertype_id, pp.extrasize, pp.webstorestarttime, pp.webstoreendtime, pp.sellwithedit, pp.seasonalaccountproductstartdate, pp.seasonalaccountproductenddate, pp.isseasonalaccountproduct, pp.system_id, pp.onlinepayment
  • Index Cond: (ps.selectionproduct_id = pp.product_id)
  • Buffers: shared hit=16
25. 24.970 25.000 ↑ 1.0 23 1,000

Materialize (cost=0.00..1.34 rows=23 width=20) (actual time=0.001..0.025 rows=23 loops=1,000)

  • Output: ppt.producttype_id, ppt.main_producttype_id
  • Buffers: shared hit=1
26. 0.030 0.030 ↑ 1.0 23 1

Seq Scan on "company2de759bc-3fce-11e3-ac31-5b2986f46e71".producttype ppt (cost=0.00..1.23 rows=23 width=20) (actual time=0.004..0.030 rows=23 loops=1)

  • Output: ppt.producttype_id, ppt.main_producttype_id
  • Buffers: shared hit=1
27. 29.961 30.000 ↑ 1.0 28 1,000

Materialize (cost=0.00..1.42 rows=28 width=5) (actual time=0.001..0.030 rows=28 loops=1,000)

  • Output: mppt.ispackage, mppt.producttype_id
  • Buffers: shared hit=1
28. 0.039 0.039 ↑ 1.0 28 1

Seq Scan on main.producttype mppt (cost=0.00..1.28 rows=28 width=5) (actual time=0.005..0.039 rows=28 loops=1)

  • Output: mppt.ispackage, mppt.producttype_id
  • Buffers: shared hit=1
Planning time : 16.926 ms
Execution time : 231.466 ms