Introducing “Database Performance at Scale”: A Free, Open Source Book

Re:Introducing “Database Performance at Scale”: A Free, Open... by cogman10 on Thursday October 05, 2023 @12:37PM (#37780766)

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

[ Parent | on HN ]

Re:Introducing “Database Performance at Scale”: A Free, Open... by WanderPanda on Thursday October 05, 2023 @01:01PM (#37781090)

I can‘t believe this is a thing in 2023! If this is faster, shouldn‘t the database do that transparently in the background?!

[ Parent | on HN ]

Re:Introducing “Database Performance at Scale”: A Free, Open... by cogman10 on Thursday October 05, 2023 @02:32PM (#37782250)

At 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 and 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).

[ Parent | on HN ]