Re: Problems with + 1 million record table

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
mustbe a simple question to solve but, I'm trying without success. I'm worried because next week I will need to work
withtables 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