explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IJvK : Optimization for: plan #7qBa

Settings

Optimization path:

Optimization(s) for this plan:

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

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

2. 457.180 912.365 ↓ 2.2 629,891 1

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

  • Hash Cond: (associadovenda.asv_id_associado = date_to_process.id_associado)
3. 434.194 450.838 ↓ 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.705..450.838 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.644 16.644 ↓ 1.0 22 1

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

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

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

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

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

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

Seq Scan on date_to_process (cost=0.00..64.80 rows=4,080 width=4) (actual time=0.011..0.920 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)