explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WZi6

Settings
# exclusive inclusive rows x rows loops node
1. 7.869 2,372.870 ↑ 2.7 1,353 1

HashAggregate (cost=37,231.73..37,268.87 rows=3,714 width=3,648) (actual time=2,371.538..2,372.870 rows=1,353 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".paidamt, "*SELECT* 1".openamt, "*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".ad_orgtrx_id, "*SELECT* 1".ad_user_id, "*SELECT* 1".c_bpartner_location_id, "*SELECT* 1".c_charge_id, "*SELECT* 1".c_doctype_id, "*SELECT* 1".c_doctypetarget_id, "*SELECT* 1".c_dunninglevel_id, "*SELECT* 1".chargeamt, "*SELECT* 1".c_payment_id, "*SELECT* 1".created, "*SELECT* 1".createdby, "*SELECT* 1".dateordered, "*SELECT* 1".dateprinted, "*SELECT* 1".description, "*SELECT* 1".docaction, "*SELECT* 1".docstatus, "*SELECT* 1".dunninggrace, "*SELECT* 1".generateto, "*SELECT* 1".isactive, "*SELECT* 1".isapproved, "*SELECT* 1".isdiscountprinted, "*SELECT* 1".isindispute, "*SELECT* 1".ispaid, "*SELECT* 1".isprinted, "*SELECT* 1".isselfservice, "*SELECT* 1".istaxincluded, "*SELECT* 1".istransferred, "*SELECT* 1".m_pricelist_id, "*SELECT* 1".m_rma_id, "*SELECT* 1".paymentrule, "*SELECT* 1".poreference, "*SELECT* 1".posted, "*SELECT* 1".processedon, "*SELECT* 1".processing, "*SELECT* 1".ref_invoice_id, "*SELECT* 1".reversal_id, "*SELECT* 1".salesrep_id, "*SELECT* 1".sendemail, "*SELECT* 1".totallines, "*SELECT* 1".updated, "*SELECT* 1".updatedby, "*SELECT* 1".user1_id, "*SELECT* 1".user2_id, "*SELECT* 1".ide_nnf, "*SELECT* 1".lbr_nfestatus, "*SELECT* 1".ide_serie, "*SELECT* 1".ide_tpnf, "*SELECT* 1".cof_processed, "*SELECT* 1".cof_processed2, "*SELECT* 1".cof_borderoname, "*SELECT* 1".cof_titulo_nossonumero, "*SELECT* 1".cof_bankintegrationname, "*SELECT* 1".cof_occurrencevalue, "*SELECT* 1".cof_occurrencename, "*SELECT* 1".cof_titulo_id, "*SELECT* 1".cof_recordhistory, "*SELECT* 1".lbr_transactiontype, "*SELECT* 1".infnfe_id, "*SELECT* 1".lbr_nfentrada, "*SELECT* 1".cof_serienfentrada, "*SELECT* 1".lbr_nfmodel, "*SELECT* 1".cof_otheramt, "*SELECT* 1".cof_insuranceamt, "*SELECT* 1".cof_modfrete, "*SELECT* 1".cof_sourceinvoice_id, "*SELECT* 1".cof_composescashflow, "*SELECT* 1".cof_payfrom_location_id, "*SELECT* 1".cof_orgst_id, "*SELECT* 1".cof_isindustrialization, "*SELECT* 1".lbr_docfiscal_id, "*SELECT* 1".cof_qtycheck, "*SELECT* 1".c_bp_group_id, "*SELECT* 1".cof_c_custody_id, "*SELECT* 1".bpartner_value, "*SELECT* 1".cof_c_planofinanceiro_id, "*SELECT* 1".cof_openamtconverted, "*SELECT* 1".cof_grandtotalconverted, "*SELECT* 1".cof_exibiremrelatorios, "*SELECT* 1".rv_openitem_id, (NULL::character varying), "*SELECT* 1".cof_c_bankintegration_id, "*SELECT* 1".cof_c_bankoccurrence_id, (NULL::timestamp without time zone), ('N'::text), "*SELECT* 1".cof_titulo_numdoc, "*SELECT* 1".cof_checkallocateamt
2. 0.195 2,365.001 ↑ 2.7 1,353 1

Append (cost=67.82..36,191.81 rows=3,714 width=3,648) (actual time=398.392..2,365.001 rows=1,353 loops=1)

3. 0.000 21.072 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=67.82..11,152.83 rows=738 width=1,281) (actual time=21.072..21.072 rows=0 loops=1)

