explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 22LU

Settings
# exclusive inclusive rows x rows loops node
1. 0.026 133,905.031 ↑ 556,147.1 106 1

Nested Loop Left Join (cost=625,219,146.15..626,987,693.90 rows=58,951,591 width=262) (actual time=133,905.001..133,905.031 rows=106 loops=1)

2.          

CTE dados

3. 2,318.477 133,904.565 ↑ 556,147.1 106 1

Unique (cost=623,303,219.43..623,892,735.34 rows=58,951,591 width=73) (actual time=124,166.622..133,904.565 rows=106 loops=1)

4. 30,536.212 131,586.088 ↑ 4.4 13,530,799 1

Sort (cost=623,303,219.43..623,450,598.41 rows=58,951,591 width=73) (actual time=124,166.620..131,586.088 rows=13,530,799 loops=1)

  • Sort Key: lot.descricao, (((a.cabecalho ->> 'numero_cnj'::text))::character varying), paud.fkprocessosagenda
  • Sort Method: external merge Disk: 1050744kB
5. 5,632.583 101,049.876 ↑ 4.4 13,530,799 1

Merge Join (cost=2,933.49..607,836,329.76 rows=58,951,591 width=73) (actual time=12,865.954..101,049.876 rows=13,530,799 loops=1)

  • Merge Cond: (pag.id = paud.fkprocessosagenda)
6. 656.527 2,341.237 ↑ 1.0 1,288,514 1

Nested Loop (cost=0.70..1,155,479.09 rows=1,345,706 width=41) (actual time=0.040..2,341.237 rows=1,288,514 loops=1)

7. 396.196 396.196 ↑ 1.0 1,288,514 1

Index Scan using processosagenda_pkey on processosagenda pag (cost=0.43..214,854.48 rows=1,345,706 width=8) (actual time=0.024..396.196 rows=1,288,514 loops=1)

8. 1,288.514 1,288.514 ↑ 1.0 1 1,288,514

Index Scan using lotacao_pkey on lotacao lot (cost=0.28..0.70 rows=1 width=41) (actual time=0.001..0.001 rows=1 loops=1,288,514)

  • Index Cond: (id = pag.fklotacao)
9. 13,916.144 93,076.056 ↑ 4.4 13,530,799 1

Materialize (cost=2,862.18..605,793,212.53 rows=58,951,591 width=787) (actual time=11,170.545..93,076.056 rows=13,530,799 loops=1)

10. 53,485.988 79,159.912 ↑ 4.4 13,530,799 1

Nested Loop (cost=2,862.18..605,645,833.56 rows=58,951,591 width=787) (actual time=11,170.541..79,159.912 rows=13,530,799 loops=1)

  • Join Filter: (((pcc.fk_processo = a.id) AND (pcc.fk_cnj_classe = 1268)) OR ((pcc.fk_cnj_classe = ANY ('{278,279,280,1733,12121,283,10943}'::integer[])) AND (alternatives: SubPlan 1 or hashed SubPlan 2)))
  • Rows Removed by Join Filter: 244534113
11. 1.293 1,504.583 ↓ 1.9 1,744 1

Nested Loop (cost=1.28..1,123,131.64 rows=913 width=791) (actual time=847.728..1,504.583 rows=1,744 loops=1)

12. 2.545 1,480.254 ↓ 1.7 1,772 1

Nested Loop (cost=0.85..1,121,075.16 rows=1,043 width=16) (actual time=847.698..1,480.254 rows=1,772 loops=1)

13. 1,463.509 1,463.509 ↓ 1.7 1,775 1

Index Scan using processosaudiencia_pkey on processosaudiencia paud (cost=0.43..1,116,863.02 rows=1,043 width=8) (actual time=847.649..1,463.509 rows=1,775 loops=1)

  • Filter: ((dtaudiencia_final >= '2019-08-19'::date) AND (dtaudiencia_final <= '2019-08-23'::date))
  • Rows Removed by Filter: 1056042
14. 14.200 14.200 ↑ 1.0 1 1,775

