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
