>
> Voce poderia rodar EXPLAIN ANALYZE de ambas e colar aqui para nós por
> favor?
EXPLAIN ANALYZE
select c_cpd, sum(n_cant) from (
select c_cpd, sum(n_cant) as n_cant from movie
where c_sucursal>='001' and c_sucursal<='001'
group by c_cpd
union all
select c_cpd, sum(n_cant)*-1 as n_cant from movis
where c_sucursal>='001' and c_sucursal<='001'
group by c_cpd
) as movi
group by c_cpd
order by c_cpd
"Sort (cost=6785.20..6785.70 rows=200 width=41) (actual
time=932.636..937.650 rows=12912 loops=1)"
" Sort Key: movie.c_cpd"
" Sort Method: quicksort Memory: 761kB"
" -> HashAggregate (cost=6775.06..6777.56 rows=200 width=41) (actual
time=833.629..843.035 rows=12912 loops=1)"
" -> Append (cost=1156.94..6734.38 rows=8136 width=41) (actual
time=140.748..803.186 rows=22074 loops=1)"
" -> HashAggregate (cost=1156.94..1230.74 rows=5904 width=18)
(actual time=140.746..159.345 rows=12896 loops=1)"
" -> Seq Scan on movie (cost=0.00..1029.95 rows=25397
width=18) (actual time=0.030..95.165 rows=25397 loops=1)"
" Filter: ((c_sucursal >= '001'::bpchar) AND
(c_sucursal <= '001'::bpchar))"
" -> HashAggregate (cost=5388.80..5422.28 rows=2232 width=18)
(actual time=614.433..627.373 rows=9178 loops=1)"
" -> Seq Scan on movis (cost=0.00..4883.60 rows=101040
width=18) (actual time=0.069..389.244 rows=128404 loops=1)"
" Filter: ((c_sucursal >= '001'::bpchar) AND
(c_sucursal <= '001'::bpchar))"
"Total runtime: 942.074 ms"
************************************************************************************************
EXPLAIN ANALYZE
select movie.c_cpd, sum(movie.n_cant + (movis.n_cant)*-1) as n_cant
from movie
JOIN movis ON (movis.c_cpd = movie.c_cpd)
where movie.c_sucursal>='001' and movie.c_sucursal<='001'
group by movie.c_cpd
"GroupAggregate (cost=14677.89..43425.50 rows=5904 width=30) (actual
time=1806.037..13908.462 rows=9162 loops=1)"
" -> Merge Join (cost=14677.89..36974.43 rows=1269551 width=30) (actual
time=1729.832..8146.105 rows=2174865 loops=1)"
" Merge Cond: (movie.c_cpd = movis.c_cpd)"
" -> Index Scan using ind_cpd on movie (cost=0.00..3191.92
rows=25397 width=18) (actual time=0.179..112.027 rows=25397 loops=1)"
" Filter: ((c_sucursal >= '001'::bpchar) AND (c_sucursal <=
'001'::bpchar))"
" -> Materialize (cost=14677.63..15940.63 rows=101040 width=18)
(actual time=1729.605..3261.645 rows=2174914 loops=1)"
" -> Sort (cost=14677.63..14930.23 rows=101040 width=18)
(actual time=1729.599..2297.426 rows=128404 loops=1)"
" Sort Key: movis.c_cpd"
" Sort Method: external merge Disk: 3136kB"
" -> Seq Scan on movis (cost=0.00..4378.40 rows=101040
width=18) (actual time=0.056..132.679 rows=128404 loops=1)"
"Total runtime: 13913.799 ms"
************************************************************************************************
EXPLAIN ANALYZE
SELECT coalesce(movie.c_cpd, movis.c_cpd) AS c_cpd,
sum(movie.n_cant - movis.n_cant) AS n_cant
FROM movie FULL OUTER JOIN movis ON (movis.c_cpd = movie.c_cpd)
GROUP BY 1
ORDER BY 1
"GroupAggregate (cost=263195.19..291760.09 rows=1269551 width=36) (actual
time=35667.063..41756.503 rows=12912 loops=1)"
" -> Sort (cost=263195.19..266369.07 rows=1269551 width=36) (actual
time=35667.042..37349.399 rows=2179180 loops=1)"
" Sort Key: (COALESCE(movie.c_cpd, movis.c_cpd))"
" Sort Method: external sort Disk: 94408kB"
" -> Merge Full Join (cost=14677.63..36849.33 rows=1269551
width=36) (actual time=1726.882..8855.795 rows=2179180 loops=1)"
" Merge Cond: (movie.c_cpd = movis.c_cpd)"
" -> Index Scan using ind_cpd on movie (cost=0.00..3064.94
rows=25397 width=18) (actual time=0.028..66.888 rows=25397 loops=1)"
" -> Materialize (cost=14677.63..15940.63 rows=101040
width=18) (actual time=1726.841..3383.342 rows=2174914 loops=1)"
" -> Sort (cost=14677.63..14930.23 rows=101040
width=18) (actual time=1726.835..2310.608 rows=128404 loops=1)"
" Sort Key: movis.c_cpd"
" Sort Method: external merge Disk: 3136kB"
" -> Seq Scan on movis (cost=0.00..4378.40
rows=101040 width=18) (actual time=0.050..127.825 rows=128404 loops=1)"
"Total runtime: 41791.048 ms"
Grato pelas respostas...
--
*:-.,_,.-:*'``'*:-.,_,.-:*:-.,_,.-:*'``'*:-.,_,.-:
:: Rodrigo Machado ::
FlaRo Sistemas
Linux User #338262
Lat S25º49'20.11' - Lon W55º07'12.05''
*:-.,_,.-:*'``'*:-.,_,.-:*:-.,_,.-:*'``'*:-.,_,.-:
_______________________________________________
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
