Hi,
I'm using PostgreSQL 8.
I have two tables that I am doing a join on, and the join executes very
slowly.
The table called Notification has a text field called NotificationID,
which is its primary key. The Notification table also has an int4 field
called ItemID, and it has an index on the ItemID field. The table
called Item has an int4 field called ItemID, which is its primary key.
If I do a simple select on Notification using just the ItemID field, the
index is used...
explain select notificationID from NOTIFICATION n where n.itemID = 12;
QUERY PLAN
------------------------------------------------------------------------
---------------------
Index Scan using notification_4_idx on notification n
(cost=0.00..129.22 rows=57 width=44)
Index Cond: (itemid = 12)
This query runs in far less than one second.
But if I do a join, the index isn't used...
explain select notificationID from NOTIFICATION n, ITEM i where
n.itemID = i.itemID;
QUERY PLAN
------------------------------------------------------------------------
------
Hash Join (cost=47162.85..76291.32 rows=223672 width=44)
Hash Cond: ("outer".itemid = "inner".itemid)
-> Seq Scan on notification n (cost=0.00..12023.71 rows=223671
width=48)
-> Hash (cost=42415.28..42415.28 rows=741028 width=4)
-> Seq Scan on item i (cost=0.00..42415.28 rows=741028
width=4)
This query takes about 20 seconds to run.
I have run "vacuum analyze", and it didn't make any difference.
I've seen people say that sometimes the query optimizer will decide to
not use an index if it thinks that doing a sequential scan would be
faster. I don't know if that's what's happening here, but it seems to
me that using the index should be much faster than the performance I'm
getting here.
Does anyone have any suggestions on how to make this query run faster?