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 B1CDCD1B349 for ; Tue, 8 Jun 2004 12:26:41 -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 26026-02 for ; Tue, 8 Jun 2004 15:26:39 +0000 (GMT) Received: from mpls-qmqp-03.inet.qwest.net (mpls-qmqp-03.inet.qwest.net [63.231.195.114]) by svr1.postgresql.org (Postfix) with SMTP id 95BB9D1B233 for ; Tue, 8 Jun 2004 12:26:37 -0300 (ADT) Received: (qmail 57949 invoked by uid 0); 8 Jun 2004 14:58:37 -0000 Received: from mpls-pop-07.inet.qwest.net (63.231.195.7) by mpls-qmqp-03.inet.qwest.net with QMQP; 8 Jun 2004 14:58:37 -0000 Received: from 63-227-127-37.dnvr.qwest.net (HELO ?10.0.0.2?) (63.227.127.37) by mpls-pop-07.inet.qwest.net with SMTP; 8 Jun 2004 15:26:36 -0000 Date: Tue, 08 Jun 2004 09:27:16 -0600 Message-Id: <1086708436.27200.66.camel@localhost.localdomain> From: "Scott Marlowe" To: "=?ISO-8859-1?Q?Josu=E9?= Maldonado" Cc: pgsql-performance@postgresql.org Subject: Re: Join slow on "large" tables In-Reply-To: <40C5CED2.3020002@lamundial.hn> References: <40C4E9EA.7000708@lamundial.hn> <1086648432.27200.37.camel@localhost.localdomain> <40C5CED2.3020002@lamundial.hn> Content-Type: text/plain; charset=utf-8 Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) 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/92 X-Sequence-Number: 7150 On Tue, 2004-06-08 at 08:36, Josué Maldonado wrote: > Hello Scott, SNIP... > > Your numbers show that you are tossing 659M and 314M against each other, > > but I don't know if you're harvesting the whole set at once, or just a > > couple row of each. Indexing help, or is this always gonna be a big seq > > scan of 90% of both tables? > > Generally only a small set is queried, the bigest record set expected is > about 24,000 rows and does not exced the 10MB size, explain analyze > shows the planner is using the index as expected but performance still poor. If that is the case, then shared_buffers should likely only be 1000 to 10000. anything over 10000 is usually a bad idea, unless you've proven it to be faster than <10000. > dbmund=# explain analyze select * from vkardex where kprocode='1013'; > Nested Loop (cost=0.00..2248.19 rows=403 width=114) (actual > time=846.318..16030.633 rows=3145 loops=1) > -> Index Scan using pkd_pcode_idx on pkardex (cost=0.00..806.27 > rows=403 width=72) (actual time=0.054..87.393 rows=3544 loops=1) > Index Cond: ((pkd_pcode)::text = '1013'::text) > -> Index Scan using pdc_pk_idx on pmdoc (cost=0.00..3.55 rows=1 > width=50) (actual time=4.482..4.484 rows=1 loops=3544) > Index Cond: (pmdoc.pdc_pk = "outer".doctofk) > Total runtime: 16033.807 ms > (6 rows) Well, it looks like your predicted versus actual rows are a bit off, and in the final bit, the planner things that it is going to be merging 403 rows but is in fact merging 3145 rows. Try set enable_nestloop = off; and run the explain analyze again and see if that's faster. If so, try upping your target stats on kprocode (see "\h alter table" in psql for the syntax), rerun analyze, and try the query with set enable_nestloop = on to see if the planner makes the right choice.