pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josué Maldonado <josue@lamundial.hn>
To: pgsql-performance@postgresql.org
Subject: Join slow on "large" tables
Date: Mon, 07 Jun 2004 16:19:22 -0600
Message-ID: <40C4E9EA.7000708@lamundial.hn> (raw)

Hello list,

Server is dual Xeon 2.4, 2GBRAM, Postgresql is running on partition:
/dev/sda9              29G  8.9G   20G  31% /home2
/dev/sda9 on /home2 type jfs (rw)

Version()
PostgreSQL 7.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 
20020903 (Red Hat Linux 8.0 3.2-7)

I have a view to join two tables inventory details (pkardex) and 
inventory documents header (pmdoc) this view usually runs pretty slow as 
indicated in the explain analyze, pkardex is 1943465 rows and its size 
aprox 659MB, pmdoc is 1183520 rows and its size is aprox 314MB. The view 
definition is:

SELECT pkd_pk AS kpk, (pkd_stamp)::date AS kfecha, pkd_docto AS kdocto,
     ((((pdc_custid)::text || ' '::text) || 
(pdc_custname)::text))::character
     varying(50) AS kclpv, pkd_saldo AS ksaldo, pkd_es AS kes, CASE WHEN 
(pkd_es
     = 'E'::bpchar) THEN pkd_qtyinv ELSE (0)::numeric END AS kentrada, 
CASE WHEN
     (pkd_es = 'S'::bpchar) THEN pkd_qtyinv ELSE (0)::numeric END AS 
ksalida,
     pkd_pcode AS kprocode, pkd_price AS kvalor, pdc_tipdoc AS ktipdoc
FROM (pkardex JOIN pmdoc ON ((pmdoc.pdc_pk = pkardex.doctofk)));


Shared memory is:
/root: cat /proc/sys/kernel/shmmax
1073741824

and postgresql.conf have this settings:
tcpip_socket = true
sort_mem = 8190         # min 64, size in KB
vacuum_mem = 262144             # min 1024, size in KB
checkpoint_segments = 10
max_connections = 256
shared_buffers = 32000
effective_cache_size = 160000   # typically 8KB each
random_page_cost = 2            # units are one sequ

The explain analyze is:
dbmund=# explain analyze select * from vkardex where kprocode='1017';
  Nested Loop  (cost=0.00..32155.66 rows=5831 width=114) (actual 
time=18.223..47983.157 rows=4553 loops=1)
    ->  Index Scan using pkd_pcode_idx on pkardex  (cost=0.00..11292.52 
rows=5831 width=72) (actual time=18.152..39520.406 rows=5049 loops=1)
          Index Cond: ((pkd_pcode)::text = '1017'::text)
    ->  Index Scan using pdc_pk_idx on pmdoc  (cost=0.00..3.55 rows=1 
width=50) (actual time=1.659..1.661 rows=1 loops=5049)
          Index Cond: (pmdoc.pdc_pk = "outer".doctofk)
  Total runtime: 47988.067 ms
(6 rows)

Does anyone can help me how to properly tune postgresql to gain some 
speed in such queries, some people have mentioned a RAM increase is 
necesary, about 8GB or more to have postgresql to run  smooth, any 
comment or suggestion. I really appreciate any help.

Regards,


-- 
Sinceramente,
Josué Maldonado.
"Que se me den seis líneas escritas de puño y letra del hombre más 
honrado del mundo, y hallaré en ellas motivos para hacerle ahorcar." 
--cardenal Richelieu (Cardenal y político francés. 1.585 - 1.642)



view thread (9+ messages)  latest in thread

Message-ID: <40C4E9EA.7000708@lamundial.hn>
Permalink:  ../40C4E9EA.7000708@lamundial.hn/
Also on:    postgresql.org/message-id/40C4E9EA.7000708@lamundial.hn

 · 

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: josue@lamundial.hn
  Subject: Re: Join slow on "large" tables
  In-Reply-To: <40C4E9EA.7000708@lamundial.hn>

* 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