Trying to get postgres to use an index
От | Mike Wertheim |
---|---|
Тема | Trying to get postgres to use an index |
Дата | |
Msg-id | 007e01c4c43b$33b23c90$dc0a000a@mikespc обсуждение исходный текст |
Ответы |
Re: Trying to get postgres to use an index
|
Список | pgsql-general |
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?
В списке pgsql-general по дате отправления: