explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JSjo : Optimization for: plan #qgft

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.308 19,682.168 ↓ 19.0 19 1

Limit (cost=45,523.24..45,523.24 rows=1 width=630) (actual time=19,681.726..19,682.168 rows=19 loops=1)

2. 0.346 19,681.860 ↓ 19.0 19 1

Sort (cost=45,523.24..45,523.24 rows=1 width=630) (actual time=19,681.707..19,681.860 rows=19 loops=1)

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

Group (cost=45,523.21..45,523.23 rows=1 width=630) (actual time=19,680.954..19,681.514 rows=19 loops=1)

  • Group Key: cliente1_.id, negativaca0_.data_negativacao, funcionari2_.nome, loja3_.nome
4. 0.409 19,681.069 ↓ 19.0 19 1

Sort (cost=45,523.21..45,523.22 rows=1 width=598) (actual time=19,680.915..19,681.069 rows=19 loops=1)

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

Nested Loop (cost=38,200.89..45,523.20 rows=1 width=598) (actual time=10,610.946..19,680.660 rows=19 loops=1)

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

Nested Loop (cost=38,200.89..45,522.04 rows=1 width=90) (actual time=10,610.825..19,678.494 rows=19 loops=1)

7. 25.377 19,677.555 ↓ 19.0 19 1

Nested Loop (cost=38,200.60..45,520.56 rows=1 width=57) (actual time=10,610.773..19,677.555 rows=19 loops=1)

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

Nested Loop (cost=38,200.60..45,513.01 rows=1 width=40) (actual time=10,610.253..19,638.232 rows=19 loops=1)

9. 3,742.744 19,176.689 ↓ 11,772.0 11,772 1

Unique (cost=38,200.32..45,504.69 rows=1 width=8) (actual time=5,927.893..19,176.689 rows=11,772 loops=1)

10. 3,458.436 15,433.945 ↓ 359,478.0 359,478 1

Gather Merge (cost=38,200.32..45,504.69 rows=1 width=8) (actual time=5,927.874..15,433.945 rows=359,478 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
11. 3,797.902 11,975.509 ↓ 119,826.0 119,826 3 / 3

Merge Anti Join (cost=37,200.30..44,504.55 rows=1 width=8) (actual time=5,865.180..11,975.509 rows=119,826 loops=3)

  • Merge Cond: (prestacao4_.cliente_id = prestacao5_.cliente_id)
12. 4,904.215 7,656.478 ↑ 1.2 153,015 3 / 3

Sort (cost=28,329.67..28,799.42 rows=187,898 width=8) (actual time=5,492.842..7,656.478 rows=153,015 loops=3)

  • Sort Key: prestacao4_.cliente_id
  • Sort Method: external merge Disk: 2,680kB
  • Worker 0: Sort Method: external merge Disk: 2,704kB
  • Worker 1: Sort Method: external merge Disk: 2,752kB
13. 2,752.263 2,752.263 ↑ 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=0.490..2,752.263 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. 325.264 521.129 ↑ 1.7 12,086 3 / 3

Sort (cost=8,870.62..8,920.79 rows=20,065 width=8) (actual time=372.288..521.129 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. 194.874 195.865 ↑ 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=1.296..195.865 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. 0.991 0.991 ↓ 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=0.983..0.991 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. 13.946 13.946 ↑ 2.0 78 19

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

19. 0.418 0.418 ↑ 1.0 1 19

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

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

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

Planning time : 10.852 ms
Execution time : 19,684.539 ms