explain.depesz.com

PostgreSQL's explain analyze made readable

Result: h7dL : View con Vista

Settings
# exclusive inclusive rows x rows loops node
1. 100.894 10,093.960 ↓ 3.8 27,309 1

Sort (cost=728,567.47..728,585.34 rows=7,150 width=2,698) (actual time=10,070.427..10,093.960 rows=27,309 loops=1)

  • Sort Key: "*SELECT* 1".value0, "*SELECT* 1".dateacct
  • Sort Method: external merge Disk: 8,920kB
  • Buffers: shared hit=1,195,632, temp read=3,416 written=3,434
  • Functions: 307
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 52.505 ms, Inlining 26.756 ms, Optimization 3513.787 ms, Emission 2228.483 ms, Total 5821.532 ms"Execution Time: 10,152.814 ms
2. 29.840 9,993.066 ↓ 3.8 27,309 1

Unique (cost=717,789.41..718,343.53 rows=7,150 width=2,698) (actual time=9,944.954..9,993.066 rows=27,309 loops=1)

  • Buffers: shared hit=1,195,632, temp read=1,710 written=1,719
3. 93.516 9,963.226 ↓ 3.8 27,309 1

Sort (cost=717,789.41..717,807.28 rows=7,150 width=2,698) (actual time=9,944.951..9,963.226 rows=27,309 loops=1)

  • Sort Key: "*SELECT* 1".fact_acct_id, "*SELECT* 1".saldosa, ((0)::numeric), ((0)::numeric), "*SELECT* 1".saldof, "*SELECT* 1".documentno, "*SELECT* 1".record_id, "*SELECT* 1".c_period_id, "*SELECT* 1".name1, "*SELECT* 1".dateacct, "*SELECT* 1".amtacctdr, "*SELECT* 1".amtacctcr, "*SELECT* 1".saldos, "*SELECT* 1".description1, "*SELECT* 1".account_id, "*SELECT* 1".gr1, "*SELECT* 1".name9, "*SELECT* 1".gr2, "*SELECT* 1".name10, "*SELECT* 1".gr3, "*SELECT* 1".name11, "*SELECT* 1".gr4, "*SELECT* 1".name12, "*SELECT* 1".value0, "*SELECT* 1".name13, "*SELECT* 1".description2, "*SELECT* 1".ad_table_id, "*SELECT* 1".tablename, "*SELECT* 1".name3, "*SELECT* 1".empre99
  • Sort Method: external merge Disk: 8,928kB
  • Buffers: shared hit=1,195,632, temp read=1,710 written=1,719
4. 5.226 9,869.710 ↓ 3.8 27,309 1

Append (cost=208.95..707,565.47 rows=7,150 width=2,698) (actual time=5,773.171..9,869.710 rows=27,309 loops=1)

  • Buffers: shared hit=1,195,632
5. 3.205 7,015.101 ↓ 1.1 7,847 1

Subquery Scan on *SELECT* 1 (cost=208.95..481,619.40 rows=7,044 width=1,092) (actual time=5,773.169..7,015.101 rows=7,847 loops=1)

  • Buffers: shared hit=333,711
6. 44.627 7,011.896 ↓ 1.1 7,847 1

Hash Left Join (cost=208.95..481,513.74 rows=7,044 width=1,036) (actual time=5,773.165..7,011.896 rows=7,847 loops=1)

  • Hash Cond: (f.c_period_id = per.c_period_id)
  • Buffers: shared hit=333,711
7. 6.469 6,232.396 ↓ 1.1 7,847 1

Nested Loop (cost=170.35..179,674.01 rows=7,044 width=289) (actual time=5,772.705..6,232.396 rows=7,847 loops=1)

  • Buffers: shared hit=85,618
8. 6.997 6,194.539 ↓ 1.1 7,847 1

Hash Join (cost=169.92..169,263.16 rows=7,044 width=278) (actual time=5,772.682..6,194.539 rows=7,847 loops=1)

  • Hash Cond: (f.account_id = ce.c_elementvalue_id)
  • Buffers: shared hit=54,230
9. 7.373 6,186.918 ↑ 8.6 7,847 1

