pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: nbarraza@uolsinectis.com.ar
To: pgsql-performance@postgresql.org
Cc: nbarraza@boldt.com.ar
Date: Wed, 10 Dec 2003 16:29:58 -0300 (GMT+3)
Message-ID: <20031210192958.BF23C6C8A6@honorio.sinectis.com.ar> (raw)

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;
 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




view thread (2+ messages)  latest in thread

Message-ID: <20031210192958.BF23C6C8A6@honorio.sinectis.com.ar>
Permalink:  ../20031210192958.BF23C6C8A6@honorio.sinectis.com.ar/
Also on:    postgresql.org/message-id/20031210192958.BF23C6C8A6@honorio.sinectis.com.ar

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: nbarraza@uolsinectis.com.ar, nbarraza@boldt.com.ar
  Subject: Re: 
  In-Reply-To: <20031210192958.BF23C6C8A6@honorio.sinectis.com.ar>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox