explain.depesz.com

PostgreSQL's explain analyze made readable

Result: neKY : Optimization for: Optimization for: plan #7qBa; plan #IJvK

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 4,723.739 7,517.125 ↓ 2.4 1,264,849 1

Nested Loop (cost=5,020.13..7,722,747.56 rows=524,883 width=133) (actual time=21.033..7,517.125 rows=1,264,849 loops=1)

2. 457.896 903.713 ↓ 2.2 629,891 1

Hash Join (cost=5,019.56..910,817.54 rows=281,620 width=86) (actual time=20.969..903.713 rows=629,891 loops=1)

  • Hash Cond: (associadovenda.asv_id_associado = date_to_process.id_associado)
3. 424.916 441.475 ↓ 1.1 629,891 1

Index Scan using idx_data_referencia on associadovenda (cost=4,940.06..908,344.48 rows=563,239 width=86) (actual time=16.617..441.475 rows=629,891 loops=1)

  • Index Cond: ((asv_data_referencia >= '2018-09-05 00:00:00'::timestamp without time zone) AND (asv_data_referencia <= '2018-09-05 23:59:59'::timestamp without time zone))
  • Filter: (NOT (hashed SubPlan 1))
4.          

SubPlan (forIndex Scan)

5. 16.559 16.559 ↓ 1.0 22 1

Seq Scan on associado (cost=0.00..4,939.43 rows=21 width=4) (actual time=0.014..16.559 rows=22 loops=1)

  • Filter: (asd_erp <> ALL ('{1,2,3}'::integer[]))
  • Rows Removed by Filter: 6558
6. 1.030 4.342 ↓ 18.2 3,637 1

Hash (cost=77.00..77.00 rows=200 width=4) (actual time=4.342..4.342 rows=3,637 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 160kB
7. 2.399 3.312 ↓ 18.2 3,637 1

HashAggregate (cost=75.00..77.00 rows=200 width=4) (actual time=2.335..3.312 rows=3,637 loops=1)

  • Group Key: date_to_process.id_associado
8. 0.913 0.913 ↑ 1.1 3,637 1

Seq Scan on date_to_process (cost=0.00..64.80 rows=4,080 width=4) (actual time=0.010..0.913 rows=3,637 loops=1)

9. 1,889.673 1,889.673 ↑ 276.5 2 629,891

Index Scan using idx_110744_fkassociadov84690 on associadovendaitens (cost=0.57..18.16 rows=553 width=51) (actual time=0.002..0.003 rows=2 loops=629,891)

  • Index Cond: (asi_id_venda = associadovenda.asv_id)