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 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(at)Sheeky(dot)Biz

Get Sheeky @ http://Sheeky.Biz