Hacker News new | ask | show | jobs
by Akronymus 1260 days ago
There's also the possibility of filtering each source table first, then doing an inner join. Which can VASTLY cut down on computation. I assume GP assumed doing an outer join first, then filtering.

But those are details for the database engine to handle. And, as you said, indexes
1 comments
_a_a_a_ 1260 days ago
FYI for others, such filtering is called predicate pushdown (I believe also called predicate hoisting sometimes). Example (and this is trivial but for illustration)

   select * from (select * from tbl) as subqry where subqry.col = 25
would be rewritten by any halfway decent optimiser to

   select * from (select * from tbl where tbl.col = 25)
(and FTR the outermost select * would be stripped off as well).

Good DB optimisers do a whole load of that and much more.

link
Akronymus 1260 days ago
Yeah, had to get quite well acquainted with query execution plans and the like a few years ago (And forgot most of it by now) because of diagnosing a SLOW query.

Joining onto either table a or table b is something that REALLY trips optimizers up.

link