Skip site navigation (1) Skip section navigation (2)

From: nbarraza(at)uolsinectis(dot)com(dot)ar
To: pgsql-performance(at)postgresql(dot)org
Cc: nbarraza(at)boldt(dot)com(dot)ar
Subject:
Date: 2003-12-10 19:29:58
Message-ID: 20031210192958.BF23C6C8A6@honorio.sinectis.com.ar (view raw or flat)
Thread:
Lists: 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 theres something wrong on my configuration. Ive 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;
 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=517829 loops=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


Responses

  • Re: at 2003-12-10 20:35:05 from Stephan Szabo

pgsql-performance by date

Next:From: Tom LaneDate: 2003-12-10 19:54:14
Subject: Re: TRUNCATE veeeery slow compared to DELETE in 7.4
Previous:From: Neil ConwayDate: 2003-12-10 19:15:35
Subject: Re: Solaris Performance (Again)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group