3 ms·
IDK if postgres has this problem but mssql does. If you do "Select * from table where ID in (blah)" it ends up with some pretty terrible performance. It's of
by cogman10 3y ago
IDK if postgres has this problem but mssql does. If you do "Select * from table where ID in (blah)"
it ends up with some pretty terrible performance.
It's often a lot faster to do something like
Insert into #tempTable ids
select from table JOIN #tempTable t on t.id = table.id
- WanderPanda 3y agoI can‘t believe this is a thing in 2023! If this is faster, shouldn‘t the database do that transparently in the background?!
- cogman10 3y agoAt least in our case, it comes down to expectations. For the `IN` query the DB doesn't know how many elements are in the list. In MSSQL, it would default to assuming "well, probably a short list" which blows out performance when that's not the case. When you first insert into the temp table, the DB can reasonably say "Oh, this table has n elements" and switch the query plan accordingly. In addition, you can throw an index on the temp table which can also improve performance. Assuming the table you are querying against is indexed on ID, when you have another table with IDs that are indexed it doesn't have to assume random access as it pulls out each id. (Effectively, it just has to navigate the tree nodes in order rather than needing to do a full look into the tree).