explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LN74 : query1_16MB

Settings
# exclusive inclusive rows x rows loops node
1. 0.728 129,447.149 ↑ 2,502.8 153 1

Sort (cost=1,906,199.69..1,907,157.00 rows=382,922 width=249) (actual time=129,447.139..129,447.149 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. 86.822 129,446.421 ↑ 2,502.8 153 1

Nested Loop (cost=1,599,903.82..1,824,878.51 rows=382,922 width=249) (actual time=129,384.853..129,446.421 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.169 129,359.585 ↑ 2,502.8 153 1

Unique (cost=1,599,903.54..1,624,793.47 rows=382,922 width=257) (actual time=129,359.380..129,359.585 rows=153 loops=1)

5. 0.784 129,359.416 ↑ 2,502.8 153 1

Sort (cost=1,599,903.54..1,600,860.84 rows=382,922 width=257) (actual time=129,359.379..129,359.416 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.029 129,358.632 ↑ 2,502.8 153 1

Append (cost=9,742.99..1,517,273.36 rows=382,922 width=257) (actual time=6,890.739..129,358.632 rows=153 loops=1)

7. 0.127 128,571.711 ↑ 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,890.738..128,571.711 rows=153 loops=1)

8. 15.110 128,571.584 ↑ 2,502.8 153 1

Hash Join (cost=9,742.99..1,209,905.49 rows=382,921 width=257) (actual time=6,890.737..128,571.584 rows=153 loops=1)

  • Hash Cond: (i.c_paymentterm_id = p.c_paymentterm_id)
9. 2.083 128,556.455 ↑ 2,502.8 153 1

Hash Join (cost=9,741.65..812,144.96 rows=382,921 width=180) (actual time=6,889.842..128,556.455 rows=153 loops=1)

  • Hash Cond: (i.c_bpartner_id = b.c_bpartner_id)
10. 0.151 128,548.301 ↑ 2,502.8 153 1

Hash Join (cost=9,010.45..412,697.27 rows=382,921 width=180) (actual time=6,883.698..128,548.301 rows=153 loops=1)

  • Hash Cond: (i.c_doctype_id = d.c_doctype_id)
11. 0.290 128,548.098 ↑ 2,502.8 153 1

Hash Join (cost=9,004.82..407,426.47 rows=382,921 width=182) (actual time=6,883.632..128,548.098 rows=153 loops=1)

  • Hash Cond: (i.c_bpartner_location_id = bpl.c_bpartner_location_id)
12. 128,339.642 128,530.395 ↑ 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,866.173..128,530.395 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. 190.753 190.753 ↑ 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=190.753..190.753 rows=369,557 loops=1)

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

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

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

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

  • Hash Cond: (bpl.c_location_id = loc.c_location_id)
16. 2.905 2.905 ↑ 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.905 rows=14,523 loops=1)

17. 2.460 5.344 ↑ 1.0 14,969 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 717kB
18. 2.884 2.884 ↑ 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.884 rows=14,969 loops=1)

19. 0.026 0.052 ↑ 1.0 117 1

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

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

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

21. 2.025 6.071 ↓ 1.0 11,921 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 600kB
22. 4.046 4.046 ↓ 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..4.046 rows=11,921 loops=1)

23. 0.009 0.019 ↑ 1.0 15 1

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

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

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

25. 0.001 786.892 ↓ 0.0 0 1

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

26. 0.001 786.891 ↓ 0.0 0 1

Nested Loop (cost=7,544.02..303,538.65 rows=1 width=257) (actual time=786.891..786.891 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.001 786.890 ↓ 0.0 0 1

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

28. 0.000 786.889 ↓ 0.0 0 1

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

29. 0.000 786.889 ↓ 0.0 0 1

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

30. 0.002 786.889 ↓ 0.0 0 1

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

  • Join Filter: (i_1.c_doctype_id = d_1.c_doctype_id)
31. 734.612 786.887 ↓ 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=786.887..786.887 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.275 52.275 ↑ 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.275..52.275 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)