2014-07-09 13:49 GMT-03:00 Rafael Fialho <[email protected]>:
>
> Execute somente o explain para verificar uma prévia do plano de execução.
> Não tens snapshots anteriores, para fazer uma comparação?
>
Gerei o resultado usando LIMIT e tirando outras consultas a frente, pois
são uma série de consultas usando WITH.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------
Limit (cost=477.30..15059.88 rows=20 width=12) (actual
time=44512.373..47486.520 rows=20 loops=1)
Buffers: shared hit=12205 read=21761
I/O Timings: read=47078.702
CTE tb_movimentacoes_cnj_julgamento
-> Recursive Union (cost=0.00..462.18 rows=3097 width=4) (actual
time=0.063..1.172 rows=105 loops=1)
Buffers: shared hit=110
-> Seq Scan on tipo_movimento_cnj (cost=0.00..31.78 rows=17
width=4) (actual time=0.060..0.444 rows=16 loops=1)
Filter: (inttipomovimentocnjid = ANY
('{193,133,944,373,374,11019,11423,11424,11425,11426,108,122,352,353,357,358,905}'::integer[]))
Rows Removed by Filter: 297
Buffers: shared hit=22
-> Hash Join (cost=5.53..36.85 rows=308 width=4) (actual
time=0.073..0.168 rows=22 loops=4)
Hash Cond: (tm.inttipomovimentocnjpaiid = tmj.id)
Buffers: shared hit=88
-> Seq Scan on tipo_movimento_cnj tm (cost=0.00..27.09
rows=308 width=8) (actual time=0.002..0.077 rows=308 loops=4)
Filter: (inttipomovimentocnjid <> ALL
('{198,871,200,10953,196}'::integer[]))
Rows Removed by Filter: 5
Buffers: shared hit=88
-> Hash (cost=3.40..3.40 rows=170 width=4) (actual
time=0.009..0.009 rows=26 loops=4)
Buckets: 1024 Batches: 1 Memory Usage: 2kB
-> WorkTable Scan on
tb_movimentacoes_cnj_julgamento tmj (cost=0.00..3.40 rows=170 width=4)
(actual time=0.001..0.003 rows=26 loops=4)
-> Unique (cost=15.12..4220815472.11 rows=5788848 width=12) (actual
time=44512.372..47486.510 rows=20 loops=1)
Buffers: shared hit=12205 read=21761
I/O Timings: read=47078.702
-> Nested Loop (cost=15.12..4219667708.94 rows=229552634
width=12) (actual time=44512.368..47486.464 rows=21 loops=1)
Join Filter: (movjulg.inttipomovimentoid = tmcj.id)
Rows Removed by Join Filter: 494289
Buffers: shared hit=12205 read=21761
I/O Timings: read=47078.702
-> Merge Append (cost=15.12..185854018.78 rows=57888475
width=16) (actual time=570.380..47332.414 rows=4708 loops=1)
Sort Key: movjulg.pk_processo,
movjulg.intmunicipiocomarcaid
Buffers: shared hit=12095 read=21761
I/O Timings: read=47078.702
-> Index Scan using ix_movimentacao_04 on
movimentacao movjulg (cost=0.56..52676472.59 rows=16518832 width=16)
(actual time=359.452..39891.966 rows=216 loops=1)
Index Cond: (dtamovimento <= '2013-12-31
23:59:59'::timestamp without time zone)
Filter: ((bolcancelado)::text <> 'S'::text)
Rows Removed by Filter: 195962
Buffers: shared hit=11589 read=19548
I/O Timings: read=39666.394
-> Index Scan using
movimentacao_2008_pk_processo_intmunicipiocomarcaid_bolcanc_idx on
movimentacao_2008 movjulg_1 (cost=0.56..10349986.80 rows=3266779 width=16)
(actual time=57.661..
1599.154 rows=10 loops=1)
Index Cond: (dtamovimento <= '2013-12-31
23:59:59'::timestamp without time zone)
Filter: ((bolcancelado)::text <> 'S'::text)
Rows Removed by Filter: 643
Buffers: shared hit=19 read=407
I/O Timings: read=1595.393
-> Index Scan using
movimentacao_2013_pk_processo_intmunicipiocomarcaid_bolcanc_idx on
movimentacao_2013 movjulg_2 (cost=0.56..41335475.03 rows=12954843
width=16) (actual time=37.514.
.1736.034 rows=927 loops=1)
Index Cond: (dtamovimento <= '2013-12-31
23:59:59'::timestamp without time zone)
Filter: ((bolcancelado)::text <> 'S'::text)
Rows Removed by Filter: 60
Buffers: shared hit=57 read=433
I/O Timings: read=1730.047
-> Index Scan using
movimentacao_2012_pk_processo_intmunicipiocomarcaid_bolcanc_idx on
movimentacao_2012 movjulg_3 (cost=0.56..33450909.40 rows=10540483
width=16) (actual time=41.266.
.2364.773 rows=2572 loops=1)
Index Cond: (dtamovimento <= '2013-12-31
23:59:59'::timestamp without time zone)
Filter: ((bolcancelado)::text <> 'S'::text)
Rows Removed by Filter: 2
Buffers: shared hit=95 read=946
I/O Timings: read=2355.489
-> Index Scan using
movimentacao_2011_pk_processo_intmunicipiocomarcaid_bolcanc_idx on
movimentacao_2011 movjulg_4 (cost=0.56..26788832.98 rows=8489590 width=16)
(actual time=26.105..
550.640 rows=407 loops=1)
Index Cond: (dtamovimento <= '2013-12-31
23:59:59'::timestamp without time zone)
Filter: ((bolcancelado)::text <> 'S'::text)
Rows Removed by Filter: 3
Buffers: shared hit=79 read=168
I/O Timings: read=548.122
-> Index Scan using
movimentacao_2010_pk_processo_intmunicipiocomarcaid_bolcanc_idx on
movimentacao_2010 movjulg_5 (cost=0.56..19482473.98 rows=6117935 width=16)
(actual time=48.338..
1187.383 rows=581 loops=1)
Index Cond: (dtamovimento <= '2013-12-31
23:59:59'::timestamp without time zone)
Filter: ((bolcancelado)::text <> 'S'::text)
Rows Removed by Filter: 67
Buffers: shared hit=256 read=259
I/O Timings: read=1183.257
-> Sort (cost=10.84..10.87 rows=13 width=16) (actual
time=0.033..0.033 rows=0 loops=1)
Sort Key: movjulg_6.pk_processo,
movjulg_6.intmunicipiocomarcaid
Sort Method: quicksort Memory: 25kB
-> Seq Scan on movimentacao_2000 movjulg_6
(cost=0.00..10.60 rows=13 width=16) (actual time=0.002..0.002 rows=0
loops=1)
Filter: (((bolcancelado)::text <>
'S'::text) AND (dtamovimento <= '2013-12-31 23:59:59'::timestamp without
time zone))
-> CTE Scan on tb_movimentacoes_cnj_julgamento tmcj
(cost=0.00..61.94 rows=3097 width=4) (actual time=0.000..0.015 rows=105
loops=4708)
Buffers: shared hit=110
Total runtime: 47486.710 ms
Bruno E. A. Silva.
Analista de Sistemas.
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
