Join slow on "large" tables - Mailing list pgsql-performance

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)