explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hOg0

Settings
# exclusive inclusive rows x rows loops node
1. 0.029 1,971.662 ↓ 0.0 0 1

Sort (cost=14,849.19..14,849.19 rows=1 width=214) (actual time=1,971.662..1,971.662 rows=0 loops=1)

  • Sort Key: o.name, bp.name, o_1.dateordered
  • Sort Method: quicksort Memory: 25kB
2. 0.000 1,971.633 ↓ 0.0 0 1

Nested Loop (cost=14,800.79..14,849.18 rows=1 width=214) (actual time=1,971.633..1,971.633 rows=0 loops=1)

3. 0.001 1,971.633 ↓ 0.0 0 1

Nested Loop Left Join (cost=14,799.34..14,841.22 rows=2 width=161) (actual time=1,971.633..1,971.633 rows=0 loops=1)

  • Filter: ((bpl.c_bpartner_location_id IS NULL) OR (NOT (hashed SubPlan 5)))
4. 0.000 1,971.632 ↓ 0.0 0 1

Nested Loop Left Join (cost=14,797.89..14,831.04 rows=4 width=127) (actual time=1,971.632..1,971.632 rows=0 loops=1)

  • Filter: ((u.ad_user_id IS NULL) OR (NOT (hashed SubPlan 6)))
5. 0.009 1,971.632 ↓ 0.0 0 1

Hash Join (cost=14,796.44..14,812.13 rows=8 width=121) (actual time=1,971.632..1,971.632 rows=0 loops=1)

  • Hash Cond: (dt.c_doctype_id = o_1.c_doctype_id)
6. 0.034 0.050 ↑ 126.0 1 1

Seq Scan on c_doctype dt (cost=1.16..16.30 rows=126 width=39) (actual time=0.050..0.050 rows=1 loops=1)

  • Filter: ((c_doctype_id IS NULL) OR (NOT (hashed SubPlan 4)))
7.          

SubPlan (for Seq Scan)

8. 0.016 0.016 ↓ 0.0 0 1

Seq Scan on ad_private_access ad_private_access_2 (cost=0.00..1.16 rows=1 width=7) (actual time=0.016..0.016 rows=0 loops=1)

  • Filter: ((ad_user_id <> '5003247'::numeric) AND (ad_table_id = '217'::numeric) AND (isactive = 'Y'::bpchar))
  • Rows Removed by Filter: 9
9. 0.001 1,971.573 ↓ 0.0 0 1

