explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5Rf8

Settings
# exclusive inclusive rows x rows loops node
1. 0.124 37,714.665 ↓ 23.0 23 1

Nested Loop Left Join (cost=261,990.14..431,771.52 rows=1 width=16) (actual time=24,355.523..37,714.665 rows=23 loops=1)

2.          

CTE agendados

3. 0.028 0.399 ↑ 38.2 70 1

Unique (cost=455.07..468.49 rows=2,673 width=22) (actual time=0.358..0.399 rows=70 loops=1)

4. 0.082 0.371 ↑ 37.8 71 1

Sort (cost=455.07..461.78 rows=2,684 width=22) (actual time=0.357..0.371 rows=71 loops=1)

  • Sort Key: agenda.cod_pac, agenda.ini_agen
  • Sort Method: quicksort Memory: 30kB
5. 0.289 0.289 ↑ 37.8 71 1

Index Scan using agenda_idx on agenda (cost=0.44..302.22 rows=2,684 width=22) (actual time=0.037..0.289 rows=71 loops=1)

  • Index Cond: (fec_agen = ('now'::cstring)::date)
  • Filter: (cod_pac <> '0'::double precision)
  • Rows Removed by Filter: 13
6. 0.328 35,529.061 ↓ 20.0 20 1

Nested Loop Anti Join (cost=261,520.35..431,294.37 rows=1 width=16) (actual time=24,231.531..35,529.061 rows=20 loops=1)

7. 0.239 33,166.453 ↓ 20.0 20 1

Nested Loop Anti Join (cost=261,519.92..431,288.59 rows=1 width=16) (actual time=24,192.848..33,166.453 rows=20 loops=1)

8. 2,204.054 27,349.294 ↓ 40.0 40 1

Hash Join (cost=261,519.48..431,286.02 rows=1 width=16) (actual time=23,946.395..27,349.294 rows=40 loops=1)

  • Hash Cond: (((pac.cod_pac)::numeric)::double precision = ag.cod_pac)
9. 1,836.177 25,144.781 ↓ 1,140,143.0 1,140,143 1

Hash Right Join (cost=261,432.61..431,198.01 rows=1 width=8) (actual time=23,925.109..25,144.781 rows=1,140,143 loops=1)

  • Hash Cond: (cp.cod_pac = (pac.cod_pac)::numeric)
  • Filter: ((cp.cod_pac IS NULL) OR (cd.folio_diag IS NULL))
  • Rows Removed by Filter: 383,865
10. 2,837.222 20,581.838 ↑ 1.0 389,447 1

Hash Right Join (cost=168,747.69..323,459.33 rows=404,184 width=14) (actual time=17,793.654..20,581.838 rows=389,447 loops=1)

  • Hash Cond: (cd.folio_diag = cp.folio_diag)
11. 13,756.740 13,756.740 ↑ 1.0 5,006,911 1

Seq Scan on cabe_diag cd (cost=0.00..131,379.03 rows=5,167,559 width=8) (actual time=64.386..13,756.740 rows=5,006,911 loops=1)

  • Filter: (cod_esp <> '23'::numeric)
  • Rows Removed by Filter: 275,115
12. 207.756 3,987.876 ↑ 1.0 389,447 1

