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
Scanon manu_base
Any information to make my original query quicker would be much
appreciated. Thanks in advance,
Marc