Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id A93449FA4A0 for ; Fri, 5 Oct 2007 11:39:51 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 95242-01 for ; Fri, 5 Oct 2007 11:39:46 -0300 (ADT) X-Greylist: delayed 00:06:36.083061 by SQLgrey-1.7.5 Received: from pcinformatica.com.br (ns2.pcinformatica.com.br [201.24.152.2]) by postgresql.org (Postfix) with ESMTP id 829FC9F9D3A for ; Fri, 5 Oct 2007 11:39:44 -0300 (ADT) Received: (qmail 31927 invoked by uid 88); 5 Oct 2007 14:32:58 -0000 Received: from 192.168.200.159 by smart (envelope-from , uid 89) with qmail-scanner-1.25 (uvscan: v4.4.00/v4400. Clear:RC:1(192.168.200.159):. Processed in 0.754453 secs); 05 Oct 2007 14:32:58 -0000 X-Qmail-Scanner-Mail-From: claudia.amorim@pcinformatica.com.br via smart X-Qmail-Scanner: 1.25 (Clear:RC:1(192.168.200.159):. Processed in 0.754453 secs) X-Qmail-Util: Version 0.6.3 Received: from unknown (HELO pcterm0068) (claudia.amorim@pcinformatica.com.br@192.168.200.159) by 0 with SMTP; 5 Oct 2007 14:32:57 -0000 Message-ID: <002001c8075c$c9736df0$9fc8a8c0@pcserver.pcinformatica.com.br> From: =?iso-8859-1?Q?Cl=E1udia_Macedo_Amorim?= To: Subject: Problems with + 1 million record table Date: Fri, 5 Oct 2007 11:34:07 -0300 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_001D_01C80743.A41CC010" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.3138 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/55 X-Sequence-Number: 27077 This is a multi-part message in MIME format. ------=_NextPart_000_001D_01C80743.A41CC010 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 PSQLODBC.LOG: [13236.470] = ------------------------------------------------------------ [13236.470] hdbc=3D02DE3008, stmt=3D02C7B1A8, = result=3D02C791D0 [13236.470] prepare=3D0, internal=3D0 [13236.470] bindings=3D32090580, bindings_allocated=3D20 [13236.470] parameters=3D00000000, = parameters_allocated=3D0 [13236.470] statement_type=3D0, statement=3D'select=20 a_teste_nestle."CODCLI", a_teste_nestle."CODFAB", a_teste_nestle."CODFAMILIANESTLE", a_teste_nestle."CODFILIAL", a_teste_nestle."CODGRUPONESTLE", a_teste_nestle."CODSUBGRUPONESTLE", a_teste_nestle."CONDVENDA", a_teste_nestle."DATA", a_teste_nestle."DESCRICAO", a_teste_nestle."PESO", a_teste_nestle."PRACA", a_teste_nestle."PUNIT", a_teste_nestle."PVENDA", a_teste_nestle."QT", a_teste_nestle."QTITVENDIDOS", a_teste_nestle."QTPESOPREV", a_teste_nestle."QTVENDAPREV", a_teste_nestle."SUPERVISOR", a_teste_nestle."VENDEDOR", a_teste_nestle."VLVENDAPREV" from a_teste_nestle=20 =20 ' [13236.486] stmt_with_params=3D'select=20 a_teste_nestle."CODCLI", a_teste_nestle."CODFAB", a_teste_nestle."CODFAMILIANESTLE", a_teste_nestle."CODFILIAL", a_teste_nestle."CODGRUPONESTLE", a_teste_nestle."CODSUBGRUPONESTLE", a_teste_nestle."CONDVENDA", a_teste_nestle."DATA", a_teste_nestle."DESCRICAO", a_teste_nestle."PESO", a_teste_nestle."PRACA", a_teste_nestle."PUNIT", a_teste_nestle."PVENDA", a_teste_nestle."QT", a_teste_nestle."QTITVENDIDOS", a_teste_nestle."QTPESOPREV", a_teste_nestle."QTVENDAPREV", a_teste_nestle."SUPERVISOR", a_teste_nestle."VENDEDOR", a_teste_nestle."VLVENDAPREV" from a_teste_nestle=20 ' [13236.486] data_at_exec=3D-1, current_exec_param=3D-1, = put_data=3D0 [13236.501] currTuple=3D-1, current_col=3D-1, = lobj_fd=3D-1 [13236.501] maxRows=3D0, rowset_size=3D1, = keyset_size=3D0, cursor_type=3D0, scroll_concurrency=3D1 [13236.501] cursor_name=3D'SQL_CUR02C7B1A8' [13236.501] ----------------QResult Info = ------------------------------- [13236.501] fields=3D02C7C9B8, = backend_tuples=3D00000000, tupleField=3D0, conn=3D02DE3008 [13236.501] fetch_count=3D0, num_total_rows=3D819200, = num_fields=3D20, cursor=3D'(NULL)' [13236.501] message=3D'Out of memory while reading = tuples.', command=3D'(NULL)', notice=3D'(NULL)' [13236.501] status=3D7, inTuples=3D1 [13236.501]CONN ERROR: func=3DSC_execute, desc=3D'(null)', errnum=3D109, = errmsg=3D'Out of memory while reading tuples.' [13236.517] = ------------------------------------------------------------ [13236.517] henv=3D02C727B8, conn=3D02DE3008, status=3D1, = num_stmts=3D16 [13236.517] sock=3D02DD3120, stmts=3D02DD8EE8, = lobj_type=3D17288 [13236.517] ---------------- Socket Info = ------------------------------- [13236.517] socket=3D512, reverse=3D0, errornumber=3D0, = errormsg=3D'(NULL)' [13236.517] buffer_in=3D46642688, buffer_out=3D46633712 [13236.517] buffer_filled_in=3D4096, buffer_filled_out=3D0, = buffer_read_in=3D3426 [63860.095]conn=3D02DE3008, PGAPI_Disconnect [63880.251]conn=3D02C73A78, PGAPI_Disconnect POSTGRESQL.CONF: #------------------------------------------------------------------------= --- # RESOURCE USAGE (except WAL) #------------------------------------------------------------------------= --- # - Memory - shared_buffers =3D 512MB # min 128kB or max_connections*16kB # (change requires restart) temp_buffers =3D 32MB # min 800kB #max_prepared_transactions =3D 5 # can be 0 or more # (change requires restart) # Note: increasing max_prepared_transactions costs ~600 bytes of shared = memory # per transaction slot, plus lock space (see max_locks_per_transaction). work_mem =3D 256MB # min 64kB maintenance_work_mem =3D 128MB # min 1MB #max_stack_depth =3D 2MB # min 100kB # - Free Space Map - max_fsm_pages =3D 409600 # min max_fsm_relations*16, 6 bytes each # (change requires restart) #max_fsm_relations =3D 1000 # min 100, ~70 bytes each # (change requires restart) The table structure is: CREATE TABLE "public"."a_teste_nestle" ( "DATA" TIMESTAMP WITH TIME ZONE,=20 "CODCLI" DOUBLE PRECISION,=20 "VENDEDOR" DOUBLE PRECISION,=20 "SUPERVISOR" DOUBLE PRECISION,=20 "CODFILIAL" VARCHAR(2),=20 "PRACA" DOUBLE PRECISION,=20 "CONDVENDA" DOUBLE PRECISION,=20 "QTITVENDIDOS" DOUBLE PRECISION,=20 "PVENDA" DOUBLE PRECISION,=20 "PESO" DOUBLE PRECISION,=20 "CODPROD" VARCHAR(15),=20 "CODFAB" VARCHAR(15),=20 "DESCRICAO" VARCHAR(80),=20 "CODGRUPONESTLE" DOUBLE PRECISION,=20 "CODSUBGRUPONESTLE" DOUBLE PRECISION,=20 "CODFAMILIANESTLE" DOUBLE PRECISION,=20 "QTPESOPREV" DOUBLE PRECISION,=20 "QTVENDAPREV" DOUBLE PRECISION,=20 "VLVENDAPREV" DOUBLE PRECISION,=20 "QT" DOUBLE PRECISION,=20 "PUNIT" DOUBLE PRECISION ) WITHOUT OIDS; CREATE INDEX "a_teste_nestle_idx" ON "public"."a_teste_nestle" USING btree ("DATA"); Thanks, _________________________ Cl=E1udia Macedo Amorim Consultora de Desenvolvimento PC Sistemas - www.pcsist.com.br (62) 3250-0200 claudia.amorim@pcinformatica.com.br =20 Auto Servi=E7o WinThor: um novo conceito em tecnologia, seguran=E7a e = agilidade. ------=_NextPart_000_001D_01C80743.A41CC010 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 
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=20 not send data to client: Unknown winsock error 10061
<2007-10-05 09:03:03%idle> = LOG:  could=20 not receive data from client: Unknown winsock error 10061
<2007-10-05 09:03:03%idle> = LOG: =20 unexpected EOF on client connection
 
 
PSQLODBC.LOG:
 

