explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4bdJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 18,352.452 ↑ 1.0 1 1

Aggregate (cost=103,676.27..103,676.28 rows=1 width=96) (actual time=18,352.451..18,352.452 rows=1 loops=1)

2. 0.003 18,352.435 ↑ 1.0 3 1

Append (cost=3,930.88..103,676.21 rows=3 width=39) (actual time=3,128.868..18,352.435 rows=3 loops=1)

3. 0.007 17,101.294 ↑ 1.0 2 1

Result (cost=3,930.88..98,849.06 rows=2 width=38) (actual time=3,128.867..17,101.294 rows=2 loops=1)

4. 0.002 17,101.287 ↑ 1.0 2 1

Append (cost=3,930.88..98,849.06 rows=2 width=38) (actual time=3,128.864..17,101.287 rows=2 loops=1)

5. 0.012 3,128.865 ↑ 1.0 1 1

Subquery Scan on *SELECT* 1 (cost=3,930.88..3,930.90 rows=1 width=42) (actual time=3,128.864..3,128.865 rows=1 loops=1)

6. 0.004 3,128.853 ↑ 1.0 1 1

Aggregate (cost=3,930.88..3,930.89 rows=1 width=42) (actual time=3,128.853..3,128.853 rows=1 loops=1)

7. 5.110 3,128.849 ↓ 0.0 0 1

Bitmap Heap Scan on contapagar (cost=1,336.01..3,930.11 rows=76 width=42) (actual time=3,128.849..3,128.849 rows=0 loops=1)

  • Recheck Cond: (((situacao)::text = ANY ('{AP,PP}'::text[])) AND (datavencimento >= '2019-01-01 00:00:00'::timestamp without time zone) AND (datavencimento <= '2019-05-01 23:59:59.059'::timestamp without time zone) AND (unidadeensino = 27))
  • Filter: (SubPlan 3)
  • Rows Removed by Filter: 905
  • Heap Blocks: exact=451
8. 0.287 10.539 ↓ 0.0 0 1

BitmapAnd (cost=1,336.01..1,336.01 rows=153 width=0) (actual time=10.539..10.539 rows=0 loops=1)

9. 0.385 0.385 ↓ 1.0 2,656 1

Bitmap Index Scan on ch_contapagar_situacao (cost=0.00..52.62 rows=2,638 width=0) (actual time=0.385..0.385 rows=2,656 loops=1)

  • Index Cond: ((situacao)::text = ANY ('{AP,PP}'::text[]))
10. 1.999 1.999 ↓ 1.0 16,040 1

Bitmap Index Scan on ch_contapagar_datavencimento (cost=0.00..325.59 rows=15,317 width=0) (actual time=1.999..1.999 rows=16,040 loops=1)

  • Index Cond: ((datavencimento >= '2019-01-01 00:00:00'::timestamp without time zone) AND (datavencimento <= '2019-05-01 23:59:59.059'::timestamp without time zone))
11. 7.868 7.868 ↑ 1.0 51,030 1

Bitmap Index Scan on idx_contapagar_unidadeensino (cost=0.00..957.25 rows=51,311 width=0) (actual time=7.868..7.868 rows=51,030 loops=1)

  • Index Cond: (unidadeensino = 27)
12.          

SubPlan (forBitmap Heap Scan)

13. 9.634 3,113.200 ↓ 0.0 0 905

Nested Loop (cost=0.43..13.48 rows=1 width=0) (actual time=3.440..3.440 rows=0 loops=905)

  • Join Filter: (centroresultadoorigem_2.categoriadespesa = categoriadespesa_2.codigo)
  • Rows Removed by Join Filter: 2
14. 28.960 28.960 ↓ 2.0 2 905

Index Scan using idx_centroresultadoorigem_origem_codorigem on centroresultadoorigem centroresultadoorigem_2 (cost=0.43..8.46 rows=1 width=4) (actual time=0.026..0.032 rows=2 loops=905)

  • Index Cond: (((tipocentroresultadoorigem)::text = 'CONTA_PAGAR'::text) AND ((codorigem)::text = ((contapagar.codigo)::character varying)::text))
15. 3,074.606 3,074.606 ↑ 1.0 1 1,741

