explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rYZr : query1_10MB

Settings
# exclusive inclusive rows x rows loops node
1. 0.712 124,439.331 ↑ 2,502.8 153 1

Sort (cost=1,906,199.69..1,907,157.00 rows=382,922 width=249) (actual time=124,439.321..124,439.331 rows=153 loops=1)

  • Sort Key: "*SELECT* 1".c_bpartner_id, "*SELECT* 1".c_currency_id, "*SELECT* 1".c_invoice_id
  • Sort Method: quicksort Memory: 46kB
2. 63.882 124,438.619 ↑ 2,502.8 153 1

Nested Loop (cost=1,599,903.82..1,824,878.51 rows=382,922 width=249) (actual time=124,376.437..124,438.619 rows=153 loops=1)

3. 0.014 0.014 ↑ 1.0 1 1

Index Scan using c_bpartner_pkey on c_bpartner bp (cost=0.29..8.30 rows=1 width=13) (actual time=0.013..0.014 rows=1 loops=1)

  • Index Cond: (c_bpartner_id = '1000016'::numeric)
4. 0.161 124,374.723 ↑ 2,502.8 153 1

Unique (cost=1,599,903.54..1,624,793.47 rows=382,922 width=257) (actual time=124,374.517..124,374.723 rows=153 loops=1)

5. 0.785 124,374.562 ↑ 2,502.8 153 1

Sort (cost=1,599,903.54..1,600,860.84 rows=382,922 width=257) (actual time=124,374.517..124,374.562 rows=153 loops=1)

  • Sort Key: "*SELECT* 1".ad_org_id, "*SELECT* 1".ad_client_id, "*SELECT* 1".documentno, "*SELECT* 1".c_invoice_id, "*SELECT* 1".c_order_id, "*SELECT* 1".c_bpartner_id, "*SELECT* 1".issotrx, "*SELECT* 1".dateinvoiced, "*SELECT* 1".dateacct, "*SELECT* 1".netdays, "*SELECT* 1".duedate, "*SELECT* 1".daysdue, (("*SELECT* 1".discountdate)::timestamp without time zone), "*SELECT* 1".discountamt, "*SELECT* 1".grandtotal, "*SELECT* 1".c_currency_id, "*SELECT* 1".c_conversiontype_id, "*SELECT* 1".c_paymentterm_id, "*SELECT* 1".ispayschedulevalid, (NULL::numeric), "*SELECT* 1".invoicecollectiontype, "*SELECT* 1".c_campaign_id, "*SELECT* 1".c_project_id, "*SELECT* 1".c_activity_id, "*SELECT* 1".pay_bpartner_id
  • Sort Method: quicksort Memory: 65kB
6. 0.034 124,373.777 ↑ 2,502.8 153 1

Append (cost=9,742.99..1,517,273.36 rows=382,922 width=257) (actual time=6,489.103..124,373.777 rows=153 loops=1)

7. 0.103 123,618.668 ↑ 2,502.8 153 1

Subquery Scan on *SELECT* 1 (cost=9,742.99..1,213,734.70 rows=382,921 width=257) (actual time=6,489.103..123,618.668 rows=153 loops=1)

8. 14.778 123,618.565 ↑ 2,502.8 153 1

Hash Join (cost=9,742.99..1,209,905.49 rows=382,921 width=257) (actual time=6,489.102..123,618.565 rows=153 loops=1)

  • Hash Cond: (i.c_paymentterm_id = p.c_paymentterm_id)
9. 2.052 123,603.765 ↑ 2,502.8 153 1

Hash Join (cost=9,741.65..812,144.96 rows=382,921 width=180) (actual time=6,488.211..123,603.765 rows=153 loops=1)

  • Hash Cond: (i.c_bpartner_id = b.c_bpartner_id)
10. 0.130 123,592.160 ↑ 2,502.8 153 1