[13236.470]           &n= bsp;    =20 ------------------------------------------------------------
[1= 3236.470]           &n= bsp;    =20 hdbc=3D02DE3008, stmt=3D02C7B1A8, = result=3D02C791D0
[13236.470]           &n= bsp;    =20 prepare=3D0, internal=3D0
[13236.470]           &n= bsp;    =20 bindings=3D32090580, bindings_allocated=3D20
[13236.470]           &n= bsp;    =20 parameters=3D00000000, = parameters_allocated=3D0
[13236.470]           &n= bsp;    =20 statement_type=3D0, statement=3D'select

a_teste_nestle."CODCLI",

a_teste_nestle."CODFAB",

a_teste_nestle."CODFAMILIANESTLE",

a_teste_nestle."CODFILIAL",

a_teste_nestle."CODGRUPONESTLE",

=

a_teste_nestle."CODSUBGRUPONESTLE",<= /P>

a_teste_nestle."CONDVENDA",

a_teste_nestle."DATA",

a_teste_nestle."DESCRICAO",

a_teste_nestle."PESO",

a_teste_nestle."PRACA",

a_teste_nestle."PUNIT",

a_teste_nestle."PVENDA",

a_teste_nestle."QT",

a_teste_nestle."QTITVENDIDOS",

a_teste_nestle."QTPESOPREV",

