Message-ID: <4706E28C.10404@Sheeky.Biz>
Date: Sat, 06 Oct 2007 10:49:08 +0930
From: Shane Ambler <pgsql@Sheeky.Biz>
User-Agent: Thunderbird 2.0.0.6 (Macintosh/20070728)
MIME-Version: 1.0
To: "Joshua D. Drake" <jd@commandprompt.com>
CC: =?ISO-8859-1?Q?Cl=E1udia_Macedo_Amorim?=
	<claudia.amorim@pcinformatica.com.br>, pgsql-performance@postgresql.org
Subject: Re: Problems with + 1 million record table
References: <002001c8075c$c9736df0$9fc8a8c0@pcserver.pcinformatica.com.br>
	<4706784A.2010000@commandprompt.com>
In-Reply-To: <4706784A.2010000@commandprompt.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Joshua D. Drake wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> Cláudia Macedo Amorim wrote:
>> I'm new in PostGreSQL and I need some help.
>> I have a table with ~2 million records. Queries in this table are too slow and some are not completed.I think it must be a simple question to solve but, I'm trying without success. I'm worried because next week I will need to work with tables with ~100 million records.I'm using:O.S.: Windows XP;PostgreSQL 8.2;Index type: btree.I have 2 GB of RAM.
>> POSTGRESQL XXX.LOG:
>>
>> <2007-10-05 09:01:42%SELECT> LOG:  could not send data to client: Unknown winsock error 10061
>> <2007-10-05 09:03:03%idle> LOG:  could not receive data from client: Unknown winsock error 10061
>> <2007-10-05 09:03:03%idle> LOG:  unexpected EOF on client connection
> 
> 
> You are not providing a where clause which means you are scanning all 2
> million records. If you need to do that, do it in a cursor.
> 
> 
> Joshua D. Drake
> 
> 

I would also add that if you want to use anything other than the data 
column in the where clause you should add an index to those columns as well.

>>
>> The table structure is:
>>
>> CREATE TABLE "public"."a_teste_nestle" (
>>   "DATA" TIMESTAMP WITH TIME ZONE, 
>>   "CODCLI" DOUBLE PRECISION, 
>>   "VENDEDOR" DOUBLE PRECISION, 
>>   "SUPERVISOR" DOUBLE PRECISION, 
>>   "CODFILIAL" VARCHAR(2), 
>>   "PRACA" DOUBLE PRECISION, 
>>   "CONDVENDA" DOUBLE PRECISION, 
>>   "QTITVENDIDOS" DOUBLE PRECISION, 
>>   "PVENDA" DOUBLE PRECISION, 
>>   "PESO" DOUBLE PRECISION, 
>>   "CODPROD" VARCHAR(15), 
>>   "CODFAB" VARCHAR(15), 
>>   "DESCRICAO" VARCHAR(80), 
>>   "CODGRUPONESTLE" DOUBLE PRECISION, 
>>   "CODSUBGRUPONESTLE" DOUBLE PRECISION, 
>>   "CODFAMILIANESTLE" DOUBLE PRECISION, 
>>   "QTPESOPREV" DOUBLE PRECISION, 
>>   "QTVENDAPREV" DOUBLE PRECISION, 
>>   "VLVENDAPREV" DOUBLE PRECISION, 
>>   "QT" DOUBLE PRECISION, 
>>   "PUNIT" DOUBLE PRECISION
>> ) WITHOUT OIDS;
>>
>> CREATE INDEX "a_teste_nestle_idx" ON "public"."a_teste_nestle"
>>   USING btree ("DATA");
>>
>>
>> Thanks,


-- 

Shane Ambler
pgSQL@Sheeky.Biz

Get Sheeky @ http://Sheeky.Biz