Hash Join (cost=9,010.45..412,697.27 rows=382,921 width=180) (actual time=6,478.581..123,592.160 rows=153 loops=1)

  • Hash Cond: (i.c_doctype_id = d.c_doctype_id)
11. 0.275 123,591.976 ↑ 2,502.8 153 1

Hash Join (cost=9,004.82..407,426.47 rows=382,921 width=182) (actual time=6,478.510..123,591.976 rows=153 loops=1)

  • Hash Cond: (i.c_bpartner_location_id = bpl.c_bpartner_location_id)
12. 123,524.384 123,574.457 ↑ 2,502.8 153 1

Bitmap Heap Scan on c_invoice i (cost=7,638.90..400,795.39 rows=382,921 width=189) (actual time=6,461.214..123,574.457 rows=153 loops=1)

  • Recheck Cond: (COALESCE(pay_bpartner_id, c_bpartner_id) = '1000016'::numeric)
  • Filter: ((ispayschedulevalid <> 'Y'::bpchar) AND (docstatus = ANY ('{CO,CL}'::bpchar[])) AND (ad_client_id = ANY ('{0,1000000}'::numeric[])) AND (invoiceopentodate(c_invoice_id, NULL::numeric, '2019-11-01 23:59:59+02'::timestamp with time zone) <> '0'::numeric))
  • Rows Removed by Filter: 369404
  • Heap Blocks: exact=111391
13. 50.073 50.073 ↑ 1.0 369,557 1

Bitmap Index Scan on c_invoice_cobpartner (cost=0.00..7,543.17 rows=384,898 width=0) (actual time=50.073..50.073 rows=369,557 loops=1)

  • Index Cond: (COALESCE(pay_bpartner_id, c_bpartner_id) = '1000016'::numeric)
14. 2.550 17.244 ↑ 1.0 14,523 1

