pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Cláudia Macedo Amorim <claudia.amorim@pcinformatica.com.br>
To: pgsql-performance@postgresql.org
Subject: Problems with + 1 million record table
Date: Fri, 5 Oct 2007 11:34:07 -0300
Message-ID: <002001c8075c$c9736df0$9fc8a8c0@pcserver.pcinformatica.com.br> (raw )
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=02DE3008, stmt=02C7B1A8, result=02C791D0
[13236.470] prepare=0, internal=0
[13236.470] bindings=32090580, bindings_allocated=20
[13236.470] parameters=00000000, parameters_allocated=0
[13236.470] statement_type=0, statement='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",
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] stmt_with_params='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",
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] data_at_exec=-1, current_exec_param=-1, put_data=0
[13236.501] currTuple=-1, current_col=-1, lobj_fd=-1
[13236.501] maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0, scroll_concurrency=1
[13236.501] cursor_name='SQL_CUR02C7B1A8'
[13236.501] ----------------QResult Info -------------------------------
[13236.501] fields=02C7C9B8, backend_tuples=00000000, tupleField=0, conn=02DE3008
[13236.501] fetch_count=0, num_total_rows=819200, num_fields=20, cursor='(NULL)'
[13236.501] message='Out of memory while reading tuples.', command='(NULL)', notice='(NULL)'
[13236.501] status=7, inTuples=1
[13236.501]CONN ERROR: func=SC_execute, desc='(null)', errnum=109, errmsg='Out of memory while reading tuples.'
[13236.517] ------------------------------------------------------------
[13236.517] henv=02C727B8, conn=02DE3008, status=1, num_stmts=16
[13236.517] sock=02DD3120, stmts=02DD8EE8, lobj_type=17288
[13236.517] ---------------- Socket Info -------------------------------
[13236.517] socket=512, reverse=0, errornumber=0, errormsg='(NULL)'
[13236.517] buffer_in=46642688, buffer_out=46633712
[13236.517] buffer_filled_in=4096, buffer_filled_out=0, buffer_read_in=3426
[63860.095]conn=02DE3008, PGAPI_Disconnect
[63880.251]conn=02C73A78, PGAPI_Disconnect
POSTGRESQL.CONF:
#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------
# - Memory -
shared_buffers = 512MB # min 128kB or max_connections*16kB
# (change requires restart)
temp_buffers = 32MB # min 800kB
#max_prepared_transactions = 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 = 256MB # min 64kB
maintenance_work_mem = 128MB # min 1MB
#max_stack_depth = 2MB # min 100kB
# - Free Space Map -
max_fsm_pages = 409600 # min max_fsm_relations*16, 6 bytes each
# (change requires restart)
#max_fsm_relations = 1000 # min 100, ~70 bytes each
# (change requires restart)
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,
_________________________
Cláudia Macedo Amorim
Consultora de Desenvolvimento
PC Sistemas - www.pcsist.com.br
(62) 3250-0200
claudia.amorim@pcinformatica.com.br
Auto Serviço WinThor: um novo conceito em tecnologia, segurança e agilidade.
view thread (5+ messages) latest in thread
Message-ID: <002001c8075c$c9736df0$9fc8a8c0@pcserver.pcinformatica.com.br>
Permalink: ../002001c8075c$c9736df0$9fc8a8c0@pcserver.pcinformatica.com.br/
Also on: postgresql.org/message-id/002001c8075c$c9736df0$9fc8a8c0@pcserver.pcinformatica.com.br
copy link · copy postgr.es
reply 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: claudia.amorim@pcinformatica.com.br
Subject: Re: Problems with + 1 million record table
In-Reply-To: <002001c8075c$c9736df0$9fc8a8c0@pcserver.pcinformatica.com.br>
* 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