Speed up a query, simple inner join with one large table and one small table.

Question

I have a table T1 with 60 rows and 5 columns: ID1, ID2, info1, info2, info3.

I have a table T2 with 1.2 million rows and another 5 columns: ID3, ID2, info4, info5, info6.

I want to get (ID1, ID2, info4, info5, info6) from all the rows where the ID2s match up. Currently my query looks like this:

SELECT T1.ID1, T2.ID2,
       T2.info4, T2.info5, T2.info6
  FROM T1, T2
 WHERE T1.ID2 = T2.ID2;

This takes about 15 seconds to run. My question is - should it take that long, and if not, how can I speed it up? I figure it shouldn't since T1 is so small.

I asked PostgreSQL to EXPLAIN the query, and it says that it hashes T2, then hash joins that hash with T1. It seems hashing T2 is what takes so long. Is there any way to write the query so it doesn't have to hash T2? Or, is there a way to have it cache the hash of T2 so it doesn't re-do it? The tables will only be updated every few days.

If it makes a difference, T1 is a temporary table created earlier in the session.

Answer 1

It should not take that long :)

Creating an index on T2( ID2 ) should improve the performance of your query.

Answer 2

May be using JOIN increase speed of query:

SELECT T1.ID1, T2.ID2,
    T2.info4, T2.info5, T2.info6
FROM T1
JOIN T2 ON T2.ID2 = T1.ID2;

I don't know exactly but may be your query firstly join all row in both table, and after that apply WHERE conditions and it's problem.

And of course, as Peter Lang saw, you should create index.

Answer 3

First, a make a join.

SELECT T1.ID1, T2.ID2,
       T2.info4, T2.info5, T2.info6
  FROM T1
  JOIN T2 ON T1.ID2 = T2.ID2;

Then try creating and index on T2.d2.

If not, if possible, you can add ID1 column to T2. Update it accordingly every few days as you claim. Then it just a simple query on T2 with no joins.

SELECT T2.ID1, T2.ID2,
       T2.info4, T2.info5, T2.info6
  FROM T2 
  WHERE T2.ID2 = A_VALUE;

Again, an index on T2.ID2 will be recommended.