a_teste_nestle."QTVENDAPREV",

a_teste_nestle."SUPERVISOR",

a_teste_nestle."VENDEDOR",

a_teste_nestle."VLVENDAPREV"

from a_teste_nestle =

 

'
[13236.486]           &n= bsp;    =20 stmt_with_params=3D'select=20
a_teste_nestle."CODCLI",
a_teste_nestle."CODFAB",
a_teste_nestl= e."CODFAMILIANESTLE",
a_teste_nestle."CODFILIAL",
a_teste_nestle."C= ODGRUPONESTLE",
a_teste_nestle."CODSUBGRUPONESTLE",
a_teste_nestle.= "CONDVENDA",
a_teste_nestle."DATA",
a_teste_nestle."DESCRICAO",
= a_teste_nestle."PESO",
a_teste_nestle."PRACA",
a_teste_nestle."PUNI= T",
a_teste_nestle."PVENDA",
a_teste_nestle."QT",
a_teste_nestle= ."QTITVENDIDOS",
a_teste_nestle."QTPESOPREV",
a_teste_nestle."QTVEN= DAPREV",
a_teste_nestle."SUPERVISOR",
a_teste_nestle."VENDEDOR",a_teste_nestle."VLVENDAPREV"
from=20 a_teste_nestle

'
[13236.486]           &n= bsp;    =20 data_at_exec=3D-1, current_exec_param=3D-1, = put_data=3D0
[13236.501]           &n= bsp;    =20 currTuple=3D-1, current_col=3D-1, = lobj_fd=3D-1
[13236.501]           &n= bsp;    =20 maxRows=3D0, rowset_size=3D1, keyset_size=3D0, cursor_type=3D0,=20 scroll_concurrency=3D1
[13236.501]           &n= bsp;    =20 cursor_name=3D'SQL_CUR02C7B1A8'
[13236.501]           &n= bsp;    =20 ----------------QResult Info=20 -------------------------------
[13236.501]           &n= bsp;    =20 fields=3D02C7C9B8, backend_tuples=3D00000000, tupleField=3D0,=20 conn=3D02DE3008
[13236.501]           &n= bsp;    =20 fetch_count=3D0, num_total_rows=3D819200, num_fields=3D20,=20 cursor=3D'(NULL)'
[13236.501]           &n= bsp;    =20 message=3D'Out of memory while reading tuples.', = command=3D'(NULL)',=20 notice=3D'(NULL)'
[13236.501]           &n= bsp;    =20 status=3D7, inTuples=3D1
[13236.501]CONN ERROR: = func=3DSC_execute,=20 desc=3D'(null)', errnum=3D109, errmsg=3D'Out of memory while reading=20 tuples.'
[13236.517]           =20 ------------------------------------------------------------
[1= 3236.517]           =20 henv=3D02C727B8, conn=3D02DE3008, status=3D1, = num_stmts=3D16
[13236.517]           =20 sock=3D02DD3120, stmts=3D02DD8EE8, = lobj_type=3D17288
[13236.517]           =20 ---------------- Socket Info=20 -------------------------------
[13236.517]         =20   socket=3D512, reverse=3D0,=20 errornumber=3D0, errormsg=3D'(NULL)'
[13236.517]           =20 buffer_in=3D46642688, buffer_out=3D46633712
[13236.517]           =20 buffer_filled_in=3D4096, buffer_filled_out=3D0,=20 buffer_read_in=3D3426
[63860.095]conn=3D02DE3008,=20 PGAPI_Disconnect
[63880.251]conn=3D02C73A78,=20 PGAPI_Disconnect

 

 

 

