explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Fbh7

Settings
# exclusive inclusive rows x rows loops node
1. 0.051 3,462.006 ↑ 37.8 21 1

GroupAggregate (cost=15,096.96..15,116.81 rows=794 width=92) (actual time=3,461.966..3,462.006 rows=21 loops=1)

  • Group Key: o.c_order_id
2. 0.064 3,461.955 ↑ 21.5 37 1

Sort (cost=15,096.96..15,098.95 rows=794 width=77) (actual time=3,461.953..3,461.955 rows=37 loops=1)

  • Sort Key: o.c_order_id
  • Sort Method: quicksort Memory: 30kB
3. 0.309 3,461.891 ↑ 21.5 37 1

Hash Left Join (cost=13.08..15,058.72 rows=794 width=77) (actual time=1.172..3,461.891 rows=37 loops=1)

  • Hash Cond: (bp.c_invoiceschedule_id = si.c_invoiceschedule_id)
  • Filter: ((o.invoicerule = 'I'::bpchar) OR ((o.invoicerule = 'O'::bpchar) AND (NOT (alternatives: SubPlan 1 or hashed SubPlan 2))) OR ((o.invoicerule = 'D'::bpchar) AND (l.qtyinvoiced <> l.qtydelivered)) OR ((o.invoicerule = 'S'::bpchar) AND (bp.c_invoiceschedule_id IS NULL)) OR ((o.invoicerule = 'S'::bpchar) AND (bp.c_invoiceschedule_id IS NOT NULL) AND ((si.invoicefrequency IS NULL) OR (si.invoicefrequency = 'D'::bpchar) OR (si.invoicefrequency = 'W'::bpchar) OR ((si.invoicefrequency = 'T'::bpchar) AND (((trunc((o.dateordered)::timestamp with time zone) <= (((firstof(getdate(), 'MM'::character varying))::timestamp with time zone + si.invoicedaycutoff) - 1)) AND (trunc(getdate()) >= (((firstof((o.dateordered)::timestamp with time zone, 'MM'::character varying))::timestamp with time zone + si.invoiceday) - 1))) OR ((trunc((o.dateordered)::timestamp with time zone) <= (((firstof(getdate(), 'MM'::character varying))::timestamp with time zone + si.invoicedaycutoff) + 14)) AND (trunc(getdate()) >= (((firstof((o.dateordered)::timestamp with time zone, 'MM'::character varying))::timestamp with time zone + si.invoiceday) + 14))))) OR ((si.invoicefrequency = 'M'::bpchar) AND (trunc((o.dateordered)::timestamp with time zone) <= (((firstof(getdate(), 'MM'::character varying))::timestamp with time zone + si.invoicedaycutoff) - 1)) AND (trunc(getdate()) >= (((firstof((o.dateordered)::timestamp with time zone, 'MM'::character varying))::timestamp with time zone + si.invoiceday) - 1))))))
  • Rows Removed by Filter: 71
4. 24.532 3,461.578 ↑ 14.6 108 1

Nested Loop (cost=1.28..15,042.77 rows=1,582 width=100) (actual time=1.130..3,461.578 rows=108 loops=1)

5. 261.200 2,417.426 ↓ 472.9 254,905 1

Nested Loop (cost=0.86..14,426.24 rows=539 width=78) (actual time=0.190..2,417.426 rows=254,905 loops=1)

6. 74.455 1,646.416 ↓ 472.9 254,905 1

Nested Loop (cost=0.57..14,235.55 rows=539 width=62) (actual time=0.173..1,646.416 rows=254,905 loops=1)

7. 0.190 0.190 ↓ 29.0 29 1

Index Scan using c_doctype_pkey on c_doctype (cost=0.14..19.96 rows=1 width=6) (actual time=0.054..0.190 rows=29 loops=1)

  • Filter: ((docbasetype = 'SOO'::bpchar) AND (docsubtypeso <> ALL ('{ON,OB,WR}'::bpchar[])))
  • Rows Removed by Filter: 222
8. 344.591 1,571.771 ↓ 2.1 8,790 29

Index Scan using idx_c_order_c_doctype_id on c_order o (cost=0.42..14,174.59 rows=4,100 width=62) (actual time=0.018..54.199 rows=8,790 loops=29)

  • Index Cond: (c_doctype_id = c_doctype.c_doctype_id)
  • Filter: ((docstatus = ANY ('{CO,IP}'::bpchar[])) AND ((invoicerule = 'I'::bpchar) OR ((invoicerule = 'O'::bpchar) AND (NOT (alternatives: SubPlan 1 or hashed SubPlan 2))) OR (invoicerule = 'D'::bpchar) OR (invoicerule = 'S'::bpchar) OR (invoicerule = 'S'::bpchar)))
  • Rows Removed by Filter: 113
9.          

SubPlan (for Index Scan)

10. 1,227.180 1,227.180 ↓ 0.0 0 245,436

Index Scan using c_orderline_order on c_orderline zz1 (cost=0.42..2.78 rows=7 width=0) (actual time=0.005..0.005 rows=0 loops=245,436)

  • Index Cond: (c_order_id = o.c_order_id)
  • Filter: (qtyordered <> qtydelivered)
  • Rows Removed by Filter: 3
11. 0.000 0.000 ↓ 0.0 0

Seq Scan on c_orderline zz1_1 (cost=0.00..71,083.15 rows=868,766 width=6) (never executed)

  • Filter: (qtyordered <> qtydelivered)
12. 509.810 509.810 ↑ 1.0 1 254,905

Index Scan using c_bpartner_pkey on c_bpartner bp (cost=0.29..0.35 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=254,905)

  • Index Cond: (c_bpartner_id = o.c_bpartner_id)
13. 1,019.620 1,019.620 ↓ 0.0 0 254,905

Index Scan using c_orderline_order on c_orderline l (cost=0.42..1.07 rows=7 width=28) (actual time=0.004..0.004 rows=0 loops=254,905)

  • Index Cond: (c_order_id = o.c_order_id)
  • Filter: (qtyordered <> qtyinvoiced)
  • Rows Removed by Filter: 3
14. 0.000 0.004 ↓ 0.0 0 1

Hash (cost=10.80..10.80 rows=80 width=56) (actual time=0.004..0.004 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
15. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on c_invoiceschedule si (cost=0.00..10.80 rows=80 width=56) (actual time=0.004..0.004 rows=0 loops=1)

Planning time : 3.666 ms
Execution time : 3,462.279 ms