Re: Trabajando con Grandes BD

Hola Oswaldo, si mejoro, el resultado ahora es:

explain analyze 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;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=191715.06..191717.56 rows=200 width=14) (actual
time=33709.323..33709.336 rows=3 loops=1)
-> HashAggregate (cost=190715.06..191115.06 rows=40000 width=14)
(actual time=33612.347..33665.907 rows=10160 loops=1)
-> Seq Scan on venta (cost=0.00..180426.04 rows=2057804
width=14) (actual time=10.260..23092.667 rows=2057804 loops=1)
Total runtime: 33734.848 ms
(4 rows)

el problema que tengo, es que usaron un servidor ocn las mismas
caracteriticas donde esta el postgres, instalaron un SQL Server sobre
windows 2003 server y ese select sin indexar lo hizo en 1.6 segundos, yo
nunca he usado MSSQL, solo he usado postgres y el problema que tengo es
que no tenemos recursos para comprar porductos windows.

Como puedo llegar a obtener el mismo tiempo de MS-SQL para obtener el
mismo resultado ?

On Thu, 15 Mar 2007, Oswaldo Hernández wrote:

> 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.
>
>
>
>From pgsql-es-ayuda-owner(at)postgresql(dot)org Thu Mar 15 13:00:01 2007
Received: from localhost (maia-4.hub.org [200.46.204.183])
by postgresql.org (Postfix) with ESMTP id 544199FBD8D
for <pgsql-es-ayuda-postgresql(dot)org(at)postgresql(dot)org>; Thu, 15 Mar 2007 13:00:00 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024)
with ESMTP id 41945-01 for <pgsql-es-ayuda-postgresql(dot)org(at)postgresql(dot)org>;
Thu, 15 Mar 2007 12:59:49 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from bay0-omc3-s13.bay0.hotmail.com (bay0-omc3-s13.bay0.hotmail.com [65.54.246.213])
by postgresql.org (Postfix) with ESMTP id 4D5129FBD86
for <pgsql-es-ayuda(at)postgresql(dot)org>; Thu, 15 Mar 2007 12:59:49 -0300 (ADT)
Received: from hotmail.com ([64.4.61.45]) by bay0-omc3-s13.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.2668);
Thu, 15 Mar 2007 08:59:48 -0700
Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC;
Thu, 15 Mar 2007 08:59:48 -0700
Message-ID: <BAY102-F35A2E7CA0B81254CFADA28E3720(at)phx(dot)gbl>
Received: from 64.4.61.244 by by102fd.bay102.hotmail.msn.com with HTTP;
Thu, 15 Mar 2007 15:59:40 GMT
X-Originating-IP: [200.88.116.25]
X-Originating-Email: [listas_quijada(at)hotmail(dot)com]
X-Sender: listas_quijada(at)hotmail(dot)com
In-Reply-To: <447122(dot)40926(dot)qm(at)web34707(dot)mail(dot)mud(dot)yahoo(dot)com>
From: "Edwin Quijada" <listas_quijada(at)hotmail(dot)com>
To: colina_movil(at)yahoo(dot)com
Cc: pgsql-es-ayuda(at)postgresql(dot)org
Subject: Re: Simbolos dentro de cadenas
Date: Thu, 15 Mar 2007 15:59:40 +0000
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1; format=flowed
X-OriginalArrivalTime: 15 Mar 2007 15:59:48.0191 (UTC) FILETIME=[F51456F0:01C7671A]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.632 tagged_above=0 required=5 tests=AWL, BAYES_50,
MSGID_FROM_MTA_HEADER, SPF_PASS
X-Spam-Level:
X-Archive-Number: 200703/521
X-Sequence-Number: 25776

>
>Que segui tus articulos y que hoy por hoy me debato
>entre UTUTO y DEBIAN, por favor sin polemica ok. Por
>hoy tuve demasiado. y en la comunidad linux soy un
>novato que apenas sabe instalar un fedora para
>trabajar con PostgreSQL.

Gabriel , Portese como un hombrecito e instalese DEBIAN.

_________________________________________________________________
Latinos en EE.UU: noticias y artículos de interés para ti
http://latino.msn.com/noticias/latinoseneeuu