Merge Left Join (cost=6.14..168,774.78 rows=67,778 width=148) (actual time=5,772.043..6,186.918 rows=7,847 loops=1)

  • Merge Cond: (f.ad_table_id = ad.ad_table_id)
  • Buffers: shared hit=54,188
10. 6,179.437 6,179.437 ↑ 8.6 7,847 1

Index Scan using fact_acct_table_record on fact_acct f (cost=0.43..167,870.87 rows=67,778 width=115) (actual time=5,771.950..6,179.437 rows=7,847 loops=1)

  • Index Cond: (ad_client_id = '1000001'::numeric)
  • Filter: ((isactive = 'Y'::bpchar) AND (trunc((dateacct)::timestamp with time zone) < to_date('2019-01-01'::text, 'YYYY-MM-DD'::text)))
  • Rows Removed by Filter: 202,876
  • Buffers: shared hit=54,112
11. 0.108 0.108 ↑ 9.9 128 1

Index Scan using ad_table_pkey on ad_table ad (cost=0.28..91.76 rows=1,272 width=38) (actual time=0.025..0.108 rows=128 loops=1)

  • Buffers: shared hit=76
12. 0.203 0.624 ↓ 1.0 633 1

Hash (cost=155.88..155.88 rows=632 width=158) (actual time=0.623..0.624 rows=633 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 71kB
  • Buffers: shared hit=42
13. 0.127 0.421 ↓ 1.0 633 1

Nested Loop (cost=9.58..155.88 rows=632 width=158) (actual time=0.150..0.421 rows=633 loops=1)

  • Buffers: shared hit=42
14. 0.015 0.015 ↑ 1.0 1 1

Seq Scan on ad_client cl (cost=0.00..1.07 rows=1 width=114) (actual time=0.014..0.015 rows=1 loops=1)

  • Filter: (ad_client_id = '1000001'::numeric)
  • Rows Removed by Filter: 5
  • Buffers: shared hit=1
15. 0.160 0.279 ↓ 1.0 633 1

Bitmap Heap Scan on c_elementvalue ce (cost=9.58..148.48 rows=632 width=44) (actual time=0.129..0.279 rows=633 loops=1)

  • Recheck Cond: (c_element_id = '1000001'::numeric)
  • Heap Blocks: exact=20
  • Buffers: shared hit=41
16. 0.119 0.119 ↓ 1.8 1,153 1

Bitmap Index Scan on c_elementvalue_value (cost=0.00..9.42 rows=632 width=0) (actual time=0.119..0.119 rows=1,153 loops=1)

  • Index Cond: (c_element_id = '1000001'::numeric)
  • Buffers: shared hit=8
17. 31.388 31.388 ↑ 1.0 1 7,847

Index Scan using fact_acct_pkey on fact_acct f_1 (cost=0.43..1.48 rows=1 width=18) (actual time=0.004..0.004 rows=1 loops=7,847)

  • Index Cond: (fact_acct_id = f.fact_acct_id)
  • Buffers: shared hit=31,388
18. 0.147 0.283 ↑ 1.2 770 1

Hash (cost=26.60..26.60 rows=960 width=14) (actual time=0.282..0.283 rows=770 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 44kB
  • Buffers: shared hit=17
19. 0.136 0.136 ↑ 1.2 770 1

Seq Scan on c_period per (cost=0.00..26.60 rows=960 width=14) (actual time=0.008..0.136 rows=770 loops=1)

  • Buffers: shared hit=17
20.          

SubPlan (for Hash Left Join)

21. 0.000 0.000 ↓ 0.0 0

Index Scan using c_order_pkey on c_order (cost=0.42..2.64 rows=1 width=11) (never executed)

  • Index Cond: (c_order_id = f_1.record_id)
22. 0.000 0.000 ↓ 0.0 0

Index Scan using m_inout_pkey on m_inout (cost=0.42..2.64 rows=1 width=13) (never executed)

  • Index Cond: (m_inout_id = f_1.record_id)
23. 5.000 5.000 ↑ 1.0 1 2,500

Index Scan using m_inventory_pkey on m_inventory (cost=0.28..2.50 rows=1 width=9) (actual time=0.002..0.002 rows=1 loops=2,500)

  • Index Cond: (m_inventory_id = f_1.record_id)
  • Buffers: shared hit=7,500
24. 14.457 14.457 ↑ 1.0 1 4,819

Index Scan using c_invoice_key on c_invoice (cost=0.42..2.64 rows=1 width=10) (actual time=0.003..0.003 rows=1 loops=4,819)

  • Index Cond: (c_invoice_id = f_1.record_id)
  • Buffers: shared hit=19,276
25. 0.000 0.000 ↓ 0.0 0

Index Scan using c_payment_pkey on c_payment (cost=0.42..2.64 rows=1 width=7) (never executed)

  • Index Cond: (c_payment_id = f_1.record_id)
26. 0.000 0.000 ↓ 0.0 0

Seq Scan on hr_process (cost=0.00..1.07 rows=1 width=198) (never executed)

  • Filter: (hr_process_id = f_1.record_id)
27. 1.056 1.056 ↑ 1.0 1 528

Index Scan using gl_journal_pkey on gl_journal (cost=0.29..2.50 rows=1 width=11) (actual time=0.002..0.002 rows=1 loops=528)

  • Index Cond: (gl_journal_id = f_1.record_id)
  • Buffers: shared hit=1,584
28. 0.000 0.000 ↓ 0.0 0

Seq Scan on m_requisition (cost=0.00..1.11 rows=1 width=78) (never executed)

  • Filter: (m_requisition_id = f_1.record_id)
29. 0.000 0.000 ↓ 0.0 0

Index Scan using c_invoicebatch_pkey on c_invoicebatch (cost=0.14..2.36 rows=1 width=78) (never executed)

  • Index Cond: (c_invoicebatch_id = f_1.record_id)
30. 0.000 0.000 ↓ 0.0 0

Index Scan using c_allocationhdr_pkey on c_allocationhdr (cost=0.42..2.64 rows=1 width=7) (never executed)

  • Index Cond: (c_allocationhdr_id = f_1.record_id)
31. 0.000 0.000 ↓ 0.0 0

Index Scan using pp_cost_collector_pkey on pp_cost_collector (cost=0.29..2.51 rows=1 width=6) (never executed)

  • Index Cond: (pp_cost_collector_id = f_1.record_id)
32. 0.000 0.000 ↓ 0.0 0

Index Scan using m_movement_pkey on m_movement (cost=0.29..2.51 rows=1 width=9) (never executed)

  • Index Cond: (m_movement_id = f_1.record_id)
33. 0.000 0.000 ↓ 0.0 0

Index Scan using m_matchinv_pkey on m_matchinv (cost=0.29..2.50 rows=1 width=7) (never executed)

  • Index Cond: (m_matchinv_id = f_1.record_id)
34. 0.000 0.000 ↓ 0.0 0

Index Scan using c_bankstatement_pkey on c_bankstatement (cost=0.28..2.50 rows=1 width=7) (never executed)

  • Index Cond: (c_bankstatement_id = f_1.record_id)
35. 47.082 47.082 ↑ 1.0 1 7,847

Index Scan using c_elementvalue_description on c_elementvalue ev (cost=0.28..2.51 rows=1 width=25) (actual time=0.004..0.006 rows=1 loops=7,847)

  • Index Cond: ((description)::text = '1'::text)
  • Filter: ((c_element_id = '1000001'::numeric) AND ("substring"((value)::text, 1, 2) = "substring"((ce.value)::text, 1, 2)))
  • Rows Removed by Filter: 7
  • Buffers: shared hit=23,541
36. 86.317 86.317 ↑ 1.0 1 7,847

Index Scan using c_elementvalue_description on c_elementvalue ev_1 (cost=0.28..2.51 rows=1 width=25) (actual time=0.006..0.011 rows=1 loops=7,847)

  • Index Cond: ((description)::text = '2'::text)
  • Filter: ((c_element_id = '1000001'::numeric) AND ("substring"((value)::text, 1, 4) = "substring"((ce.value)::text, 1, 4)))
  • Rows Removed by Filter: 16
  • Buffers: shared hit=54,929
37. 172.634 172.634 ↑ 1.0 1 7,847

Index Scan using c_elementvalue_description on c_elementvalue ev_2 (cost=0.28..2.51 rows=1 width=25) (actual time=0.006..0.022 rows=1 loops=7,847)

  • Index Cond: ((description)::text = '3'::text)
  • Filter: ((c_element_id = '1000001'::numeric) AND ("substring"((value)::text, 1, 7) = "substring"((ce.value)::text, 1, 7)))
  • Rows Removed by Filter: 42
  • Buffers: shared hit=47,082
38. 408.044 408.044 ↑ 1.0 1 7,847

Index Scan using c_elementvalue_description on c_elementvalue ev_3 (cost=0.28..2.51 rows=1 width=25) (actual time=0.028..0.052 rows=1 loops=7,847)

  • Index Cond: ((description)::text = '4'::text)
  • Filter: ((c_element_id = '1000001'::numeric) AND ("substring"((value)::text, 1, 10) = "substring"((ce.value)::text, 1, 10)))
  • Rows Removed by Filter: 100
  • Buffers: shared hit=94,164
39. 7.963 2,849.383 ↓ 183.6 19,462 1

Subquery Scan on *SELECT* 2 (cost=165.19..225,910.32 rows=106 width=1,036) (actual time=1.635..2,849.383 rows=19,462 loops=1)

  • Buffers: shared hit=861,921
40. 116.545 2,841.420 ↓ 183.6 19,462 1

Nested Loop (cost=165.19..225,908.99 rows=106 width=1,008) (actual time=1.630..2,841.420 rows=19,462 loops=1)

  • Buffers: shared hit=861,921
41. 13.979 835.349 ↓ 183.6 19,462 1

Hash Join (cost=164.76..221,093.28 rows=106 width=286) (actual time=1.449..835.349 rows=19,462 loops=1)

  • Hash Cond: (f_2.account_id = ce_1.c_elementvalue_id)
  • Buffers: shared hit=170,926
42. 17.337 820.843 ↓ 19.1 19,462 1

Nested Loop Left Join (cost=0.98..220,924.63 rows=1,017 width=156) (actual time=0.905..820.843 rows=19,462 loops=1)

  • Buffers: shared hit=170,884
43. 31.363 764.582 ↓ 19.1 19,462 1

Nested Loop Left Join (cost=0.71..220,552.11 rows=1,017 width=123) (actual time=0.888..764.582 rows=19,462 loops=1)

  • Buffers: shared hit=112,498
44. 694.295 694.295 ↓ 19.1 19,462 1

Index Scan using fact_acct_table_record on fact_acct f_2 (cost=0.43..220,229.38 rows=1,017 width=115) (actual time=0.868..694.295 rows=19,462 loops=1)

  • Index Cond: (ad_client_id = '1000001'::numeric)
  • Filter: ((isactive = 'Y'::bpchar) AND (trunc((dateacct)::timestamp with time zone) >= to_date('2019-01-01'::text, 'YYYY-MM-DD'::text)) AND (trunc((dateacct)::timestamp with time zone) <= to_date('2019-01-31'::text, 'YYYY-MM-DD'::text)))
  • Rows Removed by Filter: 191,261
  • Buffers: shared hit=54,112
45. 38.924 38.924 ↑ 1.0 1 19,462

Index Scan using c_period_pkey on c_period per_1 (cost=0.28..0.32 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=19,462)

  • Index Cond: (c_period_id = f_2.c_period_id)
  • Buffers: shared hit=58,386
46. 38.924 38.924 ↑ 1.0 1 19,462

Index Scan using ad_table_pkey on ad_table ad_1 (cost=0.28..0.37 rows=1 width=38) (actual time=0.002..0.002 rows=1 loops=19,462)

  • Index Cond: (ad_table_id = f_2.ad_table_id)
  • Buffers: shared hit=58,386
47. 0.166 0.527 ↓ 1.0 633 1

Hash (cost=155.88..155.88 rows=632 width=158) (actual time=0.526..0.527 rows=633 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 71kB
  • Buffers: shared hit=42
48. 0.110 0.361 ↓ 1.0 633 1

Nested Loop (cost=9.58..155.88 rows=632 width=158) (actual time=0.143..0.361 rows=633 loops=1)

  • Buffers: shared hit=42
49. 0.014 0.014 ↑ 1.0 1 1

Seq Scan on ad_client cl_1 (cost=0.00..1.07 rows=1 width=114) (actual time=0.013..0.014 rows=1 loops=1)

  • Filter: (ad_client_id = '1000001'::numeric)
  • Rows Removed by Filter: 5
  • Buffers: shared hit=1
50. 0.123 0.237 ↓ 1.0 633 1

Bitmap Heap Scan on c_elementvalue ce_1 (cost=9.58..148.48 rows=632 width=44) (actual time=0.122..0.237 rows=633 loops=1)

  • Recheck Cond: (c_element_id = '1000001'::numeric)
  • Heap Blocks: exact=20
  • Buffers: shared hit=41
51. 0.114 0.114 ↓ 1.8 1,153 1

Bitmap Index Scan on c_elementvalue_value (cost=0.00..9.42 rows=632 width=0) (actual time=0.114..0.114 rows=1,153 loops=1)

  • Index Cond: (c_element_id = '1000001'::numeric)
  • Buffers: shared hit=8
52. 58.386 58.386 ↑ 1.0 1 19,462

Index Scan using fact_acct_pkey on fact_acct f_3 (cost=0.43..2.59 rows=1 width=18) (actual time=0.003..0.003 rows=1 loops=19,462)

  • Index Cond: (fact_acct_id = f_2.fact_acct_id)
  • Buffers: shared hit=77,848
53.          

SubPlan (for Nested Loop)

54. 0.000 0.000 ↓ 0.0 0

Index Scan using c_order_pkey on c_order c_order_1 (cost=0.42..2.64 rows=1 width=11) (never executed)

  • Index Cond: (c_order_id = f_3.record_id)
55. 7.374 7.374 ↑ 1.0 1 2,458

Index Scan using m_inout_pkey on m_inout m_inout_1 (cost=0.42..2.64 rows=1 width=13) (actual time=0.003..0.003 rows=1 loops=2,458)

  • Index Cond: (m_inout_id = f_3.record_id)
  • Buffers: shared hit=9,832
56. 0.400 0.400 ↑ 1.0 1 200

Index Scan using m_inventory_pkey on m_inventory m_inventory_1 (cost=0.28..2.50 rows=1 width=9) (actual time=0.002..0.002 rows=1 loops=200)

  • Index Cond: (m_inventory_id = f_3.record_id)
  • Buffers: shared hit=600
57. 12.969 12.969 ↑ 1.0 1 4,323

Index Scan using c_invoice_key on c_invoice c_invoice_1 (cost=0.42..2.64 rows=1 width=10) (actual time=0.003..0.003 rows=1 loops=4,323)

  • Index Cond: (c_invoice_id = f_3.record_id)
  • Buffers: shared hit=17,292
58. 5.670 5.670 ↑ 1.0 1 1,890

Index Scan using c_payment_pkey on c_payment c_payment_1 (cost=0.42..2.64 rows=1 width=7) (actual time=0.003..0.003 rows=1 loops=1,890)

  • Index Cond: (c_payment_id = f_3.record_id)
  • Buffers: shared hit=7,560
59. 0.000 0.000 ↓ 0.0 0

Seq Scan on hr_process hr_process_1 (cost=0.00..1.07 rows=1 width=198) (never executed)

  • Filter: (hr_process_id = f_3.record_id)
60. 2.376 2.376 ↑ 1.0 1 792

Index Scan using gl_journal_pkey on gl_journal gl_journal_1 (cost=0.29..2.50 rows=1 width=11) (actual time=0.002..0.003 rows=1 loops=792)

  • Index Cond: (gl_journal_id = f_3.record_id)
  • Buffers: shared hit=2,376
61. 0.000 0.000 ↓ 0.0 0

Seq Scan on m_requisition m_requisition_1 (cost=0.00..1.11 rows=1 width=78) (never executed)

  • Filter: (m_requisition_id = f_3.record_id)
62. 0.000 0.000 ↓ 0.0 0

Index Scan using c_invoicebatch_pkey on c_invoicebatch c_invoicebatch_1 (cost=0.14..2.36 rows=1 width=78) (never executed)

  • Index Cond: (c_invoicebatch_id = f_3.record_id)
63. 9.438 9.438 ↑ 1.0 1 3,146

Index Scan using c_allocationhdr_pkey on c_allocationhdr c_allocationhdr_1 (cost=0.42..2.64 rows=1 width=7) (actual time=0.003..0.003 rows=1 loops=3,146)

  • Index Cond: (c_allocationhdr_id = f_3.record_id)
  • Buffers: shared hit=12,584
64. 15.616 15.616 ↑ 1.0 1 3,904

Index Scan using pp_cost_collector_pkey on pp_cost_collector pp_cost_collector_1 (cost=0.29..2.51 rows=1 width=6) (actual time=0.003..0.004 rows=1 loops=3,904)

  • Index Cond: (pp_cost_collector_id = f_3.record_id)
  • Buffers: shared hit=11,712
65. 0.180 0.180 ↑ 1.0 1 60

Index Scan using m_movement_pkey on m_movement m_movement_1 (cost=0.29..2.51 rows=1 width=9) (actual time=0.003..0.003 rows=1 loops=60)

  • Index Cond: (m_movement_id = f_3.record_id)
  • Buffers: shared hit=180
66. 1.215 1.215 ↑ 1.0 1 405

Index Scan using m_matchinv_pkey on m_matchinv m_matchinv_1 (cost=0.29..2.50 rows=1 width=7) (actual time=0.002..0.003 rows=1 loops=405)

  • Index Cond: (m_matchinv_id = f_3.record_id)
  • Buffers: shared hit=1,215
67. 4.860 4.860 ↑ 1.0 1 1,620

Index Scan using c_bankstatement_pkey on c_bankstatement c_bankstatement_1 (cost=0.28..2.50 rows=1 width=7) (actual time=0.002..0.003 rows=1 loops=1,620)

  • Index Cond: (c_bankstatement_id = f_3.record_id)
  • Buffers: shared hit=4,860
68. 116.772 116.772 ↑ 1.0 1 19,462

Index Scan using c_elementvalue_description on c_elementvalue ev_4 (cost=0.28..2.51 rows=1 width=25) (actual time=0.004..0.006 rows=1 loops=19,462)

  • Index Cond: ((description)::text = '1'::text)
  • Filter: ((c_element_id = '1000001'::numeric) AND ("substring"((value)::text, 1, 2) = "substring"((ce_1.value)::text, 1, 2)))
  • Rows Removed by Filter: 7
  • Buffers: shared hit=58,386
69. 214.082 214.082 ↑ 1.0 1 19,462

Index Scan using c_elementvalue_description on c_elementvalue ev_5 (cost=0.28..2.51 rows=1 width=25) (actual time=0.006..0.011 rows=1 loops=19,462)

  • Index Cond: ((description)::text = '2'::text)
  • Filter: ((c_element_id = '1000001'::numeric) AND ("substring"((value)::text, 1, 4) = "substring"((ce_1.value)::text, 1, 4)))
  • Rows Removed by Filter: 16
  • Buffers: shared hit=136,234
70. 428.164 428.164 ↑ 1.0 1 19,462

Index Scan using c_elementvalue_description on c_elementvalue ev_6 (cost=0.28..2.51 rows=1 width=25) (actual time=0.008..0.022 rows=1 loops=19,462)

  • Index Cond: ((description)::text = '3'::text)
  • Filter: ((c_element_id = '1000001'::numeric) AND ("substring"((value)::text, 1, 7) = "substring"((ce_1.value)::text, 1, 7)))
  • Rows Removed by Filter: 42
  • Buffers: shared hit=116,772
71. 1,012.024 1,012.024 ↑ 1.0 1 19,462

Index Scan using c_elementvalue_description on c_elementvalue ev_7 (cost=0.28..2.51 rows=1 width=25) (actual time=0.021..0.052 rows=1 loops=19,462)

  • Index Cond: ((description)::text = '4'::text)
  • Filter: ((c_element_id = '1000001'::numeric) AND ("substring"((value)::text, 1, 10) = "substring"((ce_1.value)::text, 1, 10)))
  • Rows Removed by Filter: 100
  • Buffers: shared hit=233,544