explain.depesz.com

PostgreSQL's explain analyze made readable

Result: I05

Settings
# exclusive inclusive rows x rows loops node
1. 89.657 51,812.101 ↓ 1.1 47,791 1

Sort (cost=197,033.39..197,146.84 rows=45,379 width=305) (actual time=51,809.500..51,812.101 rows=47,791 loops=1)

  • Sort Key: "*SELECT* 1".c_bpartner_id, "*SELECT* 1".c_currency_id, "*SELECT* 1".c_invoice_id
  • Sort Method: quicksort Memory: 8265kB
2. 14,339.135 51,722.444 ↓ 1.1 47,791 1

Hash Join (cost=168,553.65..193,523.39 rows=45,379 width=305) (actual time=37,341.714..51,722.444 rows=47,791 loops=1)

  • Hash Cond: ("*SELECT* 1".c_bpartner_id = bp.c_bpartner_id)
3. 142.472 37,373.556 ↑ 1.9 47,791 1

HashAggregate (cost=167,193.13..168,100.66 rows=90,753 width=506) (actual time=37,330.957..37,373.556 rows=47,791 loops=1)

  • Group 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".user1_id, "*SELECT* 1".user2_id, "*SELECT* 1".c_doctype_id, "*SELECT* 1".cof_c_custody_id
4. 6.903 37,231.084 ↑ 1.9 47,791 1

Append (cost=4,099.24..160,840.42 rows=90,753 width=506) (actual time=74.336..37,231.084 rows=47,791 loops=1)

5. 0.265 4,838.581 ↑ 29.2 524 1

Subquery Scan on *SELECT* 1 (cost=4,099.24..61,068.55 rows=15,276 width=243) (actual time=74.335..4,838.581 rows=524 loops=1)

6. 39.916 4,838.316 ↑ 29.2 524 1

Hash Join (cost=4,099.24..60,877.60 rows=15,276 width=239) (actual time=74.334..4,838.316 rows=524 loops=1)

  • Hash Cond: (i.c_paymentterm_id = p.c_paymentterm_id)
7. 0.489 4,798.379 ↑ 29.2 524 1

Hash Left Join (cost=4,096.01..33,678.08 rows=15,276 width=134) (actual time=73.377..4,798.379 rows=524 loops=1)

  • Hash Cond: (i.c_invoice_id = df.c_invoice_id)
8. 0.453 4,791.568 ↑ 29.2 524 1

Hash Join (cost=3,184.01..32,649.48 rows=15,276 width=134) (actual time=66.980..4,791.568 rows=524 loops=1)

  • Hash Cond: (bpl.c_location_id = loc.c_location_id)
9. 0.426 4,786.894 ↑ 29.2 524 1

Hash Join (cost=2,557.25..31,982.60 rows=15,276 width=140) (actual time=62.688..4,786.894 rows=524 loops=1)

  • Hash Cond: (i.c_bpartner_location_id = bpl.c_bpartner_location_id)
10. 0.415 4,778.659 ↑ 29.2 524 1

Hash Join (cost=595.20..29,980.44 rows=15,276 width=140) (actual time=54.868..4,778.659 rows=524 loops=1)

  • Hash Cond: (i.c_bpartner_id = b.c_bpartner_id)
11. 0.649 4,773.804 ↑ 29.2 524 1

Hash Join (cost=13.92..29,359.05 rows=15,276 width=140) (actual time=50.412..4,773.804 rows=524 loops=1)

  • Hash Cond: (i.c_doctype_id = d.c_doctype_id)
12. 4,773.085 4,773.085 ↑ 29.2 524 1

Index Scan using idx_c_invoice_ad_client_id_ad_org_id on c_invoice i (cost=0.42..29,304.60 rows=15,276 width=142) (actual time=50.338..4,773.085 rows=524 loops=1)

  • Index Cond: ((ad_client_id = ANY ('{0,1000000}'::numeric[])) AND (ad_org_id = ANY ('{0,5000007,5000004,5000003,1000001,5000006,5000005,5000000,5000002,5000001}'::numeric[])))
  • Filter: ((ispayschedulevalid <> 'Y'::bpchar) AND (docstatus = ANY ('{CO,CL}'::bpchar[])) AND (issotrx = 'Y'::bpchar) AND (invoiceopentodate(c_invoice_id, NULL::numeric, to_timestamp('2018-12-01'::text, 'YYYY-MM-DD'::text)) <> '0'::numeric))
  • Rows Removed by Filter: 111809
13. 0.026 0.070 ↑ 1.0 200 1

Hash (cost=11.00..11.00 rows=200 width=10) (actual time=0.070..0.070 rows=200 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
14. 0.044 0.044 ↑ 1.0 200 1

Seq Scan on c_doctype d (cost=0.00..11.00 rows=200 width=10) (actual time=0.003..0.044 rows=200 loops=1)

15. 2.284 4.440 ↑ 1.0 18,967 1

Hash (cost=344.19..344.19 rows=18,967 width=6) (actual time=4.440..4.440 rows=18,967 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 979kB
16. 2.156 2.156 ↑ 1.0 18,967 1

Index Only Scan using c_bpartner_pkey on c_bpartner b (cost=0.29..344.19 rows=18,967 width=6) (actual time=0.020..2.156 rows=18,967 loops=1)

  • Heap Fetches: 3129
17. 2.694 7.809 ↓ 1.0 20,181 1

Hash (cost=1,709.80..1,709.80 rows=20,180 width=12) (actual time=7.809..7.809 rows=20,181 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1163kB
18. 5.115 5.115 ↓ 1.0 20,181 1

Seq Scan on c_bpartner_location bpl (cost=0.00..1,709.80 rows=20,180 width=12) (actual time=0.006..5.115 rows=20,181 loops=1)

19. 2.485 4.221 ↓ 1.0 20,462 1

Hash (cost=371.00..371.00 rows=20,461 width=6) (actual time=4.221..4.221 rows=20,462 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1036kB
20. 1.736 1.736 ↓ 1.0 20,462 1

Index Only Scan using c_location_pkey on c_location loc (cost=0.29..371.00 rows=20,461 width=6) (actual time=0.025..1.736 rows=20,462 loops=1)

  • Heap Fetches: 34
21. 3.605 6.322 ↑ 1.0 29,582 1

Hash (cost=541.08..541.08 rows=29,673 width=6) (actual time=6.322..6.322 rows=29,582 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1383kB
22. 2.717 2.717 ↓ 1.0 29,677 1

Index Only Scan using idx_lbr_docfiscal_c_invoice_id on lbr_docfiscal df (cost=0.29..541.08 rows=29,673 width=6) (actual time=0.020..2.717 rows=29,677 loops=1)

  • Heap Fetches: 459
23. 0.008 0.021 ↑ 1.0 55 1

Hash (cost=2.55..2.55 rows=55 width=15) (actual time=0.021..0.021 rows=55 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
24. 0.013 0.013 ↑ 1.0 55 1

Seq Scan on c_paymentterm p (cost=0.00..2.55 rows=55 width=15) (actual time=0.003..0.013 rows=55 loops=1)

25. 24.309 32,385.600 ↑ 1.6 47,267 1

Subquery Scan on *SELECT* 2 (cost=14,751.44..99,771.87 rows=75,477 width=191) (actual time=218.961..32,385.600 rows=47,267 loops=1)

26. 32,084.746 32,361.291 ↑ 1.6 47,267 1

Hash Join (cost=14,751.44..98,639.72 rows=75,477 width=163) (actual time=218.960..32,361.291 rows=47,267 loops=1)

  • Hash Cond: (ips.c_invoice_id = i_1.c_invoice_id)
  • Join Filter: (invoiceopentodate(i_1.c_invoice_id, ips.c_invoicepayschedule_id, to_timestamp('2018-12-01'::text, 'YYYY-MM-DD'::text)) <> '0'::numeric)
  • Rows Removed by Join Filter: 91548
27. 59.965 59.965 ↓ 1.0 162,004 1

Seq Scan on c_invoicepayschedule ips (cost=0.00..6,005.23 rows=161,969 width=37) (actual time=0.004..59.965 rows=162,004 loops=1)

  • Filter: (isvalid = 'Y'::bpchar)
  • Rows Removed by Filter: 51
28. 23.743 216.580 ↓ 1.4 73,043 1

Hash (cost=14,093.94..14,093.94 rows=52,600 width=124) (actual time=216.580..216.580 rows=73,043 loops=1)

  • Buckets: 131072 (originally 65536) Batches: 1 (originally 1) Memory Usage: 11781kB
29. 20.074 192.837 ↓ 1.4 73,043 1

Hash Left Join (cost=4,090.35..14,093.94 rows=52,600 width=124) (actual time=21.213..192.837 rows=73,043 loops=1)

  • Hash Cond: (i_1.c_invoice_id = df_1.c_invoice_id)
30. 20.449 166.953 ↓ 1.4 73,043 1

Hash Join (cost=3,178.36..12,780.41 rows=52,600 width=124) (actual time=15.331..166.953 rows=73,043 loops=1)

  • Hash Cond: (bpl_1.c_location_id = loc_1.c_location_id)
31. 22.161 142.535 ↓ 1.4 73,043 1

Hash Join (cost=2,551.59..12,015.54 rows=52,600 width=130) (actual time=11.290..142.535 rows=73,043 loops=1)

  • Hash Cond: (i_1.c_bpartner_location_id = bpl_1.c_bpartner_location_id)
32. 20.431 113.668 ↓ 1.4 73,043 1

Hash Join (cost=589.54..9,915.38 rows=52,600 width=130) (actual time=4.513..113.668 rows=73,043 loops=1)

  • Hash Cond: (i_1.c_bpartner_id = b_1.c_bpartner_id)
33. 24.565 88.897 ↓ 1.4 73,043 1

Hash Join (cost=8.26..9,195.99 rows=52,600 width=130) (actual time=0.098..88.897 rows=73,043 loops=1)

  • Hash Cond: (i_1.c_doctype_id = d_1.c_doctype_id)
34. 64.274 64.274 ↓ 1.4 73,043 1

Index Scan using idx_c_invoice_ad_client_id_ad_org_id on c_invoice i_1 (cost=0.42..9,047.15 rows=52,600 width=136) (actual time=0.031..64.274 rows=73,043 loops=1)

  • Index Cond: ((ad_client_id = ANY ('{0,1000000}'::numeric[])) AND (ad_org_id = ANY ('{0,5000007,5000004,5000003,1000001,5000006,5000005,5000000,5000002,5000001}'::numeric[])))
  • Filter: ((docstatus = ANY ('{CO,CL}'::bpchar[])) AND (ispayschedulevalid = 'Y'::bpchar) AND (issotrx = 'Y'::bpchar))
  • Rows Removed by Filter: 39290
35. 0.025 0.058 ↑ 1.0 200 1

Hash (cost=5.34..5.34 rows=200 width=6) (actual time=0.058..0.058 rows=200 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
36. 0.033 0.033 ↑ 1.0 200 1

Index Only Scan using c_doctype_pkey on c_doctype d_1 (cost=0.14..5.34 rows=200 width=6) (actual time=0.021..0.033 rows=200 loops=1)

  • Heap Fetches: 0
37. 2.284 4.340 ↑ 1.0 18,967 1

Hash (cost=344.19..344.19 rows=18,967 width=6) (actual time=4.340..4.340 rows=18,967 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 979kB
38. 2.056 2.056 ↑ 1.0 18,967 1

Index Only Scan using c_bpartner_pkey on c_bpartner b_1 (cost=0.29..344.19 rows=18,967 width=6) (actual time=0.014..2.056 rows=18,967 loops=1)

  • Heap Fetches: 3125
39. 2.630 6.706 ↓ 1.0 20,181 1

Hash (cost=1,709.80..1,709.80 rows=20,180 width=12) (actual time=6.706..6.706 rows=20,181 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1163kB
40. 4.076 4.076 ↓ 1.0 20,181 1

Seq Scan on c_bpartner_location bpl_1 (cost=0.00..1,709.80 rows=20,180 width=12) (actual time=0.003..4.076 rows=20,181 loops=1)

41. 2.421 3.969 ↓ 1.0 20,462 1

Hash (cost=371.00..371.00 rows=20,461 width=6) (actual time=3.969..3.969 rows=20,462 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1036kB
42. 1.548 1.548 ↓ 1.0 20,462 1

Index Only Scan using c_location_pkey on c_location loc_1 (cost=0.29..371.00 rows=20,461 width=6) (actual time=0.010..1.548 rows=20,462 loops=1)

  • Heap Fetches: 34
43. 3.475 5.810 ↑ 1.0 29,582 1

Hash (cost=541.08..541.08 rows=29,673 width=6) (actual time=5.810..5.810 rows=29,582 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1383kB
44. 2.335 2.335 ↓ 1.0 29,677 1

Index Only Scan using idx_lbr_docfiscal_c_invoice_id on lbr_docfiscal df_1 (cost=0.29..541.08 rows=29,673 width=6) (actual time=0.015..2.335 rows=29,677 loops=1)

  • Heap Fetches: 459
45. 2.875 9.753 ↓ 2.0 18,967 1

Hash (cost=1,241.97..1,241.97 rows=9,484 width=11) (actual time=9.753..9.753 rows=18,967 loops=1)

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 1076kB
46. 6.874 6.878 ↓ 2.0 18,967 1

Seq Scan on c_bpartner bp (cost=5.88..1,241.97 rows=9,484 width=11) (actual time=0.014..6.878 rows=18,967 loops=1)

  • Filter: ((c_bpartner_id IS NULL) OR (NOT (hashed SubPlan 1)))
47.          

SubPlan (forSeq Scan)

48. 0.004 0.004 ↓ 0.0 0 1

Index Scan using ad_private_access_pkey on ad_private_access (cost=0.15..5.88 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (ad_table_id = '291'::numeric)
  • Filter: ((ad_user_id <> '5017140'::numeric) AND (isactive = 'Y'::bpchar))