4. 0.001 21.072 ↓ 0.0 0 1

Nested Loop Left Join (cost=67.82..11,143.60 rows=738 width=1,277) (actual time=21.072..21.072 rows=0 loops=1)

  • Join Filter: (bpg.c_bp_group_id = bp.c_bp_group_id)
5. 0.000 21.071 ↓ 0.0 0 1

Nested Loop Left Join (cost=67.82..8,084.05 rows=738 width=829) (actual time=21.071..21.071 rows=0 loops=1)

  • Join Filter: (bp.c_bpartner_id = i.c_bpartner_id)
6. 0.000 21.071 ↓ 0.0 0 1

Hash Left Join (cost=67.53..8,070.47 rows=738 width=810) (actual time=21.071..21.071 rows=0 loops=1)

  • Hash Cond: (t.cof_c_bankoccurrence_id = bo.cof_c_bankoccurrence_id)
7. 0.000 21.071 ↓ 0.0 0 1

Hash Left Join (cost=61.87..8,062.82 rows=738 width=776) (actual time=21.071..21.071 rows=0 loops=1)

  • Hash Cond: (t.cof_c_bankintegration_id = bi.cof_c_bankintegration_id)
8. 0.000 21.071 ↓ 0.0 0 1

Nested Loop Left Join (cost=59.45..8,058.02 rows=738 width=751) (actual time=21.071..21.071 rows=0 loops=1)

9. 0.001 21.071 ↓ 0.0 0 1

Nested Loop Left Join (cost=59.03..5,991.69 rows=738 width=545) (actual time=21.071..21.071 rows=0 loops=1)

10. 0.002 21.070 ↓ 0.0 0 1

Hash Join (cost=58.74..4,828.97 rows=738 width=529) (actual time=21.070..21.070 rows=0 loops=1)

  • Hash Cond: (i.c_paymentterm_id = p.c_paymentterm_id)
11. 0.000 21.033 ↓ 0.0 0 1

Nested Loop Left Join (cost=55.21..4,823.38 rows=738 width=520) (actual time=21.033..21.033 rows=0 loops=1)

12. 0.000 21.033 ↓ 0.0 0 1

Nested Loop (cost=54.92..4,439.47 rows=738 width=520) (actual time=21.033..21.033 rows=0 loops=1)

13. 0.001 21.033 ↓ 0.0 0 1

Nested Loop (cost=54.63..4,212.01 rows=738 width=526) (actual time=21.032..21.033 rows=0 loops=1)

14. 0.002 21.032 ↓ 0.0 0 1

Nested Loop (cost=54.34..3,434.72 rows=738 width=520) (actual time=21.032..21.032 rows=0 loops=1)

15. 0.008 0.008 ↑ 1.0 1 1

Index Only Scan using c_bpartner_pkey on c_bpartner b (cost=0.29..2.51 rows=1 width=6) (actual time=0.008..0.008 rows=1 loops=1)

  • Index Cond: (c_bpartner_id = '5005922'::numeric)
  • Heap Fetches: 1
16. 0.002 21.022 ↓ 0.0 0 1

Hash Join (cost=54.06..3,424.83 rows=738 width=520) (actual time=21.022..21.022 rows=0 loops=1)

  • Hash Cond: (i.c_doctype_id = d.c_doctype_id)
17. 20.689 20.944 ↓ 0.0 0 1

Bitmap Heap Scan on c_invoice i (cost=39.35..3,408.15 rows=738 width=516) (actual time=20.944..20.944 rows=0 loops=1)

  • Recheck Cond: (c_bpartner_id = '5005922'::numeric)
  • Filter: ((ispayschedulevalid <> 'Y'::bpchar) AND (docstatus = ANY ('{CO,CL}'::bpchar[])) AND (invoiceopen(c_invoice_id, '0'::numeric) <> '0'::numeric))
  • Rows Removed by Filter: 2656
  • Heap Blocks: exact=1249
18. 0.255 0.255 ↑ 1.1 2,674 1

Bitmap Index Scan on c_invoice_documentno_target (cost=0.00..39.17 rows=2,820 width=0) (actual time=0.255..0.255 rows=2,674 loops=1)

  • Index Cond: (c_bpartner_id = '5005922'::numeric)
