Thread: Re: Which of the solution is better?

Re: Which of the solution is better?

From
"Josh Berkus"
Date:
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