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 parameters as 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 III 1 
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;
(1 row)

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

boss=# set enable_seqscan = false;

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=517829 loops=1)
   Index Cond: ((juego = '03'::bpchar) AND (juego_vta = '03'::bpchar) AND (sorteo_p = 
   Filter: (extraido = 'S'::bpchar)
 Total runtime: 335280.56 msec
(4 rows)


Thank you in advance for any help.


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Reply via email to