explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IrtJ : Optimization for: plan #mWjY

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.016 2,019.543 ↑ 1.0 10 1

Sort (cost=221.21..221.23 rows=10 width=42) (actual time=2,019.542..2,019.543 rows=10 loops=1)

  • Sort Key: ((sum((d.unidades_totales_administradas * d.coste_unidad_medicamento_mes)) / sum(d.unidades_totales_administradas))) DESC
  • Sort Method: quicksort Memory: 25kB
2. 0.438 2,019.527 ↑ 1.0 10 1

Hash Right Join (cost=172.47..221.04 rows=10 width=42) (actual time=2,018.755..2,019.527 rows=10 loops=1)

  • Hash Cond: (m.id = d.id_medicamento)
3. 0.470 0.470 ↑ 1.0 2,183 1

Seq Scan on medicamentos m (cost=0.00..42.83 rows=2,183 width=34) (actual time=0.068..0.470 rows=2,183 loops=1)

4. 0.005 2,018.619 ↑ 1.0 10 1

Hash (cost=172.35..172.35 rows=10 width=12) (actual time=2,018.619..2,018.619 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
5. 0.005 2,018.614 ↑ 1.0 10 1

Limit (cost=172.22..172.25 rows=10 width=12) (actual time=2,018.609..2,018.614 rows=10 loops=1)

6. 2.310 2,018.609 ↑ 3.8 10 1

Sort (cost=172.22..172.32 rows=38 width=12) (actual time=2,018.608..2,018.609 rows=10 loops=1)

  • Sort Key: ((sum((d.unidades_totales_administradas * d.coste_unidad_medicamento_mes)) / sum(d.unidades_totales_administradas))) DESC
  • Sort Method: top-N heapsort Memory: 25kB
7. 362.077 2,016.299 ↓ 57.4 2,183 1

GroupAggregate (cost=170.40..171.40 rows=38 width=12) (actual time=1,380.081..2,016.299 rows=2,183 loops=1)

  • Group Key: d.id_medicamento
8. 994.764 1,654.222 ↓ 20,836.8 875,144 1

Sort (cost=170.40..170.51 rows=42 width=16) (actual time=1,379.838..1,654.222 rows=875,144 loops=1)

  • Sort Key: d.id_medicamento
  • Sort Method: external merge Disk: 22,320kB
9. 447.734 659.458 ↓ 20,836.8 875,144 1

Bitmap Heap Scan on dispensaciones_cache d (cost=4.96..169.27 rows=42 width=16) (actual time=218.163..659.458 rows=875,144 loops=1)

  • Recheck Cond: ((id_hospital = 290) AND (administraciond_normalizada >= 20,180,101) AND (administraciond_normalizada <= 20,181,231))
  • Heap Blocks: exact=17,677
10. 211.724 211.724 ↓ 20,836.8 875,144 1

Bitmap Index Scan on dispensacion2 (cost=0.00..4.95 rows=42 width=0) (actual time=211.724..211.724 rows=875,144 loops=1)

  • Index Cond: ((id_hospital = 290) AND (administraciond_normalizada >= 20,180,101) AND (administraciond_normalizada <= 20,181,231))
Planning time : 0.385 ms
Execution time : 2,026.407 ms