X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 2034BD1B8B9 for ; Mon, 18 Aug 2003 17:45:38 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 48147-05 for ; Mon, 18 Aug 2003 14:45:28 -0300 (ADT) Received: from megazone.bigpanda.com (45.mufb.snfc.sffca01r18.dsl.att.net [12.103.249.45]) by svr1.postgresql.org (Postfix) with ESMTP id 029B7D1B8AC for ; Mon, 18 Aug 2003 14:45:27 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 6464BD625; Mon, 18 Aug 2003 10:45:25 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 3FD9F5C0A; Mon, 18 Aug 2003 10:45:25 -0700 (PDT) Date: Mon, 18 Aug 2003 10:45:25 -0700 (PDT) From: Stephan Szabo To: pginfo Cc: "pgsql-performance@postgresql.org" Subject: Re: bad join performance In-Reply-To: <3F40E9A9.12AE5D55@t1.unisoftbg.com> Message-ID: <20030818104119.C30675-100000@megazone.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/289 X-Sequence-Number: 3105 On Mon, 18 Aug 2003, pginfo wrote: > Hi , > I am using pg 7.3.3 on RH 7.3, > dual Athlon > 1 GB RAM. > > I have 2 tables a_acc and a_vid_doc (all PK are int). > > sizes: > > select count(IDS) from a_acc; > count > --------- > 1006772 > > select count(IDS) from a_vid_doc; > count > ------- > 25 > > I have problem with the join ot this tables. > I tryed this examples: > > explain analyze select G.IDS from A_ACC G join A_VID_DOC VD > ON(G.IDS_VID_DOC=VD.IDS) WHERE G.IDS = 1338673 ; In general the best index on A_ACC for this kind of query might be on on A_ACC(IDS, IDS_VID_DOC). That should allow you to search by IDS value but still get a sorted order of IDS_VID_DOC to help the join.