19. 0.032 0.076 ↑ 1.0 209 1

Hash (cost=12.09..12.09 rows=209 width=10) (actual time=0.076..0.076 rows=209 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
20. 0.044 0.044 ↑ 1.0 209 1

Seq Scan on c_doctype d (cost=0.00..12.09 rows=209 width=10) (actual time=0.002..0.044 rows=209 loops=1)

21. 0.000 0.000 ↓ 0.0 0

Index Scan using c_bpartner_location_pkey on c_bpartner_location bpl (cost=0.29..1.05 rows=1 width=12) (never executed)

  • Index Cond: (c_bpartner_location_id = i.c_bpartner_location_id)
22. 0.000 0.000 ↓ 0.0 0

Index Only Scan using c_location_pkey on c_location loc (cost=0.29..0.31 rows=1 width=6) (never executed)

  • Index Cond: (c_location_id = bpl.c_location_id)
  • Heap Fetches: 0
23. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_lbr_docfiscal_c_invoice_id on lbr_docfiscal df_1 (cost=0.29..0.51 rows=1 width=6) (never executed)

  • Index Cond: (c_invoice_id = i.c_invoice_id)
  • Heap Fetches: 0
24. 0.014 0.035 ↑ 1.0 68 1

Hash (cost=2.68..2.68 rows=68 width=15) (actual time=0.035..0.035 rows=68 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
25. 0.021 0.021 ↑ 1.0 68 1

Seq Scan on c_paymentterm p (cost=0.00..2.68 rows=68 width=15) (actual time=0.004..0.021 rows=68 loops=1)

26. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_lbr_docfiscal_c_invoice_id on lbr_docfiscal df (cost=0.29..1.57 rows=1 width=22) (never executed)

  • Index Cond: (c_invoice_id = i.c_invoice_id)
27. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_cof_titulo_c_invoice_id_c_invoicepayschedule_id_isvalid on cof_titulo t (cost=0.42..2.79 rows=1 width=212) (never executed)

  • Index Cond: ((c_invoice_id = i.c_invoice_id) AND (isvalid = 'Y'::bpchar))
  • Filter: (COALESCE(c_invoicepayschedule_id, '0'::numeric) = '0'::numeric)
28. 0.000 0.000 ↓ 0.0 0

Hash (cost=2.19..2.19 rows=19 width=31) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Seq Scan on cof_c_bankintegration bi (cost=0.00..2.19 rows=19 width=31) (never executed)

30. 0.000 0.000 ↓ 0.0 0

Hash (cost=4.18..4.18 rows=118 width=41) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Seq Scan on cof_c_bankoccurrence bo (cost=0.00..4.18 rows=118 width=41) (never executed)

32. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.29..2.51 rows=1 width=25) (never executed)

33. 0.000 0.000 ↓ 0.0 0

Index Scan using c_bpartner_pkey on c_bpartner bp (cost=0.29..2.51 rows=1 width=25) (never executed)

  • Index Cond: (c_bpartner_id = '5005922'::numeric)
34. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..1.09 rows=6 width=6) (never executed)

35. 0.000 0.000 ↓ 0.0 0

Seq Scan on c_bp_group bpg (cost=0.00..1.06 rows=6 width=6) (never executed)

36. 1.219 2,343.734 ↑ 2.2 1,353 1

Subquery Scan on *SELECT* 2 (cost=3,786.23..25,038.98 rows=2,976 width=1,208) (actual time=377.319..2,343.734 rows=1,353 loops=1)

37. 753.986 2,342.515 ↑ 2.2 1,353 1

Hash Left Join (cost=3,786.23..24,994.34 rows=2,976 width=1,180) (actual time=377.318..2,342.515 rows=1,353 loops=1)

  • Hash Cond: (bp_1.c_bp_group_id = bpg_1.c_bp_group_id)
38. 0.590 1,588.525 ↑ 2.2 1,353 1

Hash Left Join (cost=3,785.10..15,888.20 rows=2,976 width=862) (actual time=376.644..1,588.525 rows=1,353 loops=1)

  • Hash Cond: (t_1.cof_c_bankoccurrence_id = bo_1.cof_c_bankoccurrence_id)
39. 0.851 1,587.900 ↑ 2.2 1,353 1