Seq Scan on categoriadespesa categoriadespesa_2 (cost=0.00..5.01 rows=1 width=4) (actual time=0.411..1.766 rows=1 loops=1,741)

  • Filter: ((translate(btrim((descricao)::text), 'áéíóúàèìòùãõâêîôôäëïöüçÁÉÍÓÚÀÈÌÒÙÃÕÂÊÎÔÛÄËÏÖÜÇ'''::text, 'aeiouaeiouaoaeiooaeioucAEIOUAEIOUAOAEIOOAEIOUC'::text))::text ~~* 'TELEFONIA CELULAR%'::text)
  • Rows Removed by Filter: 171
16. 0.006 13,972.420 ↑ 1.0 1 1

Subquery Scan on *SELECT* 2 (cost=94,918.14..94,918.16 rows=1 width=33) (actual time=13,972.419..13,972.420 rows=1 loops=1)

17. 0.030 13,972.414 ↑ 1.0 1 1

Aggregate (cost=94,918.14..94,918.15 rows=1 width=33) (actual time=13,972.414..13,972.414 rows=1 loops=1)

18. 22.065 13,972.384 ↑ 3,193.0 1 1

Bitmap Heap Scan on contapagar contapagar_1 (cost=2,950.19..94,910.16 rows=3,193 width=33) (actual time=4,175.523..13,972.384 rows=1 loops=1)

  • Recheck Cond: ((datavencimento >= '2019-01-01 00:00:00'::timestamp without time zone) AND (datavencimento <= '2019-05-01 23:59:59.059'::timestamp without time zone) AND (unidadeensino = 27) AND ((situacao)::text = ANY ('{PA,PP}'::text[])))
  • Filter: (SubPlan 2)
  • Rows Removed by Filter: 3246
  • Heap Blocks: exact=1125
19. 1.317 20.689 ↓ 0.0 0 1

BitmapAnd (cost=2,950.19..2,950.19 rows=6,385 width=0) (actual time=20.689..20.689 rows=0 loops=1)

20. 1.688 1.688 ↓ 1.0 16,041 1

Bitmap Index Scan on ch_contapagar_datavencimento (cost=0.00..325.59 rows=15,317 width=0) (actual time=1.688..1.688 rows=16,041 loops=1)

  • Index Cond: ((datavencimento >= '2019-01-01 00:00:00'::timestamp without time zone) AND (datavencimento <= '2019-05-01 23:59:59.059'::timestamp without time zone))
21. 5.117 5.117 ↑ 1.0 51,030 1

Bitmap Index Scan on idx_contapagar_unidadeensino (cost=0.00..957.25 rows=51,311 width=0) (actual time=5.117..5.117 rows=51,030 loops=1)

  • Index Cond: (unidadeensino = 27)
22. 12.567 12.567 ↑ 1.0 110,262 1

Bitmap Index Scan on ch_contapagar_situacao (cost=0.00..1,664.46 rows=110,350 width=0) (actual time=12.567..12.567 rows=110,262 loops=1)

  • Index Cond: ((situacao)::text = ANY ('{PA,PP}'::text[]))
23.          

SubPlan (forBitmap Heap Scan)

24. 49.680 13,929.630 ↓ 0.0 0 3,247

Nested Loop (cost=0.43..13.48 rows=1 width=0) (actual time=4.290..4.290 rows=0 loops=3,247)

  • Join Filter: (centroresultadoorigem_1.categoriadespesa = categoriadespesa_1.codigo)
  • Rows Removed by Join Filter: 2
25. 155.856 155.856 ↓ 2.0 2 3,247

Index Scan using idx_centroresultadoorigem_origem_codorigem on centroresultadoorigem centroresultadoorigem_1 (cost=0.43..8.46 rows=1 width=4) (actual time=0.041..0.048 rows=2 loops=3,247)

  • Index Cond: (((tipocentroresultadoorigem)::text = 'CONTA_PAGAR'::text) AND ((codorigem)::text = ((contapagar_1.codigo)::character varying)::text))
26. 13,724.094 13,724.094 ↑ 1.0 1 7,122

