News for hackers. Stuff that matters.

A comment on Building Interactive SSH Applications.

Re:Building Interactive SSH Applications by FreeHugs on Monday September 02, 2019 @06:40AM (#20858146) 

    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?
[ Parent | on HN ]
Re:Building Interactive SSH Applications by YorickPeterse on Monday September 02, 2019 @07:35AM (#20858448)
No 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.
[ Parent | on HN ]
Re:Building Interactive SSH Applications by nyuszika7h on Monday September 02, 2019 @07:29AM (#20858418)
How is that relevant to this post? I don't see the text you quoted there.
[ Parent | on HN ]
Re:Building Interactive SSH Applications by kuriho on Monday September 02, 2019 @07:44AM (#20858480)
Most likely meant as a comment for https://news.ycombinator.com/item?id=20855441
[ Parent | on HN ]
Re:Building Interactive SSH Applications by nudpiedo on Monday September 02, 2019 @08:22AM (#20858630)
The 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.

[ Parent | on HN ]
Re:Building Interactive SSH Applications by striking on Monday September 02, 2019 @07:20AM (#20858373)
I mean, there are some reasons not to 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.
[ Parent | on HN ]