POSTGRESQL.CONF:

 

#---------------------------------------------------------------= ------------
#=20 RESOURCE USAGE (except=20 WAL)
#----------------------------------------------------------------= -----------

# - Memory=20 -

shared_buffers =3D 512MB   # min 128kB or=20 max_connections*16kB
     # (change requires = restart)
temp_buffers =3D 32MB   # min=20 800kB
#max_prepared_transactions =3D 5  # can be 0 or=20 more
     # (change requires restart)
# = Note:=20 increasing max_prepared_transactions costs ~600 bytes of shared = memory
# per=20 transaction slot, plus lock space (see = max_locks_per_transaction).
work_mem =3D=20 256MB    # min 64kB
maintenance_work_mem =3D=20 128MB  # min 1MB
#max_stack_depth =3D = 2MB   # min=20 100kB

# - Free=20 Space Map -

max_fsm_pages =3D 409600  # min max_fsm_relations*16, = 6 bytes=20 each
     # (change requires=20 restart)
#max_fsm_relations =3D 1000  # min 100, ~70 bytes=20 each
     # (change requires=20 restart)

 
 
 
The table structure is:
 
CREATE TABLE "public"."a_teste_nestle" (
  "DATA" = TIMESTAMP WITH=20 TIME ZONE,
  "CODCLI" DOUBLE PRECISION,
  "VENDEDOR" = DOUBLE=20 PRECISION,
  "SUPERVISOR" DOUBLE PRECISION,
  = "CODFILIAL"=20 VARCHAR(2),
  "PRACA" DOUBLE PRECISION,
  "CONDVENDA" = DOUBLE=20 PRECISION,
  "QTITVENDIDOS" DOUBLE PRECISION,
  = "PVENDA"=20 DOUBLE PRECISION,
  "PESO" DOUBLE PRECISION,
  = "CODPROD"=20 VARCHAR(15),
  "CODFAB" VARCHAR(15),
  "DESCRICAO"=20 VARCHAR(80),
  "CODGRUPONESTLE" DOUBLE PRECISION,
 =20 "CODSUBGRUPONESTLE" DOUBLE PRECISION,
  "CODFAMILIANESTLE" = DOUBLE=20 PRECISION,
  "QTPESOPREV" DOUBLE PRECISION,
  = "QTVENDAPREV"=20 DOUBLE PRECISION,
  "VLVENDAPREV" DOUBLE PRECISION,
  = "QT"=20 DOUBLE PRECISION,
  "PUNIT" DOUBLE PRECISION
) WITHOUT=20 OIDS;
 
CREATE INDEX "a_teste_nestle_idx" ON = "public"."a_teste_nestle"
 =20 USING btree ("DATA");
 
 
Thanks,
_________________________
Cl=E1udia = Macedo=20 Amorim
Consultora de Desenvolvimento
PC Sistemas - www.pcsist.com.br
(62) = 3250-0200
claudia.amorim@pcinfo= rmatica.com.br
 
 
Auto Servi=E7o WinThor: um = novo conceito em=20 tecnologia, seguran=E7a e agilidade.
------=_NextPart_000_001D_01C80743.A41CC010--