Received: from pcr.ca (www.pcr.ca [207.139.158.13] (may be forged)) by hub.org (8.9.3/8.9.3) with ESMTP id VAA18592 for ; Fri, 17 Dec 1999 21:35:57 -0500 (EST) (envelope-from admin@wtbwts.com) Received: by pcr.ca (Postfix, from userid 1000) id 4BDF41F4C; Fri, 17 Dec 1999 21:34:49 +0000 (GMT) Received: from localhost (localhost [127.0.0.1]) by pcr.ca (Postfix) with ESMTP id 3B3781F43 for ; Fri, 17 Dec 1999 21:34:49 +0000 (GMT) Date: Fri, 17 Dec 1999 21:34:49 +0000 (GMT) From: admin X-Sender: admin@server.b0x.com To: pgsql-sql@postgresql.org Subject: can't join with indices Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII I can't seem to make pgsql use my indices when joining two tables. To start, here's my query: SELECT distinct on pid prod_base.pid, manu_base.mid FROM prod_base, manu_base WHERE prod_base.mid = manu_base.mid; and these are the indices I have created on both tables: CREATE INDEX prod_mid_idx ON "prod_base" USING btree ("mid" "int2_ops"); CREATE INDEX manu_mid_idx ON "manu_base" USING btree ("mid" "int2_ops"); Then I vacuumdb'd the whole database: vacuumdb database Finally, I tried running the same original query using EXPLAIN. With and without the indices, the execution is exactly the same. I returned to the manual and read some more, but couldn't find any concrete information. I did find out I will be able to use a hash table later on instead of a btree for joining, because I only join with '=' and I use LIMIT 100 which makes it possible to use memcmp() without concern. For completeness, there are the steps displayed by EXPLAIN: Unique -> Sort -> Hash Join -> Seq Scan on prod_base -> Hash -> Seq Scan on manu_base Any information to make my original query quicker would be much appreciated. Thanks in advance, Marc