Hash Left Join (cost=3,779.44..15,874.51 rows=2,976 width=828) (actual time=376.607..1,587.900 rows=1,353 loops=1)

  • Hash Cond: (t_1.cof_c_bankintegration_id = bi_1.cof_c_bankintegration_id)
40. 1.377 1,587.037 ↑ 2.2 1,353 1

Nested Loop Left Join (cost=3,777.01..15,862.52 rows=2,976 width=803) (actual time=376.588..1,587.037 rows=1,353 loops=1)

  • Join Filter: (t_1.c_invoice_id = i_1.c_invoice_id)
41. 0.738 1,582.954 ↑ 2.2 1,353 1

Nested Loop (cost=3,776.60..14,365.18 rows=2,976 width=597) (actual time=376.581..1,582.954 rows=1,353 loops=1)

42. 1.613 32.272 ↓ 1.3 2,508 1

Hash Left Join (cost=3,776.18..7,886.82 rows=2,006 width=549) (actual time=12.049..32.272 rows=2,508 loops=1)

  • Hash Cond: (i_1.c_bpartner_id = bp_1.c_bpartner_id)
43. 1.888 30.654 ↓ 1.3 2,508 1

Nested Loop Left Join (cost=3,773.66..7,856.72 rows=2,006 width=530) (actual time=12.040..30.654 rows=2,508 loops=1)

44. 1.159 26.258 ↓ 1.3 2,508 1

Nested Loop Left Join (cost=3,773.37..5,090.21 rows=2,006 width=514) (actual time=12.037..26.258 rows=2,508 loops=1)

45. 0.856 20.083 ↓ 1.3 2,508 1

Nested Loop (cost=3,773.08..4,301.51 rows=2,006 width=514) (actual time=12.031..20.083 rows=2,508 loops=1)

46. 0.006 0.006 ↑ 1.0 1 1

Index Only Scan using c_bpartner_pkey on c_bpartner b_1 (cost=0.29..2.51 rows=1 width=6) (actual time=0.005..0.006 rows=1 loops=1)

  • Index Cond: (c_bpartner_id = '5005922'::numeric)
  • Heap Fetches: 1
47. 1.864 19.221 ↓ 1.3 2,508 1

Hash Join (cost=3,772.79..4,278.94 rows=2,006 width=514) (actual time=12.024..19.221 rows=2,508 loops=1)

  • Hash Cond: (i_1.c_doctype_id = d_1.c_doctype_id)
48. 4.598 17.295 ↓ 1.3 2,508 1

Hash Join (cost=3,758.09..4,258.86 rows=2,006 width=510) (actual time=11.958..17.295 rows=2,508 loops=1)

  • Hash Cond: (loc_1.c_location_id = bpl_1.c_location_id)
49. 1.666 1.666 ↑ 1.0 21,917 1

Index Only Scan using c_location_pkey on c_location loc_1 (cost=0.29..398.72 rows=21,942 width=6) (actual time=0.009..1.666 rows=21,917 loops=1)

  • Heap Fetches: 1066
50. 1.352 11.031 ↓ 1.3 2,508 1

Hash (cost=3,732.73..3,732.73 rows=2,006 width=516) (actual time=11.031..11.031 rows=2,508 loops=1)

  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 946kB
51. 1.174 9.679 ↓ 1.3 2,508 1

Hash Join (cost=1,070.72..3,732.73 rows=2,006 width=516) (actual time=6.761..9.679 rows=2,508 loops=1)

  • Hash Cond: (i_1.c_bpartner_location_id = bpl_1.c_bpartner_location_id)
52. 1.861 2.062 ↓ 1.3 2,508 1

Bitmap Heap Scan on c_invoice i_1 (cost=39.67..2,696.42 rows=2,006 width=510) (actual time=0.307..2.062 rows=2,508 loops=1)

  • Recheck Cond: (c_bpartner_id = '5005922'::numeric)
  • Filter: ((docstatus = ANY ('{CO,CL}'::bpchar[])) AND (ispayschedulevalid = 'Y'::bpchar))
  • Rows Removed by Filter: 148
  • Heap Blocks: exact=1249
53. 0.201 0.201 ↑ 1.1 2,674 1

Bitmap Index Scan on c_invoice_documentno_target (cost=0.00..39.17 rows=2,820 width=0) (actual time=0.201..0.201 rows=2,674 loops=1)

  • Index Cond: (c_bpartner_id = '5005922'::numeric)
