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)
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
