From: Shane Ambler <pgsql@Sheeky.Biz>
To: Joshua D. Drake <jd@commandprompt.com>
Cc: Cláudia Macedo Amorim <claudia.amorim@pcinformatica.com.br>
Cc: pgsql-performance@postgresql.org
Subject: Re: Problems with + 1 million record table
Date: Sat, 06 Oct 2007 10:49:08 +0930
Message-ID: <4706E28C.10404@Sheeky.Biz> (raw)
In-Reply-To: <4706784A.2010000@commandprompt.com>
References: <002001c8075c$c9736df0$9fc8a8c0@pcserver.pcinformatica.com.br>
<4706784A.2010000@commandprompt.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pgsql@Sheeky.Biz, jd@commandprompt.com, claudia.amorim@pcinformatica.com.br
Subject: Re: Problems with + 1 million record table
In-Reply-To: <4706E28C.10404@Sheeky.Biz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox