explain.depesz.com

PostgreSQL's explain analyze made readable

Result: otea : Query

Settings
# exclusive inclusive rows x rows loops node
1. 97.225 172,295.547 ↓ 3.8 27,309 1

Sort (cost=485,274.58..485,292.45 rows=7,150 width=2,698) (actual time=172,272.767..172,295.547 rows=27,309 loops=1)

  • Sort Key: "*SELECT* 1".value0, "*SELECT* 1".dateacct
  • Sort Method: external merge Disk: 8,920kB
  • Buffers: shared hit=1,113,705, temp read=3,416 written=3,434
  • JIT:
  • Functions: 123
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 30.621 ms, Inlining 0.000 ms, Optimization 11.464 ms, Emission 172.884 ms, Total 214.969 ms
2. 30.438 172,198.322 ↓ 3.8 27,309 1

Unique (cost=474,496.52..475,050.64 rows=7,150 width=2,698) (actual time=172,150.020..172,198.322 rows=27,309 loops=1)

  • Buffers: shared hit=1,113,705, temp read=1,710 written=1,719
3. 136.981 172,167.884 ↓ 3.8 27,309 1

Sort (cost=474,496.52..474,514.39 rows=7,150 width=2,698) (actual time=172,150.017..172,167.884 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,113,705, temp read=1,710 written=1,719
4. 10.541 172,030.903 ↓ 3.8 27,309 1

Append (cost=208.52..464,272.58 rows=7,150 width=2,698) (actual time=194.189..172,030.903 rows=27,309 loops=1)

  • Buffers: shared hit=1,113,705
5. 9.053 37,695.929 ↓ 1.1 7,847 1

Subquery Scan on *SELECT* 1 (cost=208.52..242,049.63 rows=7,044 width=1,092) (actual time=194.187..37,695.929 rows=7,847 loops=1)

  • Buffers: shared hit=310,170
6. 35,943.315 37,686.876 ↓ 1.1 7,847 1

Hash Left Join (cost=208.52..241,943.97 rows=7,044 width=1,036) (actual time=194.184..37,686.876 rows=7,847 loops=1)

  • Hash Cond: (f.c_period_id = per.c_period_id)
  • Buffers: shared hit=310,170
7. 17.221 817.255 ↓ 1.1 7,847 1

Hash Join (cost=169.92..169,263.16 rows=7,044 width=278) (actual time=188.157..817.255 rows=7,847 loops=1)

  • Hash Cond: (f.account_id = ce.c_elementvalue_id)
  • Buffers: shared hit=54,230
8. 22.341 799.312 ↑ 8.6 7,847 1

Merge Left Join (cost=6.14..168,774.78 rows=67,778 width=148) (actual time=186.546..799.312 rows=7,847 loops=1)

  • Merge Cond: (f.ad_table_id = ad.ad_table_id)
  • Buffers: shared hit=54,188
9. 776.798 776.798 ↑ 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=186.411..776.798 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
10. 0.173 0.173 ↑ 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.037..0.173 rows=128 loops=1)

  • Buffers: shared hit=76
11. 0.220 0.722 ↓ 1.0 633 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 71kB
  • Buffers: shared hit=42
12. 0.173 0.502 ↓ 1.0 633 1

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

  • Buffers: shared hit=42
13. 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.013..0.015 rows=1 loops=1)

  • Filter: (ad_client_id = '1000001'::numeric)
  • Rows Removed by Filter: 5
  • Buffers: shared hit=1
14. 0.167 0.314 ↓ 1.0 633 1

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

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

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

  • Index Cond: (c_element_id = '1000001'::numeric)
  • Buffers: shared hit=8
16. 0.168 0.360 ↑ 1.2 770 1

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

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

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

  • Buffers: shared hit=17
18.          

SubPlan (for Hash Left Join)

19. 102.011 102.011 ↑ 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.009..0.013 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
20. 117.705 117.705 ↑ 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.009..0.015 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
21. 219.716 219.716 ↑ 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.009..0.028 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
22. 486.514 486.514 ↑ 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.034..0.062 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
23. 20.517 134,324.433 ↓ 183.6 19,462 1

Subquery Scan on *SELECT* 2 (cost=164.76..222,187.20 rows=106 width=1,036) (actual time=10.294..134,324.433 rows=19,462 loops=1)

  • Buffers: shared hit=803,535
24. 130,646.876 134,303.916 ↓ 183.6 19,462 1

Hash Join (cost=164.76..222,185.88 rows=106 width=1,008) (actual time=10.290..134,303.916 rows=19,462 loops=1)

  • Hash Cond: (f_1.account_id = ce_1.c_elementvalue_id)
  • Buffers: shared hit=803,535
25. 52.973 1,359.896 ↓ 19.1 19,462 1

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

  • Buffers: shared hit=170,884
26. 71.731 1,248.537 ↓ 19.1 19,462 1

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

  • Buffers: shared hit=112,498
27. 1,098.958 1,098.958 ↓ 19.1 19,462 1

Index Scan using fact_acct_table_record on fact_acct f_1 (cost=0.43..220,229.38 rows=1,017 width=115) (actual time=1.267..1,098.958 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
28. 77.848 77.848 ↑ 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.004..0.004 rows=1 loops=19,462)

  • Index Cond: (c_period_id = f_1.c_period_id)
  • Buffers: shared hit=58,386
29. 58.386 58.386 ↑ 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.003..0.003 rows=1 loops=19,462)

  • Index Cond: (ad_table_id = f_1.ad_table_id)
  • Buffers: shared hit=58,386
30. 0.174 0.628 ↓ 1.0 633 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 71kB
  • Buffers: shared hit=42
31. 0.175 0.454 ↓ 1.0 633 1

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

  • Buffers: shared hit=42
32. 0.016 0.016 ↑ 1.0 1 1

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

  • Filter: (ad_client_id = '1000001'::numeric)
  • Rows Removed by Filter: 5
  • Buffers: shared hit=1
33. 0.147 0.263 ↓ 1.0 633 1

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

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

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

  • Index Cond: (c_element_id = '1000001'::numeric)
  • Buffers: shared hit=8
35.          

SubPlan (for Hash Join)

36. 253.006 253.006 ↑ 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.010..0.013 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
37. 291.930 291.930 ↑ 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.009..0.015 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
38. 544.936 544.936 ↑ 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.011..0.028 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
39. 1,206.644 1,206.644 ↑ 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.026..0.062 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
Planning time : 4.084 ms
Execution time : 172,332.041 ms