Index Only Scan using ind_processo_1g_fk_processo on processo_1g p1g (cost=0.43..4.04 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=1,775)

  • Index Cond: (fk_processo = paud.fkprocesso)
  • Heap Fetches: 42
15. 23.036 23.036 ↑ 1.0 1 1,772

Index Scan using autos_repid on autos a (cost=0.43..1.97 rows=1 width=787) (actual time=0.013..0.013 rows=1 loops=1,772)

  • Index Cond: (id = p1g.fk_processo)
  • Filter: (fk_tipo_documento_entrada <> ALL ('{14,24}'::integer[]))
  • Rows Removed by Filter: 0
16. 24,039.672 24,109.056 ↓ 1.0 147,973 1,744

Materialize (cost=2,860.89..14,696.01 rows=147,131 width=8) (actual time=0.008..13.824 rows=147,973 loops=1,744)

17. 59.717 69.384 ↓ 1.0 147,973 1

Bitmap Heap Scan on processo_cnj_classes pcc (cost=2,860.89..13,385.35 rows=147,131 width=8) (actual time=10.660..69.384 rows=147,973 loops=1)

  • Recheck Cond: ((fk_cnj_classe = 1268) OR (fk_cnj_classe = ANY ('{278,279,280,1733,12121,283,10943}'::integer[])))
  • Heap Blocks: exact=7293
18. 0.002 9.667 ↓ 0.0 0 1

BitmapOr (cost=2,860.89..2,860.89 rows=149,857 width=0) (actual time=9.667..9.667 rows=0 loops=1)

19. 1.749 1.749 ↑ 1.0 20,338 1

Bitmap Index Scan on ind_processo_cnj_classes_fk_cnj_classe (cost=0.00..395.52 rows=20,679 width=0) (actual time=1.749..1.749 rows=20,338 loops=1)

  • Index Cond: (fk_cnj_classe = 1268)
20. 7.916 7.916 ↑ 1.0 127,651 1

Bitmap Index Scan on ind_processo_cnj_classes_fk_cnj_classe (cost=0.00..2,391.81 rows=129,178 width=0) (actual time=7.916..7.916 rows=127,651 loops=1)

  • Index Cond: (fk_cnj_classe = ANY ('{278,279,280,1733,12121,283,10943}'::integer[]))
21.          

SubPlan (for Nested Loop)

22. 0.000 0.000 ↓ 0.0 0

Index Only Scan using unq_processo_cnj_assuntos_fk_processo on processo_cnj_assuntos pca1 (cost=0.43..4.47 rows=1 width=0) (never executed)

  • Index Cond: (fkprocesso = a.id)
  • Filter: (fkcnj_assunto = ANY ('{5560,12194,10949}'::integer[]))
  • Heap Fetches: 0
23. 3.690 60.285 ↑ 1.0 30,837 1

Gather (cost=1,000.00..23,898.50 rows=31,595 width=4) (actual time=0.341..60.285 rows=30,837 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
24. 56.595 56.595 ↑ 1.3 10,279 3

Parallel Seq Scan on processo_cnj_assuntos pca1_1 (cost=0.00..19,739.00 rows=13,165 width=4) (actual time=0.058..56.595 rows=10,279 loops=3)

  • Filter: (fkcnj_assunto = ANY ('{5560,12194,10949}'::integer[]))
  • Rows Removed by Filter: 455889
25.          

Initplan (for Nested Loop Left Join)

26. 0.177 9,738.363 ↑ 1.0 1 1

Aggregate (cost=1,326,410.80..1,326,410.81 rows=1 width=8) (actual time=9,738.363..9,738.363 rows=1 loops=1)

27. 9,738.186 9,738.186 ↑ 556,147.1 106 1

CTE Scan on dados d2 (cost=0.00..1,179,031.82 rows=58,951,591 width=4) (actual time=0.001..9,738.186 rows=106 loops=1)

28. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)

29. 124,166.641 124,166.641 ↑ 556,147.1 106 1

CTE Scan on dados d (cost=0.00..1,179,031.82 rows=58,951,591 width=190) (actual time=124,166.627..124,166.641 rows=106 loops=1)

Planning time : 2.536 ms
Execution time : 136,101.394 ms