explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1Xg7 : Optimization for: plan #BXUf

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 95.243 4,529.250 ↓ 1.6 64,654 1

Sort (cost=156,728.63..156,829.09 rows=40,186 width=157) (actual time=4,517.882..4,529.250 rows=64,654 loops=1)

  • Sort Key: source.sum DESC
  • Sort Method: external merge Disk: 6,736kB
2. 418.764 4,434.007 ↓ 1.6 64,654 1

Hash Left Join (cost=123,210.37..150,495.02 rows=40,186 width=157) (actual time=3,568.247..4,434.007 rows=64,654 loops=1)

  • Hash Cond: (source.estilo = jerarquia.estilo)
3. 208.806 2,878.248 ↓ 1.6 64,654 1

Hash Right Join (cost=83,552.16..102,045.47 rows=40,186 width=125) (actual time=2,429.939..2,878.248 rows=64,654 loops=1)

  • Hash Cond: (stock.sku = source.sku)
4. 240.925 240.925 ↑ 1.0 550,360 1

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

5. 29.570 2,428.517 ↓ 1.6 64,654 1

Hash (cost=82,303.84..82,303.84 rows=40,186 width=121) (actual time=2,428.516..2,428.517 rows=64,654 loops=1)

  • Buckets: 32,768 Batches: 2 Memory Usage: 2,879kB
6. 10.858 2,398.947 ↓ 1.6 64,654 1

Subquery Scan on source (cost=68,043.17..82,303.84 rows=40,186 width=121) (actual time=2,023.218..2,398.947 rows=64,654 loops=1)

7. 114.039 2,388.089 ↓ 1.6 64,654 1

Finalize GroupAggregate (cost=68,043.17..81,901.98 rows=40,186 width=153) (actual time=2,023.216..2,388.089 rows=64,654 loops=1)

  • Group Key: ventas.sku
8. 161.082 2,274.050 ↓ 1.3 107,267 1

Gather Merge (cost=68,043.17..80,194.07 rows=80,372 width=121) (actual time=2,023.182..2,274.050 rows=107,267 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 70.944 2,112.968 ↑ 1.1 35,756 3 / 3

Partial GroupAggregate (cost=67,043.15..69,917.13 rows=40,186 width=121) (actual time=1,985.008..2,112.968 rows=35,756 loops=3)

  • Group Key: ventas.sku
10. 1,026.386 2,042.024 ↑ 1.1 103,062 3 / 3

Sort (cost=67,043.15..67,317.83 rows=109,872 width=27) (actual time=1,984.994..2,042.024 rows=103,062 loops=3)

  • Sort Key: ventas.sku
  • Sort Method: external merge Disk: 4,344kB
11. 56.773 1,015.638 ↑ 1.1 103,062 3 / 3

Hash Semi Join (cost=10,652.26..55,211.86 rows=109,872 width=27) (actual time=182.830..1,015.638 rows=103,062 loops=3)

  • Hash Cond: (ventas.id_depto = neg_div_depto.id_depto)
12. 897.965 958.435 ↑ 1.1 104,436 3 / 3

Parallel Bitmap Heap Scan on ventas (cost=10,649.78..53,698.63 rows=109,872 width=27) (actual time=182.372..958.435 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) AND (sku IS NOT NULL))
  • Filter: (estado ~~ 'paid'::text)
  • Heap Blocks: exact=1,698
13. 60.470 60.470 ↓ 1.2 313,307 1 / 3

Bitmap Index Scan on ventas_estado_fecha_sku_idx (cost=0.00..10,583.85 rows=263,694 width=0) (actual time=181.410..181.411 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) AND (sku IS NOT NULL))
14. 0.019 0.430 ↑ 1.0 66 3 / 3

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

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

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

  • Filter: (id_depto IS NOT NULL)
16. 183.314 1,136.995 ↑ 1.0 786,409 1

Hash (cost=23,684.09..23,684.09 rows=786,409 width=35) (actual time=1,136.995..1,136.995 rows=786,409 loops=1)

  • Buckets: 65,536 Batches: 16 Memory Usage: 3,841kB
17. 953.681 953.681 ↑ 1.0 786,409 1

Seq Scan on jerarquia (cost=0.00..23,684.09 rows=786,409 width=35) (actual time=1.445..953.681 rows=786,409 loops=1)