From: | admin <admin(at)wtbwts(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | indices on join |
Date: | 1999-12-17 19:55:35 |
Message-ID: | Pine.BSF.4.10.9912171952150.8044-100000@server.b0x.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | admin | 1999-12-17 21:34:49 | can't join with indices |
Previous Message | Mitch Vincent | 1999-12-17 18:50:06 | Sub-select speed. |