explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Zra2

Settings
# exclusive inclusive rows x rows loops node
1. 0.048 290.785 ↑ 1.0 27 1

Limit (cost=43,962.42..43,962.49 rows=27 width=94) (actual time=290.722..290.785 rows=27 loops=1)

  • Output: a.product_id, a.name, (sum(a.amount)), a.selectionproduct_id, a.packageproduct, a.validselectionproduct
  • Buffers: shared hit=6,254
2. 0.752 290.737 ↑ 37.0 27 1

Sort (cost=43,962.42..43,964.92 rows=1,000 width=94) (actual time=290.716..290.737 rows=27 loops=1)

  • Output: a.product_id, a.name, (sum(a.amount)), a.selectionproduct_id, a.packageproduct, a.validselectionproduct
  • Sort Key: (sum(a.amount)) DESC
  • Sort Method: top-N heapsort Memory: 29kB
  • Buffers: shared hit=6,254
3. 1.926 289.985 ↑ 1.9 535 1

GroupAggregate (cost=43,903.65..43,933.65 rows=1,000 width=94) (actual time=287.278..289.985 rows=535 loops=1)

  • Output: a.product_id, a.name, sum(a.amount), a.selectionproduct_id, a.packageproduct, a.validselectionproduct
  • Group Key: a.product_id, a.name, a.selectionproduct_id, a.packageproduct, a.validselectionproduct
  • Buffers: shared hit=6,254
4. 3.798 288.059 ↑ 1.0 1,000 1

Sort (cost=43,903.65..43,906.15 rows=1,000 width=67) (actual time=287.253..288.059 rows=1,000 loops=1)

  • Output: a.product_id, a.name, a.selectionproduct_id, a.packageproduct, a.validselectionproduct, a.amount
  • Sort Key: a.product_id, a.name, a.selectionproduct_id, a.packageproduct, a.validselectionproduct
  • Sort Method: quicksort Memory: 152kB
  • Buffers: shared hit=6,254
5. 3.036 284.261 ↑ 1.0 1,000 1

Subquery Scan on a (cost=1,004.00..43,853.82 rows=1,000 width=67) (actual time=16.911..284.261 rows=1,000 loops=1)

  • Output: a.product_id, a.name, a.selectionproduct_id, a.packageproduct, a.validselectionproduct, a.amount
  • Buffers: shared hit=6,254
6. 2.703 281.225 ↑ 1.0 1,000 1

Limit (cost=1,004.00..43,843.82 rows=1,000 width=75) (actual time=16.909..281.225 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
7. 43.115 278.522 ↑ 46.6 1,000 1

Nested Loop Left Join (cost=1,004.00..1,997,682.32 rows=46,608 width=75) (actual time=16.907..278.522 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
8. 35.820 196.407 ↑ 46.6 1,000 1

Nested Loop Left Join (cost=1,004.00..1,978,290.60 rows=46,608 width=93) (actual time=16.700..196.407 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
9. 5.215 128.587 ↑ 46.6 1,000 1

Nested Loop Left Join (cost=1,004.00..1,962,850.28 rows=46,608 width=105) (actual time=16.496..128.587 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
10. 7.307 122.372 ↑ 46.6 1,000 1

Nested Loop Left Join (cost=1,003.57..1,793,020.46 rows=46,608 width=89) (actual time=16.488..122.372 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
11. 4.253 111.065 ↑ 46.6 1,000 1

Nested Loop Left Join (cost=1,003.57..1,791,621.19 rows=46,608 width=89) (actual time=16.454..111.065 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
12. 4.579 104.812 ↑ 46.6 1,000 1

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

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

Nested Loop (cost=1,002.87..1,507,810.10 rows=46,608 width=57) (actual time=16.417..94.233 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
14. 0.000 35.304 ↑ 56.4 1,006 1

Nested Loop (cost=1,001.30..1,491,636.28 rows=56,740 width=73) (actual time=16.178..35.304 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
15. 0.000 43.327 ↑ 70.5 1,006 1

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

  • Output: sr.amount, sr.product_id, s."timestamp
  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=3,342 read=16
16. 5.087 78.370 ↑ 55.5 752 2 / 2

Nested Loop (cost=0.86..1,297,107.97 rows=41,728 width=29) (actual time=0.140..78.370 rows=752 loops=2)

  • Output: sr.amount, sr.product_id, s."timestamp
  • Buffers: shared hit=3,342 read=16
  • Worker 0: actual time=0.211..155.405 rows=1,427 loops=1
  • Buffers: shared hit=3,154 read=16
17. 40.927 40.927 ↑ 51.2 292 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.048..40.927 rows=292 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=570 read=8
  • Worker 0: actual time=0.066..81.724 rows=553 loops=1
  • Buffers: shared hit=550 read=8
18. 32.356 32.356 ↑ 21.7 3 583 / 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.095..0.111 rows=3 loops=583)

  • 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,772 read=8
  • Worker 0: actual time=0.099..0.116 rows=3 loops=553
  • Buffers: shared hit=2,604 read=8
19. 10.060 10.060 ↑ 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.010..0.010 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
20. 26.896 27.162 ↑ 1.0 19 1,006

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

  • Output: pt.producttype_id
  • Buffers: shared hit=2
21. 0.116 0.266 ↑ 1.0 19 1

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

  • Output: pt.producttype_id
  • Inner Unique: true
  • Hash Cond: (pt.main_producttype_id = mpt.producttype_id)
  • Buffers: shared hit=2
22. 0.049 0.049 ↑ 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.017..0.049 rows=23 loops=1)

  • Output: pt.producttype_id, pt.key, pt.main_producttype_id, pt.log_id, pt.ledgeraccount_id
  • Buffers: shared hit=1
23. 0.046 0.101 ↑ 1.0 23 1

Hash (cost=1.28..1.28 rows=23 width=4) (actual time=0.100..0.101 rows=23 loops=1)

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

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

  • Output: mpt.producttype_id
  • Filter: mpt.issaleable
  • Rows Removed by Filter: 5
  • Buffers: shared hit=1
25. 6.000 6.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.006..0.006 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
26. 2.000 2.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.002..0.002 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
27. 3.988 4.000 ↑ 1.0 2 1,000

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

  • Output: cp.product_id
  • Buffers: shared hit=1
28. 0.012 0.012 ↑ 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.009..0.012 rows=2 loops=1)

  • Output: cp.product_id
  • Buffers: shared hit=1
29. 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
30. 31.951 32.000 ↑ 1.0 23 1,000

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

  • Output: ppt.producttype_id, ppt.main_producttype_id
  • Buffers: shared hit=1
31. 0.049 0.049 ↑ 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.010..0.049 rows=23 loops=1)

  • Output: ppt.producttype_id, ppt.main_producttype_id
  • Buffers: shared hit=1
32. 38.936 39.000 ↑ 1.0 28 1,000

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

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

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

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