explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wtZu : Query Rapido

Settings
# exclusive inclusive rows x rows loops node
1. 63.073 28,420.852 ↓ 4.0 27,309 1

Sort (cost=5,036,602.26..5,036,619.16 rows=6,760 width=2,698) (actual time=28,406.729..28,420.852 rows=27,309 loops=1)

  • Sort Key: "*SELECT* 1".value0, "*SELECT* 1".dateacct
  • Sort Method: external merge Disk: 7424kB
  • Buffers: shared hit=3256462 read=23756, temp read=1855 written=1861
2. 19.107 28,357.779 ↓ 4.0 27,309 1

Unique (cost=5,027,766.33..5,028,290.23 rows=6,760 width=2,698) (actual time=28,330.070..28,357.779 rows=27,309 loops=1)

  • Buffers: shared hit=3256462 read=23756, temp read=927 written=930
3. 94.985 28,338.672 ↓ 4.0 27,309 1

Sort (cost=5,027,766.33..5,027,783.23 rows=6,760 width=2,698) (actual time=28,330.068..28,338.672 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: 7416kB
  • Buffers: shared hit=3256462 read=23756, temp read=927 written=930
4. 8.101 28,243.687 ↓ 4.0 27,309 1

Append (cost=153.93..5,019,454.30 rows=6,760 width=2,698) (actual time=2.194..28,243.687 rows=27,309 loops=1)

  • Buffers: shared hit=3256462 read=23756
5. 6.594 6,332.417 ↓ 1.2 7,847 1

Subquery Scan on *SELECT* 1 (cost=153.93..4,470,062.06 rows=6,660 width=1,091) (actual time=2.193..6,332.417 rows=7,847 loops=1)

  • Buffers: shared hit=969294 read=12188
6. 1,307.923 6,325.823 ↓ 1.2 7,847 1

Nested Loop Left Join (cost=153.93..4,469,962.16 rows=6,660 width=1,035) (actual time=2.189..6,325.823 rows=7,847 loops=1)

  • Buffers: shared hit=969294 read=12188
7. 13.083 498.028 ↓ 1.2 7,847 1

Nested Loop Left Join (cost=153.65..437,876.76 rows=6,660 width=252) (actual time=0.664..498.028 rows=7,847 loops=1)

  • Buffers: shared hit=62239 read=11993
8. 3.400 469.251 ↓ 1.2 7,847 1

Nested Loop (cost=153.38..435,921.40 rows=6,660 width=244) (actual time=0.647..469.251 rows=7,847 loops=1)

  • Buffers: shared hit=38698 read=11993
9. 0.013 0.013 ↑ 1.0 1 1

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

  • Filter: (ad_client_id = '1000001'::numeric)
  • Rows Removed by Filter: 5
  • Buffers: shared hit=1
10. 13.449 465.838 ↓ 1.2 7,847 1

Hash Join (cost=153.38..435,853.73 rows=6,660 width=152) (actual time=0.632..465.838 rows=7,847 loops=1)

  • Hash Cond: (f.account_id = ce.c_elementvalue_id)
  • Buffers: shared hit=38697 read=11993
11. 451.858 451.858 ↑ 8.2 7,847 1

Index Scan using fact_acct_table_record on fact_acct f (cost=0.43..435,532.32 rows=64,127 width=114) (actual time=0.079..451.858 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: 189596
  • Buffers: shared hit=38670 read=11993
12. 0.230 0.531 ↓ 1.0 632 1

Hash (cost=145.06..145.06 rows=631 width=44) (actual time=0.530..0.531 rows=632 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 61kB
  • Buffers: shared hit=27
13. 0.221 0.301 ↓ 1.0 632 1

Bitmap Heap Scan on c_elementvalue ce (cost=17.17..145.06 rows=631 width=44) (actual time=0.094..0.301 rows=632 loops=1)

  • Recheck Cond: (c_element_id = '1000001'::numeric)
  • Heap Blocks: exact=22
  • Buffers: shared hit=27
14. 0.080 0.080 ↓ 1.0 632 1

Bitmap Index Scan on c_elementvalue_value (cost=0.00..17.02 rows=631 width=0) (actual time=0.080..0.080 rows=632 loops=1)

  • Index Cond: (c_element_id = '1000001'::numeric)
  • Buffers: shared hit=5
15. 15.694 15.694 ↑ 1.0 1 7,847

Index Scan using c_period_pkey on c_period per (cost=0.28..0.29 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=7,847)

  • Index Cond: (f.c_period_id = c_period_id)
  • Buffers: shared hit=23541
16. 15.694 15.694 ↑ 1.0 1 7,847

Index Scan using ad_table_pkey on ad_table ad (cost=0.28..0.30 rows=1 width=38) (actual time=0.002..0.002 rows=1 loops=7,847)

  • Index Cond: (f.ad_table_id = ad_table_id)
  • Buffers: shared hit=23541
17.          

SubPlan (for Nested Loop Left Join)

18. 800.394 1,161.356 ↓ 0.0 0 7,847

Bitmap Heap Scan on c_elementvalue ev (cost=17.02..151.21 rows=1 width=25) (actual time=0.148..0.148 rows=0 loops=7,847)

  • Recheck Cond: (c_element_id = '1000001'::numeric)
  • Filter: (((description)::text = '1'::text) AND ("substring"((value)::text, 1, 2) = "substring"((ce.value)::text, 1, 2)))
  • Rows Removed by Filter: 632
  • Heap Blocks: exact=172634
  • Buffers: shared hit=211869
19. 360.962 360.962 ↓ 1.0 632 7,847

Bitmap Index Scan on c_elementvalue_value (cost=0.00..17.02 rows=631 width=0) (actual time=0.046..0.046 rows=632 loops=7,847)

  • Index Cond: (c_element_id = '1000001'::numeric)
  • Buffers: shared hit=39235
20. 761.159 1,114.274 ↓ 0.0 0 7,847

Bitmap Heap Scan on c_elementvalue ev_1 (cost=17.02..151.21 rows=1 width=25) (actual time=0.142..0.142 rows=0 loops=7,847)

  • Recheck Cond: (c_element_id = '1000001'::numeric)
  • Filter: (((description)::text = '2'::text) AND ("substring"((value)::text, 1, 4) = "substring"((ce.value)::text, 1, 4)))
  • Rows Removed by Filter: 632
  • Heap Blocks: exact=172634
  • Buffers: shared hit=211869
21. 353.115 353.115 ↓ 1.0 632 7,847

Bitmap Index Scan on c_elementvalue_value (cost=0.00..17.02 rows=631 width=0) (actual time=0.045..0.045 rows=632 loops=7,847)

  • Index Cond: (c_element_id = '1000001'::numeric)
  • Buffers: shared hit=39235
22. 761.159 1,114.274 ↓ 0.0 0 7,847

Bitmap Heap Scan on c_elementvalue ev_2 (cost=17.02..151.21 rows=1 width=25) (actual time=0.142..0.142 rows=0 loops=7,847)

  • Recheck Cond: (c_element_id = '1000001'::numeric)
  • Filter: (((description)::text = '3'::text) AND ("substring"((value)::text, 1, 7) = "substring"((ce.value)::text, 1, 7)))
  • Rows Removed by Filter: 632
  • Heap Blocks: exact=172634
  • Buffers: shared hit=211869
23. 353.115 353.115 ↓ 1.0 632 7,847

Bitmap Index Scan on c_elementvalue_value (cost=0.00..17.02 rows=631 width=0) (actual time=0.045..0.045 rows=632 loops=7,847)

  • Index Cond: (c_element_id = '1000001'::numeric)
  • Buffers: shared hit=39235
24. 761.159 1,114.274 ↓ 0.0 0 7,847

Bitmap Heap Scan on c_elementvalue ev_3 (cost=17.02..151.21 rows=1 width=25) (actual time=0.142..0.142 rows=0 loops=7,847)

  • Recheck Cond: (c_element_id = '1000001'::numeric)
  • Filter: (((description)::text = '4'::text) AND ("substring"((value)::text, 1, 10) = "substring"((ce.value)::text, 1, 10)))
  • Rows Removed by Filter: 632
  • Heap Blocks: exact=172634
  • Buffers: shared hit=211869
25. 353.115 353.115 ↓ 1.0 632 7,847

Bitmap Index Scan on c_elementvalue_value (cost=0.00..17.02 rows=631 width=0) (actual time=0.045..0.045 rows=632 loops=7,847)

  • Index Cond: (c_element_id = '1000001'::numeric)
  • Buffers: shared hit=39235
26. 17.250 21,903.169 ↓ 194.6 19,462 1

Subquery Scan on *SELECT* 2 (cost=0.71..549,358.44 rows=100 width=1,035) (actual time=2.515..21,903.169 rows=19,462 loops=1)

  • Buffers: shared hit=2287168 read=11568
27. 6,123.380 21,885.919 ↓ 194.6 19,462 1

Nested Loop Left Join (cost=0.71..549,357.19 rows=100 width=1,007) (actual time=2.512..21,885.919 rows=19,462 loops=1)

  • Join Filter: (f_1.c_period_id = per_1.c_period_id)
  • Rows Removed by Join Filter: 14207260
  • Buffers: shared hit=2287168 read=11568
28. 8.652 2,353.221 ↓ 194.6 19,462 1

Nested Loop (cost=0.71..487,722.16 rows=100 width=277) (actual time=1.151..2,353.221 rows=19,462 loops=1)

  • Buffers: shared hit=98629 read=10450
29. 0.012 0.012 ↑ 1.0 1 1

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

  • Filter: (ad_client_id = '1000001'::numeric)
  • Rows Removed by Filter: 5
  • Buffers: shared hit=1
30. 1,048.459 2,344.557 ↓ 194.6 19,462 1

Nested Loop Left Join (cost=0.71..487,720.09 rows=100 width=185) (actual time=1.139..2,344.557 rows=19,462 loops=1)

  • Join Filter: (f_1.ad_table_id = ad_1.ad_table_id)
  • Rows Removed by Join Filter: 6170788
  • Buffers: shared hit=98628 read=10450
31. 29.886 926.320 ↓ 194.6 19,462 1

Nested Loop (cost=0.71..485,917.44 rows=100 width=152) (actual time=0.930..926.320 rows=19,462 loops=1)

  • Buffers: shared hit=98626 read=10431
32. 818.586 818.586 ↓ 20.2 19,462 1

Index Scan using fact_acct_table_record on fact_acct f_1 (cost=0.43..485,070.43 rows=962 width=114) (actual time=0.914..818.586 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: 177981
  • Buffers: shared hit=40232 read=10431
33. 77.848 77.848 ↑ 1.0 1 19,462

Index Scan using c_elementvalue_pkey on c_elementvalue ce_1 (cost=0.28..0.88 rows=1 width=44) (actual time=0.004..0.004 rows=1 loops=19,462)

  • Index Cond: (c_elementvalue_id = f_1.account_id)
  • Filter: (c_element_id = '1000001'::numeric)
  • Buffers: shared hit=58394
34. 369.513 369.778 ↑ 3.7 318 19,462

Materialize (cost=0.00..47.58 rows=1,172 width=38) (actual time=0.000..0.019 rows=318 loops=19,462)

  • Buffers: shared hit=2 read=19
35. 0.265 0.265 ↑ 1.4 825 1

Seq Scan on ad_table ad_1 (cost=0.00..41.72 rows=1,172 width=38) (actual time=0.008..0.265 rows=825 loops=1)

  • Buffers: shared hit=2 read=19
36. 856.167 856.328 ↑ 1.0 731 19,462

Materialize (cost=0.00..24.01 rows=734 width=14) (actual time=0.000..0.044 rows=731 loops=19,462)

  • Buffers: shared hit=2 read=11
37. 0.161 0.161 ↑ 1.0 731 1

Seq Scan on c_period per_1 (cost=0.00..20.34 rows=734 width=14) (actual time=0.009..0.161 rows=731 loops=1)

  • Buffers: shared hit=2 read=11
38.          

SubPlan (for Nested Loop Left Join)

39. 2,238.130 3,250.154 ↓ 0.0 0 19,462

Bitmap Heap Scan on c_elementvalue ev_4 (cost=17.02..151.21 rows=1 width=25) (actual time=0.167..0.167 rows=0 loops=19,462)

  • Recheck Cond: (c_element_id = '1000001'::numeric)
  • Filter: (((description)::text = '1'::text) AND ("substring"((value)::text, 1, 2) = "substring"((ce_1.value)::text, 1, 2)))
  • Rows Removed by Filter: 632
  • Heap Blocks: exact=428164
  • Buffers: shared hit=525474
40. 1,012.024 1,012.024 ↓ 1.0 632 19,462

Bitmap Index Scan on c_elementvalue_value (cost=0.00..17.02 rows=631 width=0) (actual time=0.052..0.052 rows=632 loops=19,462)

  • Index Cond: (c_element_id = '1000001'::numeric)
  • Buffers: shared hit=97310
41. 2,140.820 3,113.920 ↓ 0.0 0 19,462

Bitmap Heap Scan on c_elementvalue ev_5 (cost=17.02..151.21 rows=1 width=25) (actual time=0.160..0.160 rows=0 loops=19,462)

  • Recheck Cond: (c_element_id = '1000001'::numeric)
  • Filter: (((description)::text = '2'::text) AND ("substring"((value)::text, 1, 4) = "substring"((ce_1.value)::text, 1, 4)))
  • Rows Removed by Filter: 632
  • Heap Blocks: exact=428164
  • Buffers: shared hit=525474
42. 973.100 973.100 ↓ 1.0 632 19,462

Bitmap Index Scan on c_elementvalue_value (cost=0.00..17.02 rows=631 width=0) (actual time=0.050..0.050 rows=632 loops=19,462)

  • Index Cond: (c_element_id = '1000001'::numeric)
  • Buffers: shared hit=97310
43. 2,121.358 3,094.458 ↓ 0.0 0 19,462

Bitmap Heap Scan on c_elementvalue ev_6 (cost=17.02..151.21 rows=1 width=25) (actual time=0.159..0.159 rows=0 loops=19,462)

  • Recheck Cond: (c_element_id = '1000001'::numeric)
  • Filter: (((description)::text = '3'::text) AND ("substring"((value)::text, 1, 7) = "substring"((ce_1.value)::text, 1, 7)))
  • Rows Removed by Filter: 632
  • Heap Blocks: exact=428164
  • Buffers: shared hit=525474
44. 973.100 973.100 ↓ 1.0 632 19,462

Bitmap Index Scan on c_elementvalue_value (cost=0.00..17.02 rows=631 width=0) (actual time=0.050..0.050 rows=632 loops=19,462)

  • Index Cond: (c_element_id = '1000001'::numeric)
  • Buffers: shared hit=97310
45. 2,121.358 3,094.458 ↓ 0.0 0 19,462

Bitmap Heap Scan on c_elementvalue ev_7 (cost=17.02..151.21 rows=1 width=25) (actual time=0.159..0.159 rows=0 loops=19,462)

  • Recheck Cond: (c_element_id = '1000001'::numeric)
  • Filter: (((description)::text = '4'::text) AND ("substring"((value)::text, 1, 10) = "substring"((ce_1.value)::text, 1, 10)))
  • Rows Removed by Filter: 632
  • Heap Blocks: exact=428164
  • Buffers: shared hit=525474
46. 973.100 973.100 ↓ 1.0 632 19,462

Bitmap Index Scan on c_elementvalue_value (cost=0.00..17.02 rows=631 width=0) (actual time=0.050..0.050 rows=632 loops=19,462)

  • Index Cond: (c_element_id = '1000001'::numeric)
  • Buffers: shared hit=97310
Planning time : 2.245 ms