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@xxxxxxxxxx
Get Sheeky @ http://Sheeky.Biz
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
