Wei,
> I have two tables A and B where A is a huge table with thousands of
> rows, B is a small table with only a couple of entries.
>
> I want to do something like
>
> SELECT
> A.ID
> A.Name
> FROM
> A JOIN B ON (A.ID = B.ID)
You might consider:
SELECT A.ID
A.Name
FROM A
WHERE EXISTS (SELECT ID FROM B WHERE B.ID = A.ID)
This lets the parser know that you are not interested in retrieving
entire records from B, just those rows from A which correspond to B.
Run that, and compare the EXPLAIN ANALYZE plan against one which lets
the that parser have free reign:
SELECT A.ID, A.Name
FROM A, B
WHERE A.ID = B.ID
Chances are, the parser will do a better job on the query than you can
do by making stuff explicit.
Give it a try.
-Josh Berkus