Re: Trabajando con Grandes BD

Vida Luz escribió:
> con explain este es el costo que se tiene: aporx unos 2.7 minutos.
>
>
>
> c=# explain analyze SELECT count(distinct cliente_cod) FROM dm.venta
> GROUP BY geren_cod;
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------------------
>
> GroupAggregate (cost=421813.88..437247.45 rows=3 width=14) (actual
> time=159638.993..165536.431 rows=3 loops=1)
> -> Sort (cost=421813.88..426958.39 rows=2057804 width=14) (actual
> time=152000.054..158792.629 rows=2057804 loops=1)
> Sort Key: geren_cod
> -> Seq Scan on venta (cost=0.00..180972.04 rows=2057804
> width=14) (actual time=3.358..29855.519 rows=2057804 loops=1)
> Total runtime: 166281.479 ms
> (5 rows)
>
>

Un poco por curiosidad, podrias probar estos select y decirnos si hay diferencias con ese volumen de
datos:

select
gr.geren_cod, count(gr.cliente_cod)
from (select geren_cod, cliente_cod from dm.venta group by geren_cod, cliente_cod) as gr
group by gr.geren_cod

select
gr.geren_cod, count(gr.cliente_cod)
from (select distinct geren_cod, cliente_cod from dm.venta) as gr
group by gr.geren_cod

Gracias.

--
*****************************************
Oswaldo Hernández
oswaldo (@) soft-com (.) es
*****************************************