Hash (cost=1,182.46..1,182.46 rows=14,677 width=8) (actual time=17.244..17.244 rows=14,523 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 700kB
15. 6.775 14.694 ↑ 1.0 14,523 1

Hash Join (cost=576.88..1,182.46 rows=14,677 width=8) (actual time=5.362..14.694 rows=14,523 loops=1)

  • Hash Cond: (bpl.c_location_id = loc.c_location_id)
16. 2.610 2.610 ↑ 1.0 14,523 1

Seq Scan on c_bpartner_location bpl (cost=0.00..403.77 rows=14,677 width=16) (actual time=0.004..2.610 rows=14,523 loops=1)

17. 2.488 5.309 ↑ 1.0 14,969 1

Hash (cost=389.17..389.17 rows=15,017 width=8) (actual time=5.309..5.309 rows=14,969 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 717kB
18. 2.821 2.821 ↑ 1.0 14,969 1

Seq Scan on c_location loc (cost=0.00..389.17 rows=15,017 width=8) (actual time=0.003..2.821 rows=14,969 loops=1)

19. 0.025 0.054 ↑ 1.0 117 1

Hash (cost=4.17..4.17 rows=117 width=10) (actual time=0.054..0.054 rows=117 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
20. 0.029 0.029 ↑ 1.0 117 1

Seq Scan on c_doctype d (cost=0.00..4.17 rows=117 width=10) (actual time=0.004..0.029 rows=117 loops=1)

21. 2.242 9.553 ↓ 1.0 11,921 1

Hash (cost=582.20..582.20 rows=11,920 width=8) (actual time=9.553..9.553 rows=11,921 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 600kB
22. 7.311 7.311 ↓ 1.0 11,921 1

Seq Scan on c_bpartner b (cost=0.00..582.20 rows=11,920 width=8) (actual time=0.001..7.311 rows=11,921 loops=1)

23. 0.009 0.022 ↑ 1.0 15 1

Hash (cost=1.15..1.15 rows=15 width=80) (actual time=0.022..0.022 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 0.013 0.013 ↑ 1.0 15 1

Seq Scan on c_paymentterm p (cost=0.00..1.15 rows=15 width=80) (actual time=0.010..0.013 rows=15 loops=1)

25. 0.001 755.075 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=7,544.02..303,538.66 rows=1 width=257) (actual time=755.075..755.075 rows=0 loops=1)

26. 0.000 755.074 ↓ 0.0 0 1

Nested Loop (cost=7,544.02..303,538.65 rows=1 width=257) (actual time=755.074..755.074 rows=0 loops=1)

  • Join Filter: ((i_1.c_invoice_id = ips.c_invoice_id) AND (invoiceopentodate(i_1.c_invoice_id, ips.c_invoicepayschedule_id, '2019-11-01 23:59:59+02'::timestamp with time zone) <> '0'::numeric))
27. 0.002 755.074 ↓ 0.0 0 1

Nested Loop (cost=7,544.02..303,536.59 rows=1 width=180) (actual time=755.074..755.074 rows=0 loops=1)

28. 0.000 755.072 ↓ 0.0 0 1

Nested Loop (cost=7,543.74..303,535.17 rows=1 width=188) (actual time=755.072..755.072 rows=0 loops=1)

29. 0.000 755.072 ↓ 0.0 0 1

Nested Loop (cost=7,543.45..303,526.86 rows=1 width=187) (actual time=755.072..755.072 rows=0 loops=1)

30. 0.001 755.072 ↓ 0.0 0 1

Nested Loop (cost=7,543.17..303,518.54 rows=1 width=187) (actual time=755.072..755.072 rows=0 loops=1)

  • Join Filter: (i_1.c_doctype_id = d_1.c_doctype_id)
31. 702.331 755.071 ↓ 0.0 0 1

Bitmap Heap Scan on c_invoice i_1 (cost=7,543.17..303,512.91 rows=1 width=189) (actual time=755.071..755.071 rows=0 loops=1)

  • Recheck Cond: (COALESCE(pay_bpartner_id, c_bpartner_id) = '1000016'::numeric)
  • Filter: ((docstatus = ANY ('{CO,CL}'::bpchar[])) AND (ad_client_id = ANY ('{0,1000000}'::numeric[])) AND (ispayschedulevalid = 'Y'::bpchar))
  • Rows Removed by Filter: 369557
  • Heap Blocks: exact=111391
32. 52.740 52.740 ↑ 1.0 369,557 1

Bitmap Index Scan on c_invoice_cobpartner (cost=0.00..7,543.17 rows=384,898 width=0) (actual time=52.740..52.740 rows=369,557 loops=1)

  • Index Cond: (COALESCE(pay_bpartner_id, c_bpartner_id) = '1000016'::numeric)
33. 0.000 0.000 ↓ 0.0 0

Seq Scan on c_doctype d_1 (cost=0.00..4.17 rows=117 width=10) (never executed)

34. 0.000 0.000 ↓ 0.0 0

Index Only Scan using c_bpartner_pkey on c_bpartner b_1 (cost=0.29..8.30 rows=1 width=8) (never executed)

  • Index Cond: (c_bpartner_id = i_1.c_bpartner_id)
  • Heap Fetches: 0
35. 0.000 0.000 ↓ 0.0 0

Index Scan using c_bpartner_location_pkey on c_bpartner_location bpl_1 (cost=0.29..8.30 rows=1 width=16) (never executed)

  • Index Cond: (c_bpartner_location_id = i_1.c_bpartner_location_id)
36. 0.000 0.000 ↓ 0.0 0

Index Only Scan using c_location_pkey on c_location loc_1 (cost=0.29..0.38 rows=1 width=8) (never executed)

  • Index Cond: (c_location_id = bpl_1.c_location_id)
  • Heap Fetches: 0
37. 0.000 0.000 ↓ 0.0 0

Seq Scan on c_invoicepayschedule ips (cost=0.00..1.02 rows=1 width=112) (never executed)

  • Filter: (isvalid = 'Y'::bpchar)