Hash (cost=163,695.39..163,695.39 rows=404,184 width=14) (actual time=3,987.876..3,987.876 rows=389,447 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 22,352kB
13. 539.831 3,780.120 ↑ 1.0 389,447 1

Bitmap Heap Scan on cabe_presu cp (cost=16,943.33..163,695.39 rows=404,184 width=14) (actual time=3,252.575..3,780.120 rows=389,447 loops=1)

  • Recheck Cond: ((fecha_presu > date_trunc('day'::text, (now() - '6 mons'::interval))) AND (fecha_presu < ('now'::cstring)::date))
  • Heap Blocks: exact=16,270
14. 3,240.289 3,240.289 ↓ 1.0 409,186 1

Bitmap Index Scan on cabe_presu_idx (cost=0.00..16,842.28 rows=404,184 width=0) (actual time=3,240.289..3,240.289 rows=409,186 loops=1)

  • Index Cond: ((fecha_presu > date_trunc('day'::text, (now() - '6 mons'::interval))) AND (fecha_presu < ('now'::cstring)::date))
15. 2,141.544 2,726.766 ↑ 1.0 1,254,293 1

Hash (cost=71,506.70..71,506.70 rows=1,290,818 width=8) (actual time=2,726.766..2,726.766 rows=1,254,293 loops=1)

  • Buckets: 2,097,152 Batches: 2 Memory Usage: 40,871kB
16. 585.222 585.222 ↑ 1.0 1,254,293 1

Index Only Scan using paciente_pkey on paciente pac (cost=0.43..71,506.70 rows=1,290,818 width=8) (actual time=0.031..585.222 rows=1,254,293 loops=1)

  • Heap Fetches: 362
17. 0.029 0.459 ↑ 38.2 70 1

Hash (cost=53.46..53.46 rows=2,673 width=16) (actual time=0.459..0.459 rows=70 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 36kB
18. 0.430 0.430 ↑ 38.2 70 1

CTE Scan on agendados ag (cost=0.00..53.46 rows=2,673 width=16) (actual time=0.361..0.430 rows=70 loops=1)

19. 5,816.920 5,816.920 ↓ 0.0 0 40

Index Scan using agenda_idx3 on agenda a (cost=0.43..2.12 rows=2 width=8) (actual time=145.423..145.423 rows=0 loops=40)

  • Index Cond: (cod_pac = pac.cod_pac)
  • Filter: ((cod_serv = '3'::double precision) AND (fec_agen < ('now'::cstring)::date) AND (fec_agen > date_trunc('day'::text, (now() - '6 mons'::interval))))
  • Rows Removed by Filter: 59
20. 2,362.280 2,362.280 ↓ 0.0 0 20

Index Scan using c_maestro_mov_idx1 on c_maestro_mov mov (cost=0.44..3.11 rows=1 width=6) (actual time=118.114..118.114 rows=0 loops=20)

  • Index Cond: (cod_pac = (pac.cod_pac)::numeric)
  • Filter: (((tipo_mov)::text = 'AC'::text) AND (activo = '1'::double precision) AND ((fecha_tra)::double precision < date_part('epoch'::text, (('now'::cstring)::date)::timestamp without time zone)) AND ((fecha_tra)::double precision > date_part('epoch'::text, date_trunc('day'::text, ((now())::date - '6 mons'::interval)))))
  • Rows Removed by Filter: 62
21. 0.676 2,185.480 ↓ 0.0 0 20

Nested Loop (cost=1.29..8.64 rows=1 width=6) (actual time=104.487..109.274 rows=0 loops=20)

22. 1.245 1,268.980 ↓ 2.0 6 20

Nested Loop (cost=0.86..4.87 rows=3 width=14) (actual time=18.912..63.449 rows=6 loops=20)

23. 594.380 594.380 ↑ 1.1 7 20

Index Scan using cabe_presu_idx1 on cabe_presu cp_1 (cost=0.43..1.08 rows=8 width=14) (actual time=8.819..29.719 rows=7 loops=20)

  • Index Cond: (cod_pac = (pac.cod_pac)::numeric)
24. 673.355 673.355 ↑ 1.0 1 137

Index Scan using cabe_tratamiento_folio_presu_key on cabe_tratamiento ct (cost=0.43..0.46 rows=1 width=16) (actual time=4.914..4.915 rows=1 loops=137)

  • Index Cond: (folio_presu = cp_1.folio_presu)
25. 915.824 915.824 ↓ 0.0 0 112

Index Scan using deta_tratamiento_folio_trata_idx on deta_tratamiento dt (cost=0.43..1.25 rows=1 width=8) (actual time=8.139..8.177 rows=0 loops=112)

  • Index Cond: (folio_trata = ct.folio_trata)
  • Filter: ((cod_sucursal > date_part('epoch'::text, date_trunc('day'::text, (now() - '6 mons'::interval)))) AND (cod_sucursal < date_part('epoch'::text, (('now'::cstring)::date)::timestamp without time zone)))
  • Rows Removed by Filter: 6