explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UjrQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 136,374.938 ↑ 258.3 153 1

Sort (cost=352,714.07..352,812.87 rows=39,519 width=249) (actual time=136,374.929..136,374.938 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
  • "*SELECT* 1".c_bpartner_id, "*SELECT* 1".issotrx, "*SELECT* 1".dateinvoiced, "*SELECT* 1".dateacct, "*SELECT* 1".netdays, "*SELECT* 1".duedate, "*SELECT* 1".
2. 92.857 136,374.226 ↑ 258.3 153 1

Nested Loop (cost=321,742.82..344,968.25 rows=39,519 width=249) (actual time=136,309.309..136,374.226 rows=153 loops=1)

3. 0.008 0.008 ↑ 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.007..0.008 rows=1 loops=1)

  • Index Cond: (c_bpartner_id = '1000016'::numeric)
4. 0.179 136,281.361 ↑ 258.3 153 1

Unique (cost=321,742.53..324,311.27 rows=39,519 width=257) (actual time=136,281.142..136,281.361 rows=153 loops=1)

5. 0.781 136,281.182 ↑ 258.3 153 1

Sort (cost=321,742.53..321,841.33 rows=39,519 width=257) (actual time=136,281.141..136,281.182 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,
  • Sort Method: quicksort Memory: 65kB
6. 0.202 136,280.401 ↑ 258.3 153 1

Append (cost=2,650.51..313,860.21 rows=39,519 width=257) (actual time=8,544.348..136,280.401 rows=153 loops=1)

  • -> Subquery Scan on "*SELECT* 1" (cost=2650.51..204999.58 rows=39518 width=257) (actual time=8544.348..134907.949 rows=153 loops=
7. 15.742 134,907.804 ↑ 258.3 153 1

Hash Join (cost=2,650.51..204,604.40 rows=39,518 width=257) (actual time=8,544.346..134,907.804 rows=153 loops=1)

  • Hash Cond: (i.c_paymentterm_id = p.c_paymentterm_id)
8. 2.535 134,892.050 ↑ 258.3 153 1

Hash Join (cost=2,649.17..163,553.74 rows=39,518 width=180) (actual time=8,544.204..134,892.050 rows=153 loops=1)

  • Hash Cond: (bpl.c_location_id = loc.c_location_id)
9. 0.450 134,884.162 ↑ 258.3 153 1

Hash Join (cost=2,072.29..121,828.74 rows=39,518 width=188) (actual time=8,538.812..134,884.162 rows=153 loops=1)

  • Hash Cond: (i.c_bpartner_location_id = bpl.c_bpartner_location_id)
  • -> Hash Join (cost=1485.06..120698.14 rows=39518 width=187) (actual time=8529.836..134874.975 rows=153 lo
  • Hash Cond: (i.c_bpartner_id = b.c_bpartner_id)
10. 0.350 134,847.485 ↑ 258.3 153 1

Hash Join (cost=753.86..119,423.57 rows=39,518 width=187) (actual time=8,502.543..134,847.485 rows=153 loops=1)

  • Hash Cond: (i.c_doctype_id = d.c_doctype_id)
11. 134,790.933 134,847.089 ↑ 258.3 153 1

Bitmap Heap Scan on c_invoice i (cost=748.23..118,874.56 rows=39,518 width=189) (actual time=8,502.485..134,847.089 rows=153 loops=1)

  • Recheck Cond: (COALESCE(pay_bpartner_id, c_bpartner_id) = '1000016'::numeric)
  • Rows Removed by Index Recheck: 1742336
  • Rows Removed by Filter: 369404
  • Heap Blocks: exact=43736 lossy=67655
12. 56.156 56.156 ↓ 9.3 369,557 1

Bitmap Index Scan on c_invoice_cobpartner (cost=0.00..738.35 rows=39,722 width=0) (actual time=56.156..56.156 rows=369,557 loops=1)

  • Index Cond: (COALESCE(pay_bpartner_id, c_bpartner_id) = '1000016'::numeric)
13. 0.020 0.046 ↑ 1.0 117 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
14. 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)

15. 1.988 27.271 ↓ 1.0 11,921 1

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

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

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

17. 2.469 8.956 ↑ 1.0 14,523 1

Hash (cost=403.77..403.77 rows=14,677 width=16) (actual time=8.956..8.956 rows=14,523 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 817kB
18. 6.487 6.487 ↑ 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..6.487 rows=14,523 loops=1)

19. 2.185 5.353 ↑ 1.0 14,973 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 717kB
20. 3.168 3.168 ↑ 1.0 14,973 1

Seq Scan on c_location loc (cost=0.00..389.17 rows=15,017 width=8) (actual time=0.002..3.168 rows=14,973 loops=1)

21. 0.007 0.012 ↑ 1.0 15 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.005 0.005 ↑ 1.0 15 1

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

23. 0.002 1,372.395 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=739.20..108,860.62 rows=1 width=257) (actual time=1,372.395..1,372.395 rows=0 loops=1)

24. 0.000 1,372.393 ↓ 0.0 0 1

Nested Loop (cost=739.20..108,860.61 rows=1 width=257) (actual time=1,372.393..1,372.393 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))
25. 0.000 1,372.393 ↓ 0.0 0 1

Nested Loop (cost=739.20..108,858.55 rows=1 width=180) (actual time=1,372.393..1,372.393 rows=0 loops=1)

26. 0.001 1,372.393 ↓ 0.0 0 1

Nested Loop (cost=738.92..108,857.13 rows=1 width=188) (actual time=1,372.393..1,372.393 rows=0 loops=1)

27. 1,372.392 1,372.392 ↓ 0.0 0 1

Nested Loop (cost=738.63..108,848.82 rows=1 width=187) (actual time=1,372.392..1,372.392 rows=0 loops=1)

28. 0.000 1,372.391 ↓ 0.0 0 1

Nested Loop (cost=738.35..108,840.51 rows=1 width=187) (actual time=1,372.391..1,372.391 rows=0 loops=1)

  • Join Filter: (i_1.c_doctype_id = d_1.c_doctype_id)
29. 1,314.065 1,372.391 ↓ 0.0 0 1

Bitmap Heap Scan on c_invoice i_1 (cost=738.35..108,834.88 rows=1 width=189) (actual time=1,372.391..1,372.391 rows=0 loops=1)

  • Recheck Cond: (COALESCE(pay_bpartner_id, c_bpartner_id) = '1000016'::numeric)
  • Rows Removed by Index Recheck: 1742336
  • 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=43736 lossy=67655
30. 58.326 58.326 ↓ 9.3 369,557 1

Bitmap Index Scan on c_invoice_cobpartner (cost=0.00..738.35 rows=39,722 width=0) (actual time=58.326..58.326 rows=369,557 loops=1)

  • Index Cond: (COALESCE(pay_bpartner_id, c_bpartner_id) = '1000016'::numeric)
31. 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)

32. 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
33. 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)
34. 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
35. 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)