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 96AD4D1BAAD for ; Mon, 7 Jun 2004 20:21:13 -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 90070-09 for ; Mon, 7 Jun 2004 23:21:09 +0000 (GMT) Received: from davinci.ethosmedia.com (server228.ethosmedia.com [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id 7E408D1B34C for ; Mon, 7 Jun 2004 20:21:03 -0300 (ADT) Received: from [64.81.245.111] (HELO 192.168.1.102) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) with ESMTP id 5299287; Mon, 07 Jun 2004 16:22:41 -0700 From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: =?iso-8859-1?q?Josu=E9=20Maldonado?= Subject: Re: Join slow on "large" tables Date: Mon, 7 Jun 2004 16:21:10 -0700 User-Agent: KMail/1.5.4 Cc: pgsql-performance@postgresql.org References: <40C4E9EA.7000708@lamundial.hn> <200406071531.49207.josh@agliodbs.com> <40C4F66C.8030609@lamundial.hn> In-Reply-To: <40C4F66C.8030609@lamundial.hn> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200406071621.10732.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.3 tagged_above=0.0 required=5.0 tests=RCVD_NUMERIC_HELO X-Spam-Level: X-Archive-Number: 200406/80 X-Sequence-Number: 7138 Josue' > 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 Huh? It is not at all the same. Your index scan is down to 87ms from 27,000! And the total query is down to 16seconds from 47 seconds. Don't you consider that an improvement? -- -Josh Berkus Aglio Database Solutions San Francisco