explain.depesz.com

PostgreSQL's explain analyze made readable

Result: esAx : Optimization for: Optimization for: Optimization for: plan #vKI3; plan #u6dE; plan #yyLW

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.363 28.594 ↑ 12.2 157 1

Subquery Scan on fil_shippable_solines (cost=50,065.64..50,089.56 rows=1,914 width=250) (actual time=28.048..28.594 rows=157 loops=1)

2. 0.783 28.231 ↑ 12.2 157 1

Sort (cost=50,065.64..50,070.42 rows=1,914 width=231) (actual time=28.045..28.231 rows=157 loops=1)

  • Sort Key: co.documentno DESC, line.line
  • Sort Method: quicksort Memory: 66kB
3. 0.536 27.448 ↑ 12.2 157 1

Hash Left Join (cost=638.80..49,961.30 rows=1,914 width=231) (actual time=20.125..27.448 rows=157 loops=1)

  • Hash Cond: (co.fil_sub_bpartner_id = subbp.c_bpartner_id)
4. 0.444 20.978 ↑ 12.2 157 1

Hash Left Join (cost=441.33..49,746.15 rows=1,914 width=224) (actual time=14.176..20.978 rows=157 loops=1)

  • Hash Cond: (co.c_bpartner_id = bp.c_bpartner_id)
5. 0.488 14.424 ↑ 12.2 157 1

Hash Join (cost=243.85..49,522.36 rows=1,914 width=223) (actual time=8.053..14.424 rows=157 loops=1)

  • Hash Cond: (line.c_order_id = co.c_order_id)
6. 3.229 6.434 ↑ 16.6 174 1

Bitmap Heap Scan on c_orderline line (cost=58.94..49,307.51 rows=2,882 width=199) (actual time=0.537..6.434 rows=174 loops=1)

  • Recheck Cond: (c_bpartner_id = '1000208'::numeric)
  • Filter: ((qtydelivered <> qtyordered) AND (fil_replenishedqty <> qtyordered) AND (qtyordered <> ((SubPlan 1) + (SubPlan 2))))
  • Rows Removed by Filter: 2,747
  • Heap Blocks: exact=513
7. 0.421 0.421 ↑ 1.0 2,921 1

Bitmap Index Scan on c_orderline_bpartner_id (cost=0.00..58.22 rows=2,925 width=0) (actual time=0.421..0.421 rows=2,921 loops=1)

  • Index Cond: (c_bpartner_id = '1000208'::numeric)
8.          

SubPlan (for Bitmap Heap Scan)

9. 0.696 1.566 ↑ 1.0 1 174

Aggregate (cost=8.30..8.31 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=174)

10. 0.870 0.870 ↓ 0.0 0 174

Index Scan using m_packagelilne_order_id on m_packageline l (cost=0.28..8.30 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=174)

  • Index Cond: (c_orderline_id = line.c_orderline_id)
11. 0.696 1.218 ↑ 1.0 1 174

Aggregate (cost=8.30..8.31 rows=1 width=3) (actual time=0.006..0.007 rows=1 loops=174)

12. 0.522 0.522 ↓ 0.0 0 174

Index Scan using m_packagelilne_order_id on m_packageline l_1 (cost=0.28..8.30 rows=1 width=3) (actual time=0.003..0.003 rows=0 loops=174)

  • Index Cond: (c_orderline_id = line.c_orderline_id)
13. 2.936 7.502 ↓ 1.2 1,902 1

Hash (cost=164.63..164.63 rows=1,622 width=30) (actual time=7.502..7.502 rows=1,902 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 127kB
14. 4.566 4.566 ↓ 1.2 1,902 1

Seq Scan on c_order co (cost=0.00..164.63 rows=1,622 width=30) (actual time=0.007..4.566 rows=1,902 loops=1)

  • Filter: ((docstatus = ANY ('{IP,CO}'::bpchar[])) AND (issotrx = 'Y'::bpchar))
  • Rows Removed by Filter: 509
15. 2.630 6.110 ↑ 1.0 1,799 1

Hash (cost=174.99..174.99 rows=1,799 width=13) (actual time=6.110..6.110 rows=1,799 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 85kB
16. 3.480 3.480 ↑ 1.0 1,799 1

Seq Scan on c_bpartner bp (cost=0.00..174.99 rows=1,799 width=13) (actual time=0.004..3.480 rows=1,799 loops=1)

17. 2.621 5.934 ↑ 1.0 1,799 1

Hash (cost=174.99..174.99 rows=1,799 width=13) (actual time=5.934..5.934 rows=1,799 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 85kB
18. 3.313 3.313 ↑ 1.0 1,799 1

Seq Scan on c_bpartner subbp (cost=0.00..174.99 rows=1,799 width=13) (actual time=0.004..3.313 rows=1,799 loops=1)

Planning time : 1.385 ms
Execution time : 28.908 ms