explain.depesz.com

PostgreSQL's explain analyze made readable

Result: i3n4 : query1_13MB

Settings
# exclusive inclusive rows x rows loops node
1. 0.708 126,752.208 ↑ 2,502.8 153 1

Sort (cost=1,906,199.69..1,907,157.00 rows=382,922 width=249) (actual time=126,752.201..126,752.208 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.087 126,751.500 ↑ 2,502.8 153 1

Nested Loop (cost=1,599,903.82..1,824,878.51 rows=382,922 width=249) (actual time=126,690.070..126,751.500 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.166 126,688.399 ↑ 2,502.8 153 1

Unique (cost=1,599,903.54..1,624,793.47 rows=382,922 width=257) (actual time=126,688.198..126,688.399 rows=153 loops=1)

5. 0.778 126,688.233 ↑ 2,502.8 153 1

Sort (cost=1,599,903.54..1,600,860.84 rows=382,922 width=257) (actual time=126,688.197..126,688.233 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.038 126,687.455 ↑ 2,502.8 153 1

Append (cost=9,742.99..1,517,273.36 rows=382,922 width=257) (actual time=6,636.343..126,687.455 rows=153 loops=1)

7. 0.100 125,959.375 ↑ 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,636.342..125,959.375 rows=153 loops=1)

8. 14.659 125,959.275 ↑ 2,502.8 153 1

Hash Join (cost=9,742.99..1,209,905.49 rows=382,921 width=257) (actual time=6,636.341..125,959.275 rows=153 loops=1)

  • Hash Cond: (i.c_paymentterm_id = p.c_paymentterm_id)
9. 1.976 125,944.595 ↑ 2,502.8 153 1

Hash Join (cost=9,741.65..812,144.96 rows=382,921 width=180) (actual time=6,635.474..125,944.595 rows=153 loops=1)

  • Hash Cond: (i.c_bpartner_id = b.c_bpartner_id)
10. 0.134 125,933.040 ↑ 2,502.8 153 1

Hash Join (cost=9,010.45..412,697.27 rows=382,921 width=180) (actual time=6,625.819..125,933.040 rows=153 loops=1)

  • Hash Cond: (i.c_doctype_id = d.c_doctype_id)
11. 0.267 125,932.850 ↑ 2,502.8 153 1

Hash Join (cost=9,004.82..407,426.47 rows=382,921 width=182) (actual time=6,625.742..125,932.850 rows=153 loops=1)

  • Hash Cond: (i.c_bpartner_location_id = bpl.c_bpartner_location_id)
12. 125,861.038 125,916.149 ↑ 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,609.261..125,916.149 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. 55.111 55.111 ↑ 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=55.111..55.111 rows=369,557 loops=1)

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

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

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

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

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

17. 2.446 5.082 ↑ 1.0 14,969 1

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

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

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

19. 0.021 0.056 ↑ 1.0 117 1

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

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

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

21. 2.262 9.579 ↓ 1.0 11,921 1

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

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

23. 0.006 0.021 ↑ 1.0 15 1

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

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

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

25. 0.000 728.042 ↓ 0.0 0 1

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

26. 0.002 728.042 ↓ 0.0 0 1

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

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

28. 0.000 728.040 ↓ 0.0 0 1

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

29. 0.001 728.040 ↓ 0.0 0 1

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

30. 0.001 728.039 ↓ 0.0 0 1

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

  • Join Filter: (i_1.c_doctype_id = d_1.c_doctype_id)
31. 675.680 728.038 ↓ 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=728.038..728.038 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.358 52.358 ↑ 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.358..52.358 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)