4 ms·
Try to avoid the use of ‘IN’ or ‘NOT IN’. By doing this you are performing a full table scan as the query engine looks through every row to check if
by FreeHugs 7y ago
Try to avoid the use of ‘IN’ or ‘NOT IN’. By
doing this you are performing a full table scan
as the query engine looks through every row to
check if the condition is met.
Is that really true for PostgreSQL? Take this query for example:
SELECT * FROM t WHERE id IN (1,2,3)
I do this often in MySql DBs. I am pretty sure it uses an index on "id" if one exists. EXPLAIN confirms this. Why would a DB engine not use an index?
- striking 7y agoI mean, there are some reasons not to use NOT IN (https://wiki.postgresql.org/wiki/Don%27t_Do_This#DON.27T_USE_NOT_IN https://wiki.postgresql.org/wiki/Don%27t_Do_This#DON.27T_USE...) but that's not a problem if you just have a list of ids.
- nyuszika7h 7y agoHow is that relevant to this post? I don't see the text you quoted there.
- kuriho 7y agoMost likely meant as a comment for https://news.ycombinator.com/item?id=20855441 https://news.ycombinator.com/item?id=20855441
- YorickPeterse 7y agoNo it's not, at least not always. Depending on the query, data, and/or indexes, PostgreSQL will optimise this into a WHERE EXISTS, which in turn is more or less the same as an INNER JOIN. Also, I think you might be commenting on the wrong post.
- nudpiedo 7y agoThe cost must be logarithmic, not lineal. When `id` is part of unique key autoincrement index and the database properly implements a binary tree for such id. EDIT: know what you do before judge and adopt technology, code reviews require previous background.