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
[EMAIL PROTECTED]
Auto Serviço WinThor: um novo conceito em tecnologia, segurança e agilidade.
