explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rHhL : Optimization for: plan #l4DZ

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.394 23,363.478 ↓ 19.0 19 1

Limit (cost=45,523.24..45,523.24 rows=1 width=630) (actual time=23,362.948..23,363.478 rows=19 loops=1)

2. 0.436 23,363.084 ↓ 19.0 19 1

Sort (cost=45,523.24..45,523.24 rows=1 width=630) (actual time=23,362.928..23,363.084 rows=19 loops=1)

  • Sort Key: (lower((cliente1_.nome)::text)), cliente1_.id
  • Sort Method: quicksort Memory: 29kB
3. 0.462 23,362.648 ↓ 19.0 19 1

Group (cost=45,523.21..45,523.23 rows=1 width=630) (actual time=23,362.063..23,362.648 rows=19 loops=1)

  • Group Key: cliente1_.id, negativaca0_.data_negativacao, funcionari2_.nome, loja3_.nome
4. 0.350 23,362.186 ↓ 19.0 19 1

Sort (cost=45,523.21..45,523.22 rows=1 width=598) (actual time=23,362.031..23,362.186 rows=19 loops=1)

  • Sort Key: cliente1_.id, negativaca0_.data_negativacao, funcionari2_.nome, loja3_.nome
  • Sort Method: quicksort Memory: 27kB
5. 1.124 23,361.836 ↓ 19.0 19 1

Nested Loop (cost=38,200.89..45,523.20 rows=1 width=598) (actual time=14,877.624..23,361.836 rows=19 loops=1)

  • Join Filter: (negativaca0_.loja_id = loja3_.id)
  • Rows Removed by Join Filter: 65
6. 0.496 23,359.952 ↓ 19.0 19 1

Nested Loop (cost=38,200.89..45,522.04 rows=1 width=90) (actual time=14,877.480..23,359.952 rows=19 loops=1)

7. 15.016 23,358.943 ↓ 19.0 19 1

Nested Loop (cost=38,200.60..45,520.56 rows=1 width=57) (actual time=14,877.390..23,358.943 rows=19 loops=1)

  • Join Filter: (negativaca0_.funcionario_id = funcionari2_.id)
  • Rows Removed by Join Filter: 1,459
8. 260.976 23,329.620 ↓ 19.0 19 1

Nested Loop (cost=38,200.60..45,513.01 rows=1 width=40) (actual time=14,876.841..23,329.620 rows=19 loops=1)

9. 3,736.222 22,856.748 ↓ 11,772.0 11,772 1

Unique (cost=38,200.32..45,504.69 rows=1 width=8) (actual time=9,281.466..22,856.748 rows=11,772 loops=1)

10. 4,017.532 19,120.526 ↓ 359,478.0 359,478 1

Gather Merge (cost=38,200.32..45,504.69 rows=1 width=8) (actual time=9,281.449..19,120.526 rows=359,478 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
11. 3,656.929 15,102.994 ↓ 119,826.0 119,826 3 / 3

Merge Anti Join (cost=37,200.30..44,504.55 rows=1 width=8) (actual time=9,205.595..15,102.994 rows=119,826 loops=3)

  • Merge Cond: (prestacao4_.cliente_id = prestacao5_.cliente_id)
12. 6,375.315 10,868.892 ↑ 1.2 153,015 3 / 3

Sort (cost=28,329.67..28,799.42 rows=187,898 width=8) (actual time=8,799.744..10,868.892 rows=153,015 loops=3)

  • Sort Key: prestacao4_.cliente_id
  • Sort Method: external merge Disk: 2,800kB
  • Worker 0: Sort Method: external merge Disk: 2,680kB
  • Worker 1: Sort Method: external merge Disk: 2,648kB
13. 4,493.577 4,493.577 ↑ 1.2 153,015 3 / 3

Parallel Seq Scan on prestacao prestacao4_ (cost=0.00..9,301.19 rows=187,898 width=8) (actual time=1.022..4,493.577 rows=153,015 loops=3)

  • Filter: ((recebimento_id IS NOT NULL) AND (data_vencimento < '2020-07-07'::date))
  • Rows Removed by Filter: 7,510
14. 335.046 577.173 ↑ 1.7 12,086 3 / 3

Sort (cost=8,870.62..8,920.79 rows=20,065 width=8) (actual time=405.818..577.173 rows=12,086 loops=3)

  • Sort Key: prestacao5_.cliente_id
  • Sort Method: quicksort Memory: 951kB
  • Worker 0: Sort Method: quicksort Memory: 951kB
  • Worker 1: Sort Method: quicksort Memory: 951kB
15. 240.256 242.127 ↑ 1.7 12,088 3 / 3

Bitmap Heap Scan on prestacao prestacao5_ (cost=387.30..7,436.74 rows=20,065 width=8) (actual time=2.238..242.127 rows=12,088 loops=3)

  • Recheck Cond: (recebimento_id IS NULL)
  • Filter: (data_vencimento < '2020-07-07'::date)
  • Rows Removed by Filter: 8,802
  • Heap Blocks: exact=2,745
16. 1.871 1.871 ↓ 1.0 20,890 3 / 3

Bitmap Index Scan on prestacao_recebimento_id_ix (cost=0.00..382.28 rows=20,515 width=0) (actual time=1.863..1.871 rows=20,890 loops=3)

  • Index Cond: (recebimento_id IS NULL)
17. 211.896 211.896 ↓ 0.0 0 11,772

Index Scan using idx_negativacao_cliente on negativacao_cliente negativaca0_ (cost=0.28..8.30 rows=1 width=32) (actual time=0.018..0.018 rows=0 loops=11,772)

  • Index Cond: (cliente_id = prestacao4_.cliente_id)
18. 14.307 14.307 ↑ 2.0 78 19

Seq Scan on funcionario funcionari2_ (cost=0.00..5.58 rows=158 width=33) (actual time=0.010..0.753 rows=78 loops=19)

19. 0.513 0.513 ↑ 1.0 1 19

Index Scan using cliente_pkey on cliente cliente1_ (cost=0.29..1.48 rows=1 width=49) (actual time=0.026..0.027 rows=1 loops=19)

  • Index Cond: (id = negativaca0_.cliente_id)
20. 0.760 0.760 ↑ 1.8 4 19

Seq Scan on loja loja3_ (cost=0.00..1.07 rows=7 width=524) (actual time=0.009..0.040 rows=4 loops=19)

Planning time : 9.567 ms
Execution time : 23,368.317 ms