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

Re:Introducing “Database Performance at Scale”: A Free, Open... by 10000truths on Thursday October 05, 2023 @03:23PM (#37783054)

What you're saying is that:

  SELECT * FROM table2
  WHERE table1_id in (SELECT ids FROM table1 WHERE ...)

will be optimized to a join by the query planner (which may or may not be true, depending on how confident you are in the stability of the implementation details of your RDBMS's query planner). But in most circumstances, there is no subquery, it's more like:

  SELECT * FROM table2
  WHERE table1_id in (452345, 529872, 120395, ...)

Where the list of IDs are fetched from an API call to some microservice, or from a poorly used/designed ORM library.

[ Parent | on HN ]

Re:Introducing “Database Performance at Scale”: A Free, Open... by mr_toad on Thursday October 05, 2023 @10:39PM (#37786637)

What’s really bad is where they (or the ORM) generate a seperate select statement for each ID in a loop.

[ Parent | on HN ]

Re:Introducing “Database Performance at Scale”: A Free, Open... by sgarland on Friday October 06, 2023 @07:14PM (#37797523)

And this doesn’t even get into bizarre edge cases, like how Postgres treats foo = ANY(ARRAY[‘bar’, ‘baz’]) differently than foo IN (VALUES((‘bar’), (‘baz’))). PROTIP: the latter is often MUCH faster. I’ve learned this twice this week.

[ Parent | on HN ]