News for hackers. Stuff that matters.
Re:Speeding up SQL queries by orders of magnitude using UNION by slt2021 on Saturday March 20, 2021 @10:17PM (#26527636)
all queries with JOINs can be rewritten as cartesians: select * from a join B on col1=col2 will be rewritten by query optimizer into: select * from A, B /* this is cartesian that slows down your query */ WHERE a.col1=B.col2

in fact both queries produce the same execution plan if you check yourself

Re:Speeding up SQL queries by orders of magnitude using UNION by gfody on Saturday March 20, 2021 @10:21PM (#26527648)
the query is not the only input into the plan generator, the other inputs are the schema (with constraints and indexes) and their statistics - so you can't really say "check the plan for yourself" without all the inputs. and the only time the plan for "select * from A, B" is going to be the same as "select * from A, B where A.col1=B.col2" is when col1 and col2 are both neither primary keys nor foreign keys and that would be a weird condition under which to run such a query.
Re:Speeding up SQL queries by orders of magnitude using UNION by slt2021 on Saturday March 20, 2021 @10:34PM (#26527688)
pls reread my post, I wrote that query select * from A join B on col1=col2 is the the same plan as for select * from a, b where col1=col2

this is true regardless of schema and statistics, it is simply how query planner works under the hood

and you can check it yourself on any database

Re:Speeding up SQL queries by orders of magnitude using UNION by iaabtpbtpnn on Saturday March 20, 2021 @10:51PM (#26527756)
Those are both inner joins, just a different syntax for writing them.
Re:Speeding up SQL queries by orders of magnitude using UNION by ohlookabird on Sunday March 21, 2021 @03:36AM (#26528766)
They might be logically equivalent, but they are not identical if more than two tables are joined. For instance, the current Postgres docs say [1]:

> This latter equivalence does not hold exactly when more than two tables appear, because JOIN binds more tightly than comma. For example FROM T1 CROSS JOIN T2 INNER JOIN T3 ON condition is not the same as FROM T1, T2 INNER JOIN T3 ON condition because the condition can reference T1 in the first case but not the second.

[1] https://www.postgresql.org/docs/current/queries-table-expres...