explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rVqz : Query Rapido

Settings
# exclusive inclusive rows x rows loops node
1. 19.503 31,136.637 ↓ 4.0 27,309 1

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

  • Buffers: shared hit=3256985 read=23233, temp read=927 written=930
2. 115.000 31,117.134 ↓ 4.0 27,309 1

Sort (cost=5,027,766.33..5,027,783.23 rows=6,760 width=2,698) (actual time=31,108.159..31,117.134 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=3256985 read=23233, temp read=927 written=930
3. 12.913 31,002.134 ↓ 4.0 27,309 1

Append (cost=153.93..5,019,454.30 rows=6,760 width=2,698) (actual time=1.941..31,002.134 rows=27,309 loops=1)

  • Buffers: shared hit=3256985 read=23233
4. 9.511 7,888.182 ↓ 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=1.940..7,888.182 rows=7,847 loops=1)

  • Buffers: shared hit=969621 read=11861
5. 1,714.490 7,878.671 ↓ 1.2 7,847 1

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

  • Buffers: shared hit=969621 read=11861
6. 16.725 530.035 ↓ 1.2 7,847 1

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

  • Buffers: shared hit=62563 read=11669
7. 4.334 489.769 ↓ 1.2 7,847 1

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

  • Buffers: shared hit=39022 read=11669
8. 0.016 0.016 ↑ 1.0 1 1

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

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

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

  • Hash Cond: (f.account_id = ce.c_elementvalue_id)
  • Buffers: shared hit=39021 read=11669
10. 467.040 467.040 ↑ 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.075..467.040 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=38994 read=11669
11. 0.238 0.525 ↓ 1.0 632 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 61kB
  • Buffers: shared hit=27
12. 0.213 0.287 ↓ 1.0 632 1

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

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

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

  • Index Cond: (c_element_id = '1000001'::numeric)
  • Buffers: shared hit=5
14. 23.541 23.541 ↑ 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.003..0.003 rows=1 loops=7,847)

  • Index Cond: (f.c_period_id = c_period_id)
  • Buffers: shared hit=23541
15. 23.541 23.541 ↑ 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.003..0.003 rows=1 loops=7,847)

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

SubPlan (for Nested Loop Left Join)

17. 1,004.416 1,451.695 ↓ 0.0 0 7,847

Bitmap Heap Scan on c_elementvalue ev (cost=17.02..151.21 rows=1 width=25) (actual time=0.185..0.185 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
18. 447.279 447.279 ↓ 1.0 632 7,847

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

  • Index Cond: (c_element_id = '1000001'::numeric)
  • Buffers: shared hit=39235
19. 957.334 1,388.919 ↓ 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.177..0.177 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
20. 431.585 431.585 ↓ 1.0 632 7,847

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

  • Index Cond: (c_element_id = '1000001'::numeric)
  • Buffers: shared hit=39235
21. 957.334 1,388.919 ↓ 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.177..0.177 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
22. 431.585 431.585 ↓ 1.0 632 7,847

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

  • Index Cond: (c_element_id = '1000001'::numeric)
  • Buffers: shared hit=39235
23. 949.487 1,381.072 ↓ 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.176..0.176 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
24. 431.585 431.585 ↓ 1.0 632 7,847

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

  • Index Cond: (c_element_id = '1000001'::numeric)
  • Buffers: shared hit=39235
25. 20.876 23,101.039 ↓ 194.6 19,462 1

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

  • Buffers: shared hit=2287364 read=11372
26. 6,572.505 23,080.163 ↓ 194.6 19,462 1

Nested Loop Left Join (cost=0.71..549,357.19 rows=100 width=1,007) (actual time=4.074..23,080.163 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=2287364 read=11372
27. 11.152 2,611.790 ↓ 194.6 19,462 1

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

  • Buffers: shared hit=98712 read=10367
28. 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.011..0.014 rows=1 loops=1)

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

Nested Loop Left Join (cost=0.71..487,720.09 rows=100 width=185) (actual time=1.832..2,600.624 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=98711 read=10367
30. 44.432 998.562 ↓ 194.6 19,462 1

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

  • Buffers: shared hit=98709 read=10348
31. 876.282 876.282 ↓ 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=1.466..876.282 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=40315 read=10348
32. 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
33. 447.269 447.626 ↑ 3.7 318 19,462

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

  • Buffers: shared hit=2 read=19
34. 0.357 0.357 ↑ 1.4 825 1

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

  • Buffers: shared hit=2 read=19
35. 894.989 895.252 ↑ 1.0 731 19,462

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

  • Buffers: shared hit=2 read=11
36. 0.263 0.263 ↑ 1.0 731 1

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

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

SubPlan (for Nested Loop Left Join)

38. 2,335.440 3,386.388 ↓ 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.174..0.174 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
39. 1,050.948 1,050.948 ↓ 1.0 632 19,462

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

  • Index Cond: (c_element_id = '1000001'::numeric)
  • Buffers: shared hit=97310
40. 2,218.668 3,230.692 ↓ 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.166..0.166 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
41. 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
42. 2,179.744 3,191.768 ↓ 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.164..0.164 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
43. 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
44. 2,179.744 3,191.768 ↓ 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.164..0.164 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
45. 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
Planning time : 2.930 ms