... - Mailing list pgsql-performance

I have some problems on performance using postgresql v. 7.3.2 running on Linux RedHat 9. An update involving several
rows(about 500000) on a table having 2800000 tuples takes in the order of 6 minutes. It is more than it takes on other
plataforms(SqlServer, FOX). I think that there�s something wrong on my configuration. I�ve already adjusted some
parametersas I could understand memory and disk usage. Next, I send a description of parameters changed in
postgresql.conf,a scheme of the table, and an EXPLAIN ANALYZE of the command. The hardware configuration is a Pentium
III1 Ghz, 512 MB of memory, and an SCSI drive of 20 GB. Following goes the description: 

-- Values changed in postgresql.conf

tcpip_socket = true
max_connections = 64
shared_buffers = 4096
wal_buffers = 100
vacuum_mem = 16384
vacuum_mem = 16384
sort_mem = 32168
checkpoint_segments = 8
effective_cache_size = 10000


--
-- PostgreSQL database dump
--

\connect - nestor

SET search_path = public, pg_catalog;

--
-- TOC entry 2 (OID 22661417)
-- Name: jugadas; Type: TABLE; Schema: public; Owner: nestor
--

CREATE TABLE jugadas (
    fecha_ju character(8),
    hora_ju character(4),
    juego character(2),
    juego_vta character(2),
    sorteo_p character(5),
    sorteo_v character(5),
    nro_servidor character(1),
    ticket character(9),
    terminal character(4),
    sistema character(1),
    agente character(5),
    subagente character(3),
    operador character(2),
    importe character(7),
    anulada character(1),
    icode character(15),
    codseg character(15),
    tipo_moneda character(1),
    apuesta character(100),
    extraido character(1)
);


--
-- TOC entry 4 (OID 25553754)
-- Name: key_jug_1; Type: INDEX; Schema: public; Owner: nestor
--

CREATE UNIQUE INDEX key_jug_1 ON jugadas USING btree (juego, juego_vta, sorteo_p, nro_servidor, ticket);

boss=# explain analyze update jugadas set extraido = 'S' where juego = '03' and
juego_vta = '03' and sorteo_p = '89353' and extraido = 'N';
                                                           QUERY PLAN


---------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on jugadas  (cost=0.00..174624.96 rows=70061 width=272) (actual time=21223.88..51858.07 rows=517829 loops=1)
   Filter: ((juego = '03'::bpchar) AND (juego_vta = '03'::bpchar) AND (sorteo_p
= '89353'::bpchar) AND (extraido = 'N'::bpchar))
 Total runtime: 291167.36 msec
(3 rows)

boss=# show enable_seqscan;
 enable_seqscan
----------------
 on
(1 row)


************* FORCING INDEX SCAN ***********************************

boss=# set enable_seqscan = false;
SET

boss=# explain analyze update jugadas set extraido = 'N' where juego = '03' and
juego_vta = '03' and sorteo_p = '89353' and extraido = 'S';
                                                             QUERY PLAN


-------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using key_jug_1 on jugadas  (cost=0.00..597959.76 rows=98085 width=272) (actual time=9.93..39947.93
rows=517829loops=1) 
   Index Cond: ((juego = '03'::bpchar) AND (juego_vta = '03'::bpchar) AND (sorteo_p = '89353'::bpchar))
   Filter: (extraido = 'S'::bpchar)
 Total runtime: 335280.56 msec
(4 rows)

boss=#

Thank you in advance for any help.

Nestor