Problems with + 1 million record table

[Cláudia Macedo Amorim <claudia.amorim@xxxxxxxxxxxxxxxxxxxx>]

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@xxxxxxxxxxxxxxxxxxxx

 

 
Auto Serviço WinThor: um novo conceito em tecnologia, segurança e agilidade.