From: admin <admin@wtbwts.com>
To: pgsql-sql@postgresql.org
Subject: indices on join
Date: Fri, 17 Dec 1999 19:55:35 +0000 (GMT)
Message-ID: <Pine.BSF.4.10.9912171952150.8044-100000@server.b0x.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: admin@wtbwts.com
Subject: Re: indices on join
In-Reply-To: <Pine.BSF.4.10.9912171952150.8044-100000@server.b0x.com>
References: <Pine.BSF.4.10.9912171952150.8044-100000@server.b0x.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox