Hacker News Reader

Comment by calibas | Hacker News Reader

I abused LEFT JOIN to create a query that produces 224,964,999,650,251 rows. Only 3.68ms execution time, now that's impressive!

View on HN

SQL optimiser works out that this is a cross join and renders execution path as such. Cross join implementation is streaming with pre-determined result set size. We would not be able to render this much data this quickly without optimiser :)

Interesting, I tried to make a query taking a long time using join and got 37.43s:

    SELECT * FROM trips
    JOIN gas_prices ON gas_prices.timestamp = trips.pickup_datetime;

Seeing the same results on my end for that query. I'm a little puzzled, when I use the same query but with != instead of = to produce a 1.6 trillion row result, it's about 3-4ms execution. I don't know why it's taking 10,000x longer using = instead of !=.

Something about how timestamp comparisons are handled?

Reply on news.ycombinator.com