explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MxQ : Optimization for: ads; plan #hYOe

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 121.339 8,381.640 ↑ 4.1 64,662 1

Subquery Scan on source (cost=220,503.47..227,749.67 rows=263,498 width=125) (actual time=8,228.213..8,381.640 rows=64,662 loops=1)

2. 254.795 8,260.301 ↑ 4.1 64,662 1

Sort (cost=220,503.47..221,162.22 rows=263,498 width=110) (actual time=8,228.203..8,260.301 rows=64,662 loops=1)

  • Sort Key: (sum(ventas.monto)) DESC, ventas.sku, stock.stock_disponible, jerarquia.costo_calculado, jerarquia.descripcion, ventas.depto, (sum(ventas.cantidad))
  • Sort Method: external merge Disk: 6,576kB
3. 196.056 8,005.506 ↑ 4.1 64,662 1

Finalize GroupAggregate (cost=142,671.76..181,466.36 rows=263,498 width=110) (actual time=7,291.131..8,005.506 rows=64,662 loops=1)

  • Group Key: ventas.sku, stock.stock_disponible, jerarquia.costo_calculado, jerarquia.descripcion, ventas.depto, ventas.estado, ventas.id_depto
4. 345.400 7,809.450 ↑ 2.0 107,400 1

Gather Merge (cost=142,671.76..172,683.09 rows=219,582 width=110) (actual time=7,291.104..7,809.450 rows=107,400 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 101.066 7,464.050 ↑ 3.1 35,800 3 / 3

Partial GroupAggregate (cost=141,671.73..146,337.85 rows=109,791 width=110) (actual time=7,243.903..7,464.050 rows=35,800 loops=3)

  • Group Key: ventas.sku, stock.stock_disponible, jerarquia.costo_calculado, jerarquia.descripcion, ventas.depto, ventas.estado, ventas.id_depto
6. 1,413.616 7,362.984 ↑ 1.1 103,062 3 / 3

Sort (cost=141,671.73..141,946.21 rows=109,791 width=70) (actual time=7,243.891..7,362.984 rows=103,062 loops=3)

  • Sort Key: ventas.sku, stock.stock_disponible, jerarquia.costo_calculado, jerarquia.descripcion, ventas.depto, ventas.estado, ventas.id_depto
  • Sort Method: external merge Disk: 8,768kB
7. 159.736 5,949.368 ↑ 1.1 103,062 3 / 3

Hash Semi Join (cost=67,577.23..127,975.31 rows=109,791 width=70) (actual time=3,882.365..5,949.368 rows=103,062 loops=3)

  • Hash Cond: (ventas.id_depto = neg_div_depto.id_depto)
8. 739.580 5,789.469 ↑ 1.1 104,436 3 / 3

Hash Left Join (cost=67,574.74..126,463.20 rows=109,791 width=70) (actual time=3,881.322..5,789.469 rows=104,436 loops=3)

  • Hash Cond: (ventas.estilo = jerarquia.estilo)
9. 431.399 2,755.378 ↑ 1.1 104,436 3 / 3

Hash Left Join (cost=28,130.14..77,273.97 rows=109,791 width=49) (actual time=1,585.611..2,755.378 rows=104,436 loops=3)

  • Hash Cond: (ventas.sku = stock.sku)
10. 786.416 824.500 ↑ 1.1 104,436 3 / 3

Parallel Bitmap Heap Scan on ventas (cost=7,636.04..50,652.25 rows=109,791 width=45) (actual time=84.459..824.500 rows=104,436 loops=3)

  • Recheck Cond: ((fecha >= (concat('2019-05-26 23:30:00', '-04'))::timestamp with time zone) AND (fecha <= (concat('2019-05-29 23:59:59', '-04'))::timestamp with time zone))
  • Filter: (estado ~~ 'paid'::text)
  • Heap Blocks: exact=1,644
11. 38.084 38.084 ↓ 1.2 313,307 1 / 3

Bitmap Index Scan on ventas_estado_fecha_idx (cost=0.00..7,570.17 rows=263,498 width=0) (actual time=114.252..114.252 rows=313,307 loops=1)

  • Index Cond: ((estado = 'paid'::text) AND (fecha >= (concat('2019-05-26 23:30:00', '-04'))::timestamp with time zone) AND (fecha <= (concat('2019-05-29 23:59:59', '-04'))::timestamp with time zone))
12. 237.061 1,499.479 ↑ 1.0 550,360 3 / 3

Hash (cost=10,926.60..10,926.60 rows=550,360 width=13) (actual time=1,499.479..1,499.479 rows=550,360 loops=3)

  • Buckets: 131,072 Batches: 16 Memory Usage: 2,592kB
13. 1,262.418 1,262.418 ↑ 1.0 550,360 3 / 3

Seq Scan on stock (cost=0.00..10,926.60 rows=550,360 width=13) (actual time=0.129..1,262.418 rows=550,360 loops=3)

14. 518.806 2,294.511 ↓ 1.0 786,409 3 / 3

Hash (cost=23,613.60..23,613.60 rows=779,360 width=35) (actual time=2,294.510..2,294.511 rows=786,409 loops=3)

  • Buckets: 65,536 Batches: 16 Memory Usage: 3,772kB
15. 1,775.705 1,775.705 ↓ 1.0 786,409 3 / 3

Seq Scan on jerarquia (cost=0.00..23,613.60 rows=779,360 width=35) (actual time=0.394..1,775.705 rows=786,409 loops=3)

16. 0.025 0.163 ↑ 1.0 66 3 / 3

Hash (cost=1.66..1.66 rows=66 width=3) (actual time=0.163..0.163 rows=66 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
17. 0.138 0.138 ↑ 1.0 66 3 / 3

Seq Scan on neg_div_depto (cost=0.00..1.66 rows=66 width=3) (actual time=0.121..0.138 rows=66 loops=3)

  • Filter: (id_depto IS NOT NULL)