X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E3A50D1B265 for ; Mon, 7 Jun 2004 19:21:02 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 64105-08 for ; Mon, 7 Jun 2004 22:21:02 +0000 (GMT) Received: from correo2.lamundial.hn (unknown [200.62.45.82]) by svr1.postgresql.org (Postfix) with ESMTP id 581FBD1B4A9 for ; Mon, 7 Jun 2004 19:20:58 -0300 (ADT) Received: from lamundial.hn (computo [192.168.0.140]) by correo2.lamundial.hn (8.12.11/linuxconf) with ESMTP id i57MF5B0000846 for ; Mon, 7 Jun 2004 16:15:07 -0600 Message-ID: <40C4E9EA.7000708@lamundial.hn> Date: Mon, 07 Jun 2004 16:19:22 -0600 From: =?ISO-8859-1?Q?Josu=E9_Maldonado?= User-Agent: Mozilla Thunderbird 0.5 (Windows/20040207) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Join slow on "large" tables Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/74 X-Sequence-Number: 7132 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)