Seq Scan on categoriadespesa categoriadespesa_1 (cost=0.00..5.01 rows=1 width=4) (actual time=0.437..1.927 rows=1 loops=7,122)

  • Filter: ((translate(btrim((descricao)::text), 'áéíóúàèìòùãõâêîôôäëïöüçÁÉÍÓÚÀÈÌÒÙÃÕÂÊÎÔÛÄËÏÖÜÇ'''::text, 'aeiouaeiouaoaeiooaeioucAEIOUAEIOUAOAEIOOAEIOUC'::text))::text ~~* 'TELEFONIA CELULAR%'::text)
  • Rows Removed by Filter: 171
27. 0.010 1,251.138 ↑ 1.0 1 1

Subquery Scan on *SELECT* 3 (cost=4,827.13..4,827.15 rows=1 width=42) (actual time=1,251.137..1,251.138 rows=1 loops=1)

28. 0.003 1,251.128 ↑ 1.0 1 1

Aggregate (cost=4,827.13..4,827.14 rows=1 width=42) (actual time=1,251.128..1,251.128 rows=1 loops=1)

29. 3.053 1,251.125 ↓ 0.0 0 1

Bitmap Heap Scan on contapagar contapagar_2 (cost=1,350.55..4,826.10 rows=103 width=42) (actual time=1,251.125..1,251.125 rows=0 loops=1)

  • Recheck Cond: (((situacao)::text = 'NE'::text) AND (datavencimento >= '2019-01-01 00:00:00'::timestamp without time zone) AND (datavencimento <= '2019-05-01 23:59:59.059'::timestamp without time zone) AND (unidadeensino = 27))
  • Filter: (SubPlan 1)
  • Rows Removed by Filter: 467
  • Heap Blocks: exact=328
30. 0.255 6.786 ↓ 0.0 0 1

BitmapAnd (cost=1,350.55..1,350.55 rows=206 width=0) (actual time=6.786..6.786 rows=0 loops=1)

31. 0.448 0.448 ↓ 1.1 3,787 1

Bitmap Index Scan on ch_contapagar_situacao (cost=0.00..67.13 rows=3,562 width=0) (actual time=0.448..0.448 rows=3,787 loops=1)

  • Index Cond: ((situacao)::text = 'NE'::text)
32. 1.644 1.644 ↓ 1.0 16,041 1

Bitmap Index Scan on ch_contapagar_datavencimento (cost=0.00..325.59 rows=15,317 width=0) (actual time=1.644..1.644 rows=16,041 loops=1)

  • Index Cond: ((datavencimento >= '2019-01-01 00:00:00'::timestamp without time zone) AND (datavencimento <= '2019-05-01 23:59:59.059'::timestamp without time zone))
33. 4.439 4.439 ↑ 1.0 51,030 1

Bitmap Index Scan on idx_contapagar_unidadeensino (cost=0.00..957.25 rows=51,311 width=0) (actual time=4.439..4.439 rows=51,030 loops=1)

  • Index Cond: (unidadeensino = 27)
34.          

SubPlan (forBitmap Heap Scan)

35. 3.337 1,241.286 ↓ 0.0 0 467

Nested Loop (cost=0.43..13.48 rows=1 width=0) (actual time=2.658..2.658 rows=0 loops=467)

  • Join Filter: (centroresultadoorigem.categoriadespesa = categoriadespesa.codigo)
  • Rows Removed by Join Filter: 2
36. 13.076 13.076 ↓ 2.0 2 467

Index Scan using idx_centroresultadoorigem_origem_codorigem on centroresultadoorigem (cost=0.43..8.46 rows=1 width=4) (actual time=0.025..0.028 rows=2 loops=467)

  • Index Cond: (((tipocentroresultadoorigem)::text = 'CONTA_PAGAR'::text) AND ((codorigem)::text = ((contapagar_2.codigo)::character varying)::text))
37. 1,224.873 1,224.873 ↑ 1.0 1 741

Seq Scan on categoriadespesa (cost=0.00..5.01 rows=1 width=4) (actual time=0.378..1.653 rows=1 loops=741)

  • Filter: ((translate(btrim((descricao)::text), 'áéíóúàèìòùãõâêîôôäëïöüçÁÉÍÓÚÀÈÌÒÙÃÕÂÊÎÔÛÄËÏÖÜÇ'''::text, 'aeiouaeiouaoaeiooaeioucAEIOUAEIOUAOAEIOOAEIOUC'::text))::text ~~* 'TELEFONIA CELULAR%'::text)
  • Rows Removed by Filter: 171