Hash (cost=14,795.09..14,795.09 rows=15 width=94) (actual time=1,971.573..1,971.573 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
10. 0.009 1,971.572 ↓ 0.0 0 1

Hash Join (cost=14,779.15..14,795.09 rows=15 width=94) (actual time=1,971.572..1,971.572 rows=0 loops=1)

  • Hash Cond: (o_1.ad_org_id = o.ad_org_id)
11. 0.027 1,971.524 ↓ 0.0 0 1

GroupAggregate (cost=14,776.43..14,790.75 rows=127 width=92) (actual time=1,971.524..1,971.524 rows=0 loops=1)

  • Group Key: o_1.c_order_id
  • Filter: (sum(((l.qtyordered - l.qtyinvoiced) * l.priceactual)) > '0'::numeric)
  • Rows Removed by Filter: 8
12. 0.050 1,971.497 ↑ 25.5 15 1

Sort (cost=14,776.43..14,777.38 rows=382 width=77) (actual time=1,971.496..1,971.497 rows=15 loops=1)

  • Sort Key: o_1.c_order_id
  • Sort Method: quicksort Memory: 27kB
13. 0.142 1,971.447 ↑ 25.5 15 1

Hash Left Join (cost=13.08..14,760.05 rows=382 width=77) (actual time=1,538.776..1,971.447 rows=15 loops=1)

  • Hash Cond: (bp_1.c_invoiceschedule_id = si.c_invoiceschedule_id)
  • Filter: ((o_1.invoicerule = 'I'::bpchar) OR ((o_1.invoicerule = 'O'::bpchar) AND (NOT (alternatives: SubPlan 7 or hashed SubPlan 8))) OR ((o_1.invoicerule = 'D'::bpchar) AND (l.qtyinvoiced <> l.qtydelivered)) OR ((o_1.invoicerule = 'S'::bpchar) AND (bp_1.c_invoiceschedule_id IS NULL)) OR ((o_1.invoicerule = 'S'::bpchar) AND (bp_1.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_1.dateordered)::timestamp with time zone) <= (((firstof(getdate(), 'MM'::character varying))::timestamp with time zone + si.invoicedaycutoff) - 1)) AND (trunc(getdate()) >= (((firstof((o_1.dateordered)::timestamp with time zone, 'MM'::character varying))::timestamp with time zone + si.invoiceday) - 1))) OR ((trunc((o_1.dateordered)::timestamp with time zone) <= (((firstof(getdate(), 'MM'::character varying))::timestamp with time zone + si.invoicedaycutoff) + 14)) AND (trunc(getdate()) >= (((firstof((o_1.dateordered)::timestamp with time zone, 'MM'::character varying))::timestamp with time zone + si.invoiceday) + 14))))) OR ((si.invoicefrequency = 'M'::bpchar) AND (trunc((o_1.dateordered)::timestamp with time zone) <= (((firstof(getdate(), 'MM'::character varying))::timestamp with time zone + si.invoicedaycutoff) - 1)) AND (trunc(getdate()) >= (((firstof((o_1.dateordered)::timestamp with time zone, 'MM'::character varying))::timestamp with time zone + si.invoiceday) - 1))))))
  • Rows Removed by Filter: 12
14. 20.914 1,971.298 ↑ 28.2 27 1

Nested Loop (cost=1.28..14,746.25 rows=761 width=100) (actual time=82.528..1,971.298 rows=27 loops=1)

15. 134.139 1,461.216 ↓ 472.2 122,292 1

Nested Loop (cost=0.86..14,386.11 rows=259 width=78) (actual time=5.676..1,461.216 rows=122,292 loops=1)

16. 37.057 1,082.493 ↓ 472.2 122,292 1

Nested Loop (cost=0.57..14,281.54 rows=259 width=62) (actual time=5.634..1,082.493 rows=122,292 loops=1)

17. 0.160 0.160 ↓ 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.032..0.160 rows=29 loops=1)

  • Filter: ((docbasetype = 'SOO'::bpchar) AND (docsubtypeso <> ALL ('{ON,OB,WR}'::bpchar[])))
  • Rows Removed by Filter: 222
18. 450.121 1,045.276 ↓ 2.1 4,217 29

Index Scan using idx_c_order_c_doctype_id on c_order o_1 (cost=0.42..14,241.86 rows=1,972 width=62) (actual time=2.129..36.044 rows=4,217 loops=29)

  • Index Cond: (c_doctype_id = c_doctype.c_doctype_id)
  • Filter: ((docstatus = ANY ('{CO,IP}'::bpchar[])) AND (ad_client_id = ANY ('{0,1000001}'::numeric[])) AND (ad_org_id = ANY ('{0,1000002}'::numeric[])) AND (ad_client_id = '1000001'::numeric) AND ((invoicerule = 'I'::bpchar) OR ((invoicerule = 'O'::bpchar) AND (NOT (alternatives: SubPlan 7 or hashed SubPlan 8))) OR (invoicerule = 'D'::bpchar) OR (invoicerule = 'S'::bpchar) OR (invoicerule = 'S'::bpchar)))
  • Rows Removed by Filter: 4686
19.          

SubPlan (for Index Scan)

20. 595.155 595.155 ↓ 0.0 0 119,031

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=119,031)

  • Index Cond: (c_order_id = o_1.c_order_id)
  • Filter: (qtyordered <> qtydelivered)
  • Rows Removed by Filter: 3
21. 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)
22. 244.584 244.584 ↑ 1.0 1 122,292

Index Scan using c_bpartner_pkey on c_bpartner bp_1 (cost=0.29..0.40 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=122,292)

  • Index Cond: (c_bpartner_id = o_1.c_bpartner_id)
23. 489.168 489.168 ↓ 0.0 0 122,292

Index Scan using c_orderline_order on c_orderline l (cost=0.42..1.32 rows=7 width=28) (actual time=0.004..0.004 rows=0 loops=122,292)

  • Index Cond: (c_order_id = o_1.c_order_id)
  • Filter: (qtyordered <> qtyinvoiced)
  • Rows Removed by Filter: 3
24. 0.001 0.007 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
25. 0.006 0.006 ↓ 0.0 0 1

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

26. 0.011 0.039 ↓ 2.0 30 1

Hash (cost=2.54..2.54 rows=15 width=22) (actual time=0.039..0.039 rows=30 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
27. 0.025 0.028 ↓ 2.0 30 1

Seq Scan on ad_org o (cost=1.16..2.54 rows=15 width=22) (actual time=0.021..0.028 rows=30 loops=1)

  • Filter: ((ad_org_id IS NULL) OR (NOT (hashed SubPlan 2)))
28.          

SubPlan (for Seq Scan)

29. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on ad_private_access (cost=0.00..1.16 rows=1 width=7) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: ((ad_user_id <> '5003247'::numeric) AND (ad_table_id = '155'::numeric) AND (isactive = 'Y'::bpchar))
  • Rows Removed by Filter: 9
30. 0.000 0.000 ↓ 0.0 0

Index Scan using ad_user_pkey on ad_user u (cost=0.29..2.21 rows=1 width=18) (never executed)

  • Index Cond: (o_1.ad_user_id = ad_user_id)
31.          

SubPlan (for Nested Loop Left Join)

32. 0.000 0.000 ↓ 0.0 0

Seq Scan on ad_private_access ad_private_access_4 (cost=0.00..1.16 rows=1 width=7) (never executed)

  • Filter: ((ad_user_id <> '5003247'::numeric) AND (ad_table_id = '114'::numeric) AND (isactive = 'Y'::bpchar))
33. 0.000 0.000 ↓ 0.0 0

Index Scan using c_bpartner_location_pkey on c_bpartner_location bpl (cost=0.29..2.24 rows=1 width=46) (never executed)

  • Index Cond: (o_1.c_bpartner_location_id = c_bpartner_location_id)
34.          

SubPlan (for Nested Loop Left Join)

35. 0.000 0.000 ↓ 0.0 0

Seq Scan on ad_private_access ad_private_access_3 (cost=0.00..1.16 rows=1 width=7) (never executed)

  • Filter: ((ad_user_id <> '5003247'::numeric) AND (ad_table_id = '293'::numeric) AND (isactive = 'Y'::bpchar))
36. 0.000 0.000 ↓ 0.0 0

Index Scan using c_bpartner_pkey on c_bpartner bp (cost=1.45..3.31 rows=1 width=34) (never executed)

  • Index Cond: (c_bpartner_id = o_1.c_bpartner_id)
  • Filter: ((c_bpartner_id IS NULL) OR (NOT (hashed SubPlan 3)))
37.          

SubPlan (for Index Scan)

38. 0.000 0.000 ↓ 0.0 0

Seq Scan on ad_private_access ad_private_access_1 (cost=0.00..1.16 rows=1 width=7) (never executed)

  • Filter: ((ad_user_id <> '5003247'::numeric) AND (ad_table_id = '291'::numeric) AND (isactive = 'Y'::bpchar))
39.          

SubPlan (for Nested Loop)

40. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=1.32..1.33 rows=1 width=8) (never executed)

41. 0.000 0.000 ↓ 0.0 0

Seq Scan on cof_itemsubstituto its (cost=0.00..1.31 rows=1 width=0) (never executed)

  • Filter: ((cof_faturado = 'N'::bpchar) AND (isvalid = 'N'::bpchar) AND (c_order_id = o_1.c_order_id))
Planning time : 7.358 ms
Execution time : 1,973.021 ms