explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DAHw

Settings
# exclusive inclusive rows x rows loops node
1. 23.190 887.037 ↓ 0.0 0 1

Subquery Scan on m_compras (cost=60,085.94..68,765.71 rows=25 width=96) (actual time=887.037..887.037 rows=0 loops=1)

  • Output: m_compras.type, m_compras.dateordered, m_compras.m_product_id, m_compras.priceactual, m_compras.rank
  • Filter: ((m_compras.m_product_id = 2007001::numeric) AND (date(m_compras.dateordered) <= '2018-04-27'::date))
  • Rows Removed by Filter: 73877
  • Buffers: shared hit=52165, temp read=347 written=347
2. 25.687 863.847 ↓ 5.0 73,877 1

Append (cost=60,085.94..68,507.66 rows=14,746 width=54) (actual time=624.068..863.847 rows=73,877 loops=1)

  • Buffers: shared hit=52165, temp read=347 written=347
3. 29.241 838.109 ↓ 5.9 73,877 1

Subquery Scan on *SELECT* 1 (cost=60,085.94..60,587.10 rows=12,529 width=60) (actual time=624.068..838.109 rows=73,877 loops=1)

  • Output: 'P'::text, "*SELECT* 1".dateordered, "*SELECT* 1".m_product_id, "*SELECT* 1".priceactual, "*SELECT* 1".rank
  • Buffers: shared hit=52160, temp read=347 written=347
4. 148.133 808.868 ↓ 5.9 73,877 1

WindowAgg (cost=60,085.94..60,461.81 rows=12,529 width=60) (actual time=624.066..808.868 rows=73,877 loops=1)

  • Output: 'P'::text, o.dateordered, ol.m_product_id, CASE WHEN (pl.istaxincluded = 'Y'::bpchar) THEN ((ol.priceactual * 100::numeric) / (100::numeric + t.rate)) ELSE ol.priceactual END, rank() OVER (?), ol.m_product_id
  • Buffers: shared hit=52160, temp read=347 written=347
5. 361.824 660.735 ↓ 5.9 73,877 1

Sort (cost=60,085.94..60,117.26 rows=12,529 width=60) (actual time=624.047..660.735 rows=73,877 loops=1)

  • Output: o.dateordered, ol.m_product_id, pl.istaxincluded, ol.priceactual, t.rate
  • Sort Key: ol.m_product_id, o.dateordered
  • Sort Method: external merge Disk: 2768kB
  • Buffers: shared hit=52160, temp read=347 written=347
6. 56.352 298.911 ↓ 5.9 73,877 1

Hash Join (cost=2.85..59,233.16 rows=12,529 width=60) (actual time=0.164..298.911 rows=73,877 loops=1)

  • Output: o.dateordered, ol.m_product_id, pl.istaxincluded, ol.priceactual, t.rate
  • Hash Cond: (ol.c_tax_id = t.c_tax_id)
  • Buffers: shared hit=52160
7. 55.451 242.544 ↓ 5.9 73,877 1

Nested Loop (cost=1.74..59,059.77 rows=12,529 width=35) (actual time=0.137..242.544 rows=73,877 loops=1)

  • Output: ol.m_product_id, ol.priceactual, ol.c_tax_id, o.dateordered, pl.istaxincluded
  • Buffers: shared hit=52159
8. 6.751 114.657 ↑ 1.0 5,572 1

Hash Join (cost=1.31..20,306.06 rows=5,697 width=22) (actual time=0.120..114.657 rows=5,572 loops=1)

  • Output: o.dateordered, o.c_order_id, pl.istaxincluded
  • Hash Cond: (o.m_pricelist_id = pl.m_pricelist_id)
  • Buffers: shared hit=15711
9. 107.886 107.886 ↑ 1.0 5,572 1

Seq Scan on xendra.c_order o (cost=0.00..20,226.41 rows=5,697 width=21) (actual time=0.089..107.886 rows=5,572 loops=1)

  • Output: o.c_order_id, o.ad_client_id, o.ad_org_id, o.isactive, o.created, o.createdby, o.updated, o.updatedby, o.issotrx, o.documentno, o.docstatus, o.docaction, o.processing, o.processed, o.c_doctype_id, o (...)
  • Filter: (o.issotrx = 'N'::bpchar)
  • Rows Removed by Filter: 355741
  • Buffers: shared hit=15710
10. 0.014 0.020 ↑ 1.0 14 1

Hash (cost=1.14..1.14 rows=14 width=24) (actual time=0.020..0.020 rows=14 loops=1)

  • Output: pl.istaxincluded, pl.m_pricelist_id
  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
  • Buffers: shared hit=1
11. 0.006 0.006 ↑ 1.0 14 1

Seq Scan on xendra.m_pricelist pl (cost=0.00..1.14 rows=14 width=24) (actual time=0.003..0.006 rows=14 loops=1)

  • Output: pl.istaxincluded, pl.m_pricelist_id
  • Buffers: shared hit=1
12. 72.436 72.436 ↓ 3.2 13 5,572

Index Scan using c_orderline_order on xendra.c_orderline ol (cost=0.42..6.76 rows=4 width=25) (actual time=0.006..0.013 rows=13 loops=5,572)

  • Output: ol.c_orderline_id, ol.ad_client_id, ol.ad_org_id, ol.isactive, ol.created, ol.createdby, ol.updated, ol.updatedby, ol.c_order_id, ol.line, ol.c_bpartner_id, ol.c_bpartner_location_id, ol.dateordered, ol.d (...)
  • Index Cond: (ol.c_order_id = o.c_order_id)
  • Buffers: shared hit=36448
13. 0.008 0.015 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=48) (actual time=0.015..0.015 rows=5 loops=1)

  • Output: t.rate, t.c_tax_id
  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
  • Buffers: shared hit=1
14. 0.007 0.007 ↑ 1.0 5 1

Seq Scan on xendra.c_tax t (cost=0.00..1.05 rows=5 width=48) (actual time=0.004..0.007 rows=5 loops=1)

  • Output: t.rate, t.c_tax_id
  • Buffers: shared hit=1
15. 0.000 0.051 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=117.09..7,920.55 rows=2,217 width=17) (actual time=0.051..0.051 rows=0 loops=1)

  • Output: 'I'::text, "*SELECT* 2".movementdate, "*SELECT* 2".m_product_id, "*SELECT* 2".priceactual, 0
  • Buffers: shared hit=5
16. 0.011 0.051 ↓ 0.0 0 1

Hash Join (cost=117.09..7,898.38 rows=2,217 width=17) (actual time=0.051..0.051 rows=0 loops=1)

  • Output: 'I'::text, i.movementdate, il.m_product_id, il.cost, 0
  • Hash Cond: (il.m_inventory_id = i.m_inventory_id)
  • Buffers: shared hit=5
17. 0.008 0.008 ↑ 222,045.0 1 1

Seq Scan on xendra.m_inventoryline il (cost=0.00..6,926.45 rows=222,045 width=16) (actual time=0.008..0.008 rows=1 loops=1)

  • Output: il.m_inventoryline_id, il.ad_client_id, il.ad_org_id, il.isactive, il.created, il.createdby, il.updated, il.updatedby, il.m_inventory_id, il.m_locator_id, il.m_product_id, il.line, il.qtybook, il.qtycount, il.description, (...)
  • Buffers: shared hit=1
18. 0.001 0.032 ↓ 0.0 0 1

Hash (cost=116.73..116.73 rows=29 width=15) (actual time=0.032..0.032 rows=0 loops=1)

  • Output: i.movementdate, i.m_inventory_id
  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
  • Buffers: shared hit=4
19. 0.005 0.031 ↓ 0.0 0 1

Hash Join (cost=4.51..116.73 rows=29 width=15) (actual time=0.031..0.031 rows=0 loops=1)

  • Output: i.movementdate, i.m_inventory_id
  • Hash Cond: (i.c_doctype_id = dt.c_doctype_id)
  • Buffers: shared hit=4
20. 0.003 0.003 ↑ 2,904.0 1 1

Seq Scan on xendra.m_inventory i (cost=0.00..101.04 rows=2,904 width=22) (actual time=0.003..0.003 rows=1 loops=1)

  • Output: i.m_inventory_id, i.ad_client_id, i.ad_org_id, i.isactive, i.created, i.createdby, i.updated, i.updatedby, i.documentno, i.description, i.m_warehouse_id, i.movementdate, i.posted, i.processed, i.processing, i.u (...)
  • Buffers: shared hit=1
21. 0.000 0.023 ↓ 0.0 0 1

Hash (cost=4.50..4.50 rows=1 width=6) (actual time=0.023..0.023 rows=0 loops=1)

  • Output: dt.c_doctype_id
  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
  • Buffers: shared hit=3
22. 0.023 0.023 ↓ 0.0 0 1

Seq Scan on xendra.c_doctype dt (cost=0.00..4.50 rows=1 width=6) (actual time=0.023..0.023 rows=0 loops=1)

  • Output: dt.c_doctype_id
  • Filter: (((dt.transactiontype)::text = 'B'::text) OR ((dt.transactiontype)::text = 'C'::text))
  • Rows Removed by Filter: 100
  • Buffers: shared hit=3
Planning time : 2.105 ms
Execution time : 888.226 ms