54. 2.659 6.443 ↓ 1.0 21,615 1

Hash (cost=761.02..761.02 rows=21,602 width=12) (actual time=6.443..6.443 rows=21,615 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1227kB
55. 3.784 3.784 ↓ 1.0 21,615 1

Seq Scan on c_bpartner_location bpl_1 (cost=0.00..761.02 rows=21,602 width=12) (actual time=0.002..3.784 rows=21,615 loops=1)

56. 0.026 0.062 ↑ 1.0 209 1

Hash (cost=12.09..12.09 rows=209 width=10) (actual time=0.062..0.062 rows=209 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
57. 0.036 0.036 ↑ 1.0 209 1

Seq Scan on c_doctype d_1 (cost=0.00..12.09 rows=209 width=10) (actual time=0.002..0.036 rows=209 loops=1)

58. 5.016 5.016 ↓ 0.0 0 2,508

Index Only Scan using idx_lbr_docfiscal_c_invoice_id on lbr_docfiscal df_3 (cost=0.29..0.38 rows=1 width=6) (actual time=0.002..0.002 rows=0 loops=2,508)

  • Index Cond: (c_invoice_id = i_1.c_invoice_id)
  • Heap Fetches: 0
59. 2.508 2.508 ↓ 0.0 0 2,508

Index Scan using idx_lbr_docfiscal_c_invoice_id on lbr_docfiscal df_2 (cost=0.29..1.37 rows=1 width=22) (actual time=0.001..0.001 rows=0 loops=2,508)

  • Index Cond: (c_invoice_id = i_1.c_invoice_id)
60. 0.001 0.005 ↑ 1.0 1 1

Hash (cost=2.51..2.51 rows=1 width=25) (actual time=0.005..0.005 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
61. 0.004 0.004 ↑ 1.0 1 1

Index Scan using c_bpartner_pkey on c_bpartner bp_1 (cost=0.29..2.51 rows=1 width=25) (actual time=0.004..0.004 rows=1 loops=1)

  • Index Cond: (c_bpartner_id = '5005922'::numeric)
62. 1,549.944 1,549.944 ↑ 3.0 1 2,508

Index Scan using idx_c_invoicepayschedule_c_invoice_id_c_invoicepayschedule_id on c_invoicepayschedule ips (cost=0.42..3.20 rows=3 width=55) (actual time=0.533..0.618 rows=1 loops=2,508)

  • Index Cond: (c_invoice_id = i_1.c_invoice_id)
  • Filter: ((isvalid = 'Y'::bpchar) AND (invoiceopen(i_1.c_invoice_id, c_invoicepayschedule_id) <> '0'::numeric))
  • Rows Removed by Filter: 2
63. 2.706 2.706 ↓ 0.0 0 1,353

Index Scan using idx_cof_titulo_c_invoicepayschedule_id_isvalid_cof_billfoldtype on cof_titulo t_1 (cost=0.42..0.49 rows=1 width=218) (actual time=0.002..0.002 rows=0 loops=1,353)

  • Index Cond: ((c_invoicepayschedule_id = ips.c_invoicepayschedule_id) AND (isvalid = 'Y'::bpchar))
64. 0.005 0.012 ↑ 1.0 19 1

Hash (cost=2.19..2.19 rows=19 width=31) (actual time=0.012..0.012 rows=19 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
65. 0.007 0.007 ↑ 1.0 19 1

Seq Scan on cof_c_bankintegration bi_1 (cost=0.00..2.19 rows=19 width=31) (actual time=0.003..0.007 rows=19 loops=1)

66. 0.018 0.035 ↑ 1.0 118 1

Hash (cost=4.18..4.18 rows=118 width=41) (actual time=0.035..0.035 rows=118 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
67. 0.017 0.017 ↑ 1.0 118 1

Seq Scan on cof_c_bankoccurrence bo_1 (cost=0.00..4.18 rows=118 width=41) (actual time=0.002..0.017 rows=118 loops=1)

68. 0.002 0.004 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=6) (actual time=0.004..0.004 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
69. 0.002 0.002 ↑ 1.0 6 1

Seq Scan on c_bp_group bpg_1 (cost=0.00..1.06 rows=6 width=6) (actual time=0.002..0.002 rows=6 loops=1)

70.          

SubPlan (forHash Left Join)

71. 0.000 0.000 ↑ 1.0 1 1,353

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=1,353)