Re: Problems with + 1 million record table - Mailing list pgsql-performance

-----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
bea simple question to solve but, I'm trying without success. I'm worried because next week I will need to work with
tableswith ~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.amorim@pcinformatica.com.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-----