3 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 the conditi

by caf 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. It's not clear to me why IN () can't do an index scan or hash join?

danmaz74 7y ago
IN () definitely does use indexes in most cases on Postgres. The article is wrong there.
- aiCeivi9 7y ago
  Yes in general but I was hit with case when Postgres started to switch to FTS depending on argument count inside `IN ()` while in reality using index was faster for each and every case. I had to split it in multiple queries with 100 args each, even if few month earlier it didn't have any issue with thousands.
  - danmaz74 7y ago
    That's why I wrote "in most cases" :) Anyway, in a situation like that where you have lots of values in the IN clause, I don't think that using = instead of IN would even be an option.
  - jtc331 7y ago
    I’m guessing the index in question was a partial index defined with a “WHERE column NOT NULL” restriction? This was a limit of the optimizer previously (IN clauses are broken down into AND/OR groups to prove inferences but only if <= 100 items). But Postgres 12 includes a patch I wrote so that the optimizer can prove the NOT NULL inference directly from an array operator of any size.
    - takeda 7y ago
      In my case the index is not a partial index, but it is also on PG 9.6.8, in another response[1] I provided more details. Unfortunately we are stuck with 9.6.x since before my time my company decided to use AWS Aurora, currently there's no easy path to do major version upgrade from 9.6.x and anyway 10.x is the most recent available version :( but any information why is this happening would be appreciated. [1] https://news.ycombinator.com/item?id=20863418 https://news.ycombinator.com/item?id=20863418
julienkervizic 7y ago
Yeah IN/ Not IN should be able to use index/hash scan when given normal values. I am under the impression from the note though that this is related to subqueries fitting in a IN/NOT IN clause, eg: SELECT * FROM tableA IN (SELECT * FROM tableB)
philliphaydon 7y ago
The thing with PostgreSQL is that it may decide that it can find the result faster without the index, than it can with the index. This is sometimes due to the fact our development machines have alot of memory and its easier for PostgreSQL to store the table data in memory and not bother with the index. This is a problem when trying to test an index on a small dataset. It may look like the index is not useful, when infact it will be useful.

by caf 7y ago

danmaz74 7y ago

IN () definitely does use indexes in most cases on Postgres. The article is wrong there.

aiCeivi9 7y ago
Yes in general but I was hit with case when Postgres started to switch to FTS depending on argument count inside `IN ()` while in reality using index was faster for each and every case. I had to split it in multiple queries with 100 args each, even if few month earlier it didn't have any issue with thousands.
- danmaz74 7y ago
  That's why I wrote "in most cases" :) Anyway, in a situation like that where you have lots of values in the IN clause, I don't think that using = instead of IN would even be an option.
- jtc331 7y ago
  I’m guessing the index in question was a partial index defined with a “WHERE column NOT NULL” restriction? This was a limit of the optimizer previously (IN clauses are broken down into AND/OR groups to prove inferences but only if <= 100 items). But Postgres 12 includes a patch I wrote so that the optimizer can prove the NOT NULL inference directly from an array operator of any size.
  - takeda 7y ago
    In my case the index is not a partial index, but it is also on PG 9.6.8, in another response[1] I provided more details. Unfortunately we are stuck with 9.6.x since before my time my company decided to use AWS Aurora, currently there's no easy path to do major version upgrade from 9.6.x and anyway 10.x is the most recent available version :( but any information why is this happening would be appreciated. [1] https://news.ycombinator.com/item?id=20863418 https://news.ycombinator.com/item?id=20863418

julienkervizic 7y ago

Yeah IN/ Not IN should be able to use index/hash scan when given normal values. I am under the impression from the note though that this is related to subqueries fitting in a IN/NOT IN clause, eg: SELECT * FROM tableA IN (SELECT * FROM tableB)

philliphaydon 7y ago

The thing with PostgreSQL is that it may decide that it can find the result faster without the index, than it can with the index. This is sometimes due to the fact our development machines have alot of memory and its easier for PostgreSQL to store the table data in memory and not bother with the index. This is a problem when trying to test an index on a small dataset. It may look like the index is not useful, when infact it will be useful.