Re: Problems with + 1 million record table

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

>
>
> 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(dot)amorim(at)pcinformatica(dot)com(dot)br
>
>
> Auto Serviço WinThor: um novo conceito em tecnologia, segurança e agilidade.

- --

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFHBnhJATb/zqfZUUQRAqarAKCk2VDeiHDFYBS8K7bT5yI7LavGSwCbBcHq
hcJQZ8qPpfbbxSUVt1sMKFU=
=Ju0i
-----